Extract Date and rebuild table view

Analytics

Extract Date and rebuild table view

Hey Guys,

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.

Thanks.

Jason

1 REPLY
Teradata Employee

Re: Extract Date and rebuild table view

Hi,

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

;

Regards,

Vlad.