f93f28b1-31a0-4e0d-9414-fdbb88852db3

Assigned To

Center Contact

Title

Status

Data Import

Entered By

Center Contact Phone/Email Etc.

Date Due

Date Completed

Notes

Built a passenger miles report for them as we really don't have an easy way for them to do it. Script is below.  declare @vehicle varchar(max), @vehicle2 varchar(max), @name varchar(max),@category varchar(max),@next_category varchar(max),@last_category varchar(max), @cat_list varchar(max), @miles int, @totalmiles int,@from varchar(max),@to varchar(max),@seniors_personid int,@last_seniors_personid int,@lastTotal int,@lastPerson varchar(max),@first_seniors_personid int set @last_category = ''   set @cat_list = ''    --Set Starting Date set @from = '2/1/2021' --Set Ending Date set @to = '2/28/2021' --leave it like '%%' to get all Categories or enter in a category to get specific --select distinct Trans_Ride_Category from Trans_Rides set @category = '%%'  set @vehicle = '%%'     set @last_seniors_personid  = (SELECT top 1 s.seniors_personid  FROM Trans_Rides tr, seniors s, trans_riders rds  where tr.Trans_Ride_ID = rds.rideid and rds.SeniorID = s.seniors_personid  and tr.Trans_Ride_Date between @from and @to  order by s.seniors_personid) set @lastTotal  =  (SELECT top 1 tr.Trans_Ride_Mileage  FROM Trans_Rides tr, seniors s, trans_riders rds  where tr.Trans_Ride_ID = rds.rideid and rds.SeniorID = s.seniors_personid  and tr.Trans_Ride_Date between @from and @to  order by s.seniors_personid) set @totalmiles  =  (SELECT top 1 tr.Trans_Ride_Mileage  FROM Trans_Rides tr, seniors s, trans_riders rds  where tr.Trans_Ride_ID = rds.rideid and rds.SeniorID = s.seniors_personid  and tr.Trans_Ride_Date between @from and @to  order by s.seniors_personid)  DECLARE my_cursor CURSOR FOR  SELECT s.seniors_personid,s.seniors_firstname +' '+seniors_lastname,isnull(tr.Trans_Ride_Mileage,0),isnull(tr.Trans_Ride_Category,'NONE'),isnull(tr.Trans_Ride_Vehicle,'NONE') FROM Trans_Rides tr, seniors s, trans_riders rds   where tr.Trans_Ride_ID = rds.rideid  and rds.SeniorID = s.seniors_personid   and tr.Trans_Ride_Date between @from and @to   and tr.Trans_Ride_Category like @category and tr.Trans_Ride_Vehicle like @vehicle order by s.seniors_personid   print 'Passenger Miles Report for '+@from+' To '+@to+'   '   OPEN my_cursor;        create table #category_list(category varchar(max));    create table #report (name varchar(max),category varchar(max),miles varchar(max),vehicle varchar(max));   FETCH NEXT FROM my_cursor   INTO  @seniors_personid,@name, @miles, @category, @vehicle2     WHILE @@FETCH_STATUS = 0     BEGIN      if @seniors_personid = @last_seniors_personid       Begin               set  @totalmiles = @lastTotal + @miles        set @lastTotal = @totalmiles         set @lastPerson = @name             set @last_seniors_personid = @seniors_personid         if @category not in (select category from #category_list)        begin         insert into #category_list(category)         values (@category)         end      end     else      begin       if @lastTotal > 0       begin        DECLARE my_cat_cursor CURSOR FOR             select  category from #category_list                               open my_cat_cursor;            FETCH NEXT FROM my_cat_cursor            INTO @next_category            WHILE @@FETCH_STATUS = 0            BEGIN              set @cat_list = @cat_list + @next_category+', '              set @last_category = @next_category                        FETCH NEXT FROM my_cat_cursor            INTO @next_category           END         CLOSE my_cat_cursor;                        DEALLOCATE my_cat_cursor;           insert into #report (Name,Category,Miles,vehicle)        values (@lastPerson,LEFT(@cat_list,DATALENGTH(@cat_list)-2),convert(varchar(max),@lastTotal),@vehicle2)                      end        set @lastTotal = @miles         set @last_seniors_personid = @seniors_personid        set @lastPerson = @name          set @totalmiles = 0        set @category = NULL           set @cat_list = '  '        set @next_category = NULL        delete from #category_list      end                         FETCH NEXT FROM my_cursor     INTO  @seniors_personid,@name, @miles, @category, @vehicle2     END         CLOSE my_cursor; DEALLOCATE my_cursor; drop table #category_list select name as 'Name',replace(replace(category,' ',''),',',', ') as 'Category/Categories', miles as 'Passenger Miles' , vehicle as 'Vehicle' from #report order by vehicle, name drop table #report

6d3d7b8b-e8b3-45d8-ba71-f82a18066acc

Cancel