Grouping by consecutive days (SQL)

[milts]

Hi,

Any idea how to tagged dates based on consecutive days using sql? Kindly see example below. I know this can be attained using data step using first and last and lag. But on SQL how can I do this? Greatly appreciate your help. Thanks!

Data:

Date
01jan2014
02jan2014
03jan2014
07jan2014
08jan2014
10jan2014
12jan2014
13jan2014

Result:

Date	Period
01jan2014	1
02jan2014	1
03jan2014	1
07jan2014	2
08jan2014	2
10jan2014	3
12jan2014	4
13jan2014	4

[Reeza]

I'm sure it can be somehow but why? There is no value to doing something like this via SQL.

If you're in a DBMS environment such as MS SQL then I'd use a cursor.

[milts]

For a given period, the analysis should be done for every consecutive days for a given customer, given that my extract period is 1 month, i played 1st to 4th day, 6th day, 10th to 15th day. this will result to 3 different periods.

[PGStats]

I hope you are not looking for efficiency. Using the wrong tool :

data dates;

input date :date9.;

format date date9.;

datalines;

01jan2014

02jan2014

03jan2014

07jan2014

08jan2014

10jan2014

12jan2014

13jan2014

;

proc sql;

create table gaps as

select date as gap

from dates

where intnx("DAY",date,-1) not in (select date from dates);

create table want as

select date, (select count(*) from gaps where gap<=date) as period

from dates;

drop table gaps;

select * from want;

quit;

PG

[milts]

Thanks for your response. However I am looking into performance as well since the SQL script should be explicitly pass-through to greenplum.

Also, this will be done for every customerid.

[Reeza]

Then you should write your entire query in GreenPlum SQL.

You might be better trying a SQL specific forum instead of this one.