Overlaping Dates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Overlaping Dates

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-24 : 20:52:13

Folks, I have been strugling with this for some couple of weeks now.

I have a table with the following structure.

Pid SeqNo SDate Edate
1~~~~1~~~~4/2/2005~~~~12/1/2006
1~~~~2~~~~4/2/2005~~~~7/10/2006
1~~~~3~~~~6/5/2005~~~~9/6/2006

My objective is to recumpute the Sdate and Edtae such that the Sdate and Edate
of records with same Pid do not overlap. A record is not considered to overlap if the
Edate is the same as the Sdate of another record with same Pid.

Also the Sdate and Edate of a record with higher SeqNo takes priority over
a record with smaller Seqno.

4/2/05
|aaaaa|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|aaaaaaaaaaaaaaaaa--->Rec1
|bbbbb|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb---------------------------->Rec2
|<--->|cccccccccccccccccccccccccccccccccccccccccccccccccccccc|------------->Rec3

Final Ouput:

|bbbbb|cccccccccccccccccccccccccccccccccccccccccccccccccccccc|aaaaaaaaaaaaaaaa|

Pid SeqNo SDate Edate SDate2 Edate2
1~~~~1~~~~04/02/2005~~~~12/01/2006~~~~09/06/2006~~~~12/01/2006--->a
1~~~~2~~~~04/02/2005~~~~07/10/2006~~~~04/02/2005~~~~06/05/2005--->b
1~~~~3~~~~06/05/2005~~~~09/06/2006~~~~06/05/2005~~~~09/06/2006--->c

Hope this helps in explaining what I want

Thanks

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-25 : 07:34:19

Can somebody please advice me on how to skin thid Cat

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 07:40:53

Noone understands your graph.
Please use the [ code ] and [ /code ] tags (without spaces).

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 07:45:15

Are your dates formatted as DMY or MDY?

E 12°55'05.63"
N 56°04'39.26"

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-25 : 08:29:02

What code should I use in order to get spaces beween symbols?

For example |SPACE(8)| displays as || in preview mode.

Want to get my graph right.I guess the non response to my qustion is due to folks not understanding what I want.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 08:33:24

[ code ] dfsfd[ /code ]

BUT WITHOUT the space inbetween the tags...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-25 : 08:41:47

[code]DECLARE @Sample TABLE
(
pID INT,
seqNo INT,
sDate DATETIME,
eDate DATETIME
)

INSERT @Sample
SELECT 1, 1, '20050402', '20061201' UNION ALL
SELECT 1, 2, '20050402', '20060710' UNION ALL
SELECT 1, 3, '20050605', '20060906'

SELECT t1.pID,
t1.seqNo,
t1.sDate,
t1.eDate,
CASE
WHEN t1.eDate > t2.eDate THEN t2.eDate
ELSE t1.sDate
END AS sDate2,
CASE
WHEN t1.eDate >= t2.eDate THEN t1.eDate
ELSE t2.sDate
END AS eDate2
FROM (
SELECT pID,
seqNo,
sDate,
eDate
FROM @Sample
) AS t1
INNER JOIN (
SELECT pID,
seqNo,
MAX(seqNo) OVER (PARTITION BY pID) AS maxNo,
sDate,
eDate
FROM @Sample
) AS t2 ON t2.pID = t1.pID
AND t2.maxNo = t2.seqNo[/code]

E 12°55'05.63"
N 56°04'39.26"

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-25 : 22:03:46

Thanks Peso, you are the best !!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources