05-28-2014 08:46 PM
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!
05-28-2014 08:52 PM
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.
05-29-2014 04:20 PM
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.
05-28-2014 09:24 PM
I hope you are not looking for efficiency. Using the wrong tool :
input date :date9.;
format date date9.;
create table gaps as
select date as gap
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
drop table gaps;
select * from want;
05-29-2014 04:18 PM
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.