Stamp records with consecutive dates

For the following data table, the requirement is to assign/stamp the same unique number to the Group column if EndDate/StartDate is consecutive for the same Medicine.
ID    Medicine  StartDate       EndDate        Group
1     1              19900601      19940630       0
2     1              19900712      19940606       0
3     1              19940607      19940905       0
4     1              19940906      19961203       0
5     1              20050806      20080318       0
6     1              20080319      20370101       0
7     2              19901211      19940228       0
8     2              19910619      19940209       0
9     2              19940210      19950831       0
10   2              19940210      19940731       0
The expected output will be (assign the first start date as the group number):
ID    Medicine  StartDate       EndDate        Group
1     1              19900601      19940630       0
2     1              19900712      19940606       19900712
3     1              19940607      19940905       19900712
4     1              19940906      19961203       19900712
5     1              20050806      20080318       20050806
6     1              20080319      20370101       20050806
7     2              19901211      19940228       0
8     2              19910619      19940209       19910619
9     2              19940210      19950831       19910619
10   2              19940210      19940731       0
It is not easy to solve by set based statement only. The basic idea is to calculate the NextStartDate and Delta of EndDate of NextStartDate for each record.
For the first record that delat is 1 (i.e., consecutive to next record), get its StartDate as @Group number, find the @eID=MAX(ID) for this unbroken consutive series
(i.e. all delta is one and with the same medicine), and update the group to @Group with the ID range. Then find next ID with delta is 1 after @eID
IF OBJECT_ID(‘tempdb..#data’) is not null
DROP TABLE #Data
CREATE TABLE #Data(ID int not null, StartDate int not null, EndDate int not null,Medicine int not null, [Group] int not null, NextStartDate int default 0, Delta int
default 0)
INSERT #Data (ID,Medicine,StartDate,EndDate,[Group])
SELECT 1, 1,         19900601,  19940630,  0 UNION ALL
SELECT 2, 1,         19900712,  19940606,  0 UNION ALL
SELECT 3, 1,         19940607,  19940905,  0 UNION ALL
SELECT 4, 1,         19940906,  19961203,  0 UNION ALL
SELECT 5, 1,         20050806,  20080318,  0 UNION ALL
SELECT 6, 1,         20080319,  20370101,  0 UNION ALL
SELECT 7, 2,         19901211,  19940228,  0 UNION ALL
SELECT 8, 2,         19910619,  19940209,  0 UNION ALL
SELECT 9, 2,         19940210,  19950831,  0 UNION ALL
SELECT 10, 2,         19940210,  19940731,  0
— Get all the NextStartDate and Delta
UPDATE A
SET NextStartDate=B.StartDate,
 Delta=B.StartDate-A.EndDate
FROM #Data A
INNER JOIN #Data B
ON A.ID=B.ID-1
 AND A.Medicine=B.Medicine
SELECT * FROM #Data
DECLARE @ID int,@eID int,@GroupID int,@Medicine int
— First record consecutive with next one
SELECT @ID = MIN(ID) FROM #Data WHERE Delta=1
WHILE @ID is not null
BEGIN
 — Get the @GroupID, @Medicine
 SELECT @GroupID=StartDate,@Medicine=Medicine FROM #Data WHERE ID=@ID
 — Find the end ID of this unbroken consecutive series
 SELECT TOP 1 @eID=ID FROM #Data WHERE ID>@ID AND Delta<>1 AND Medicine=@Medicine ORDER BY ID;
 — Update the GroupID in this range
 UPDATE #Data
 SET [Group]=@GroupID
 WHERE ID>=@ID AND ID<=@eID
 — Process next series
 SELECT @ID = MIN(ID) FROM #Data WHERE ID>@eID AND Delta=1
END
SELECT ID,Medicine,StartDate,EndDate,[Group] FROM #Data
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s