November 28, 2016 at 3:39 pm
I'd like to get some help on merging consecutive dates if FLAG is same.
-- INPUT
IDEfftDTTermDTFLAG
------------------------------------------------------------------
7662004-03-302004-03-30N
7662004-03-312004-08-17I (these two records need to be merged)
7662004-08-182004-08-26I
7662004-08-272005-07-13U
7662005-07-142005-08-08N
7662005-08-092006-02-13I
7662006-02-142006-09-10N (these three records need to be merged)
7662006-09-112007-04-02N
7662007-04-032007-04-05N
7662007-04-062007-06-20I
-- EXPECTED OUTPUT
IDEfftDTTermDTFLAG
------------------------------------------------------------------
7662004-03-302004-03-30N
7662004-03-312004-08-26I (the two records are merged)
7662004-08-272005-07-13U
7662005-07-142005-08-08N
7662005-08-092006-02-13I
7662006-02-142007-04-05N (the three records are merged)
7662007-04-062007-06-20I
Below is the SQL statement to create the input table
create table ##INPUT (ID int, EfftDT date, TermDT date, FLAG char(1))
insert into ##INPUT select 766,'2004-03-30','2004-03-30','N'
insert into ##INPUT select 766,'2004-03-31','2004-08-17','I'
insert into ##INPUT select 766,'2004-08-18','2004-08-26','I'
insert into ##INPUT select 766,'2004-08-27','2005-07-13','U'
insert into ##INPUT select 766,'2005-07-14','2005-08-08','N'
insert into ##INPUT select 766,'2005-08-09','2006-02-13','I'
insert into ##INPUT select 766,'2006-02-14','2006-09-10','N'
insert into ##INPUT select 766,'2006-09-11','2007-04-02','N'
insert into ##INPUT select 766,'2007-04-03','2007-04-05','N'
insert into ##INPUT select 766,'2007-04-06','2007-06-20','I'
select * from ##INPUT order by 2
In the input table, there is no overlap between records (EfftDT is always a day after the previous TermDT)
Thank you in advance.
November 28, 2016 at 4:03 pm
This is a variation of the packing intervals problem. Since you have no gaps and no overlaps you can treat it as a gaps and islands problem where the gaps are the change in flags.
;
WITH groups AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EfftDT) - ROW_NUMBER()OVER(PARTITION BY ID, FLAG ORDER BY EfftDT) grp
FROM ##INPUT
)
SELECT ID, MIN(groups.EfftDT) AS EffDT, MAX(TermDT) AS TermDT, FLAG
FROM groups
GROUP BY ID, FLAG, grp
ORDER BY ID, EffDT
This may not work as expected if you do find that you have overlaps or gaps.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 28, 2016 at 4:53 pm
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply