I have trouble to do the following work and hope you can help. PLS take a look over following example data:
Product_key Start_date End_date
A 2013-01-01 2013-01-30
B 2013-01-15 2013-02-07
C 2013-02-02 2013-03-01
I have a long list of products having a start date and an end date. Now I want to understand for a specific time range (say Feb, 2013), how many products are live on a daily basis. I know how to do that for 1 day, but conducting same process for 28 times is killing me and I am even thinking about running a 6 months report.
Hope you can help.
just an idea - not tested for syntax, but should at least give an idea:
select T_1.calendar_date, count(*)
from sys_calendar.calendar as T_1,
your_table as T_2
where T_1.calendar_date between T_2.start_date and T_2.end_date
and T_1.calendar_date between DATE '2013-02-01' and DATE '2013-02-28'
group by 1