All Forums General
15 Jul 2015
TeraData

I have datewise list of subscribers in TD table, I want distinct count of subscribers on daily, weekly, 30 days not monthly, and 90 days basis. Currently I am doing manually by giving the range of dates. Is there any function or better way to do this excercise.
I am not an IT guy.... commercial BI analyst, but love to learn SQL.
 
 

dnoeth 4628 posts Joined 11/04
15 Jul 2015

Can you show your current query?
If you want a single result row you can use different CASEs like this:

COUNT(DISTINCT CASE WHEN date_col = CURRENT_DATE THEN subscriber_no END)

COUNT(DISTINCT CASE WHEN date_col BETWEEN CURRENT_DATE - 7 AND CURRENT_DATE THEN subscriber_no END)

 

Dieter

16 Jul 2015

Dear Dnoeth
I would like to rephrase my question; I have list of subscribers from 1-Jan to 30-Jun. I want distinct count of subscribers on 7 days groupng basis e.g.
1-Jan to 7-Jan distinct count 
8-Jan to 14-Jan distinct count
15-Jan to 21-Jan distinct count
22-Jan to 28-Jan distinct count
29-Jan to 4-Feb ditinct count
 
Hopefully now you will understand...... I want distinct count on 7 days starting from 1-Jan and ending on 30-Jun.
 
Regards
Arshad
 
 

You must sign in to leave a comment.