Group By Question - How do I group by every 2 weeks? 2

[emaduddeen]

Hi Everyone,

Can you show me how to change this query so the data is grouped every 2 weeks?

Thanks.

Emad

Select   To_char (travel_date, 'MON-YYYY') Month, Count (tripid) total_trips
    From itms_archtrips
   Where TRAVEL_DATE >= Add_months (Sysdate, -6)
Group By To_char (travel_date, 'MON-YYYY')
Order By Max (travel_date) Desc

 

[Dagon]

What would you want to see exactly ? At the moment it shows months as MON-YYYY. Unfortunately, fortnights aren't defined in such a clear cut way (they don't have names). What would you want to see as the grouping function ? Would it be, for example, the date of a particular Sunday, then advancing two weeks to the next Sunday and so on ?

 

[taupirho]

You need to assign a unique number or other identifier to every 2 week period in your data.

Use a combination of something like the rownum
in combination with the floor function.

Have a look at this for a start

select count(*),grpset
from
(
select to_date('01-jan-2007') + rownum-1 dte,
rownum -1 rn ,
mod(rownum-1,14) +1,
floor((rownum-1)/14) grpset
from all_objects
where rownum <= 200
)
group by grpset




In order to understand recursion, you must first understand recursion.

 

[emaduddeen]

Hi Dagon,

Thanks for the quick reply.

I quickly pasted the query and forgot to change the date format. I was looking to 'DD-MON-YYYY'.

We would like the data to look like this:

DATES                         TOTAL_TRIPS
1-JAN-2007 TO 14-JAN-2007     388
15-JAN-2007 TO 28-JAN-2007    530

And so on.

Cheers,
Emad

 

[taupirho]

You'll have to modify the code below to use your own data set, also the sorting isn't quite right but it should give you a good start

select count(fortnight),fortnight
from
(
select to_date('01-jan-2007') + rownum-1 dte,
min(to_date('01-jan-2007') + rownum-1)
over (partition by floor((rownum-1)/14))  || ' to ' ||
max(to_date('01-jan-2007') + rownum-1)
over (partition by floor((rownum-1)/14)) fortnight
from all_objects
where rownum <= 200
)
group by fortnight


 14 01-JAN-07 to 14-JAN-07
 14 02-JUL-07 to 15-JUL-07
 14 04-JUN-07 to 17-JUN-07
 14 07-MAY-07 to 20-MAY-07
 14 09-APR-07 to 22-APR-07
 14 12-FEB-07 to 25-FEB-07
 14 12-MAR-07 to 25-MAR-07
 14 15-JAN-07 to 28-JAN-07
  4 16-JUL-07 to 19-JUL-07
 14 18-JUN-07 to 01-JUL-07
 14 21-MAY-07 to 03-JUN-07
 14 23-APR-07 to 06-MAY-07
 14 26-FEB-07 to 11-MAR-07
 14 26-MAR-07 to 08-APR-07
 14 29-JAN-07 to 11-FEB-07

In order to understand recursion, you must first understand recursion.

 

[Dagon]

Something like this should do it:

select to_char(fortnight_start, 'DD-MM-YYYY')||' to '||to_char(fortnight_start+13, 'DD-MM-YYYY') as label, count(*)
from ims_archtrips t,
(select to_date('01-jan-2007')+((level-1)*14) as fortnight_start
from dual
connect by level < 26
) d
where t.TRAVEL_DATE between d.fortnight_start and d.fortnight_start+13
group by to_char(fortnight_start, 'DD-MM-YYYY')||' to '||to_char(fortnight_start+13, 'DD-MM-YYYY')
order by to_date(substr(label, 1,10), 'DD-MM-YYYY')

 

[emaduddeen]

Hi Everyone,

Thanks for the help.

I will try both queries later today.

Truly,
Emad