TeraData

General

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.

2 REPLIES
Junior Contributor

Re: TeraData

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)

Re: TeraData

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