SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Enddate within 14 days of next Row startdate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Husman
Starting Member

13 Posts

Posted - 06/09/2012 :  16:09:53  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000