Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Enddate within 14 days of next Row startdate

Author  Topic 

Husman
Starting Member

13 Posts

Posted - 2012-06-09 : 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

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. Eliot

Muhammad Al Pasha
Go to Top of Page
   

- Advertisement -