| Author |
Topic  |
|
|
Husman
Starting Member
13 Posts |
Posted - 06/09/2012 : 16:09:53
|
Hi
I 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 date
can someone please point me to wright direction?
DECLARE @sampleData TABLE ( id int, StartDate datetime, DEP char(1), EndDate datetime )
SET DATEFORMAT DMY
INSERT 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/2011 1 16/03/2011 B 20/05/2011 1 06/08/2011 B 08/08/2011 2 06/09/2011 B 08/09/2011
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 06/10/2012 : 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. Eliot
Muhammad Al Pasha |
 |
|
| |
Topic  |
|
|
|