​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
 Grouping by a two week period

​An interesting question popped up on one of the forums recently.The questioner wanted to group their data on a two week period. I came up with the following - analytic - based solution

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