topic Grouping by consecutive days (SQL) in SAS Procedures

Grouping by consecutive days (SQL)

milts — Thu, 29 May 2014 00:46:45 GMT

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

Re: Grouping by consecutive days (SQL)

Reeza — Thu, 29 May 2014 00:52:35 GMT

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.

Re: Grouping by consecutive days (SQL)

PGStats — Thu, 29 May 2014 01:24:34 GMT

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;

Re: Grouping by consecutive days (SQL)

milts — Thu, 29 May 2014 20:18:42 GMT

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.

Re: Grouping by consecutive days (SQL)

milts — Thu, 29 May 2014 20:20:50 GMT

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.

Re: Grouping by consecutive days (SQL)

Reeza — Thu, 29 May 2014 20:41:15 GMT

Then you should write your entire query in GreenPlum SQL.

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