Author |
Topic |
Husman
Starting Member
13 Posts |
Posted - 2012-06-09 : 16:09:53
|
HiI want to check End date is within 14 days of next row start date, If within 14 days than use first row start date and last row end datecan someone please point me to wright direction?DECLARE @sampleData TABLE ( id int, StartDate datetime, DEP char(1), EndDate datetime)SET DATEFORMAT DMYINSERT INTO @sampleData VALUES(1,'14/02/2011','A','14/02/2011')INSERT INTO @sampleData VALUES(1,'14/02/2011','A','15/02/2011')INSERT INTO @sampleData VALUES(1,'16/03/2011','A','25/04/2011')INSERT INTO @sampleData VALUES(1,'05/05/2011','A','05/05/2011')INSERT INTO @sampleData VALUES(1,'13/05/2011','B','16/05/2011')INSERT INTO @sampleData VALUES(1,'20/05/2011','B','20/05/2011')INSERT INTO @sampleData VALUES(1,'06/08/2011','B','08/08/2011')INSERT INTO @sampleData VALUES(2,'06/09/2011','B','08/09/2011')The results will look like this ID StartDate DEP EndDate 1 14/02/2011 A 15/02/20111 16/03/2011 B 20/05/20111 06/08/2011 B 08/08/20112 06/09/2011 B 08/09/2011 |
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2012-06-10 : 04:19:16
|
Try something like this:DECLARE @T TABLE( id int, StartDate datetime, DEP char(1), EndDate datetime, row_num int PRIMARY KEY(id, row_num));INSERT INTO @T(id, StartDate, DEP, EndDate, row_num) SELECT T1.id, T1.StartDate, T1.DEP, T1.EndDate, ROW_NUMBER() OVER(PARTITION BY T1.id ORDER BY T1.StartDate) AS row_num FROM @sampleData AS T1 WHERE NOT EXISTS(SELECT T2.StartDate, T2.EndDate FROM @sampleData AS T2 WHERE T2.id = T1.id AND (T2.StartDate <> T1.StartDate OR T2.EndDate <> T1.EndDate OR T2.DEP <> T1.DEP) AND T2.EndDate >= DATEADD(DAY, -14, T1.StartDate) AND T2.EndDate <= T1.StartDate);SELECT T1.id, T1.StartDate, ISNULL(T3.DEP, T1.DEP) AS DEP, ISNULL(T3.EndDate, T1.EndDate) AS EndDate FROM @T AS T1 LEFT OUTER JOIN @T AS T2 ON T2.id = T1.id AND T2.row_num = T1.row_num + 1 OUTER APPLY (SELECT TOP(1) T3.DEP, T3.EndDate FROM @sampleData AS T3 WHERE T3.id = T1.id AND T3.StartDate >= T1.StartDate AND T3.StartDate < T2.StartDate ORDER BY T3.EndDate DESC) AS T3 For us, there is only the trying. The rest is not our business. ~T.S. EliotMuhammad Al Pasha |
 |
|
|
|
|