how to do time parameters

[ewallace]

My report needs the following 3 parameters added to it:

Time From = specific time of day(s) the output starts (not to be confused with Start Date)

Time To = specific time of day(s) the output ends (not to be confused with End Date)

Time Interval = Summerized times (15 mins, 30 mins, Hour, Day, Week)

But I am not sure how.. Is a variable needed? Or is there a way to do this with parameters? I'm a bit lost on how to word this question, and even more lost on how to get this done. Query is below.

select sample_date_time, station_desc,

to_char(TO_DATE(calendar_date, 'yyyy/mm/dd') , 'DAY') calender_date_day,

direction, segment_desc,

       sum(case when lane_type = 'EL' then volume end) volume_el,

       sum(case when lane_type = 'EL' then speed end) speed_el,

       sum(case when lane_type = 'GP' then volume end) volume_gp,

       sum(case when lane_type = 'GP' then speed end) speed_gp

  from (select rt.calendar_date || rt.time_1_hour sample_date_time, rt.calendar_date,

               rt.lane_type,

               sum(rt.volume) volume,

               avg(rt.speed) speed,

               s.direction,

               s.segment_desc,

             s.station_desc

          from dph_dashboard.raw_traffic_view rt,

           dph_dashboard.STATIONS S

         where s.station_id = rt.station_id

         and rt.station_id in (7)     /*8,9,10,11,12,19,20,21,22,23,24,31,32,371,33,34,35,41,42,43,44,45,46,53,54,55,56,57,58,64,

         65,66,67,68,69,75,76,77,78,79,80,81,87,88,89,90,91,92,99,100,374,101,102,103,109,110,111,112,113,114,121,122,375,

         123,124,125,131,132,376,133,134,135,141,142,143,144,145,151,152,153,154,155,161,162,163,164,165)*/

           and rt.sample_date >= '01-feb-2017'

           and rt.sample_date <= '28-feb-2017' 

           and rt.day_of_week_number in (2,3,4,5,6)

           and to_date(rt.time_5_minute, 'hh24:mi') >=  to_date('05:00', 'hh24:mi')  --time from

           and to_date(rt.time_5_minute, 'hh24:mi') <=  to_date('20:00', 'hh24:mi')   --time to

         group by calendar_date || time_1_hour, lane_type, s.station_desc, calendar_date, s.direction, s.segment_desc)

where to_date(sample_date_time, 'yyyymmddhh24:mi') >= '01-feb-2017'

   and to_date(sample_date_time, 'yyyymmddhh24:mi') < '28-feb-2017'

      

   and ( $P{StartDate}  is null or  $P{StartDate} <= calendar_date )

   and ( $P{EndDate} is null or  $P{EndDate} >= calendar_date  )   

   

   and ( $X{IN, calender_date_day, DaysOfWeek} )

   

   and ($X{IN, direction, Direction})

   and ($X{IN, segment_desc, Segment})

   and ($X{IN, station_desc, Station})

   

group by sample_date_time, station_desc, calendar_date, direction, segment_desc

order by to_date(sample_date_time, 'yyyymmddhh24:mi')