Complex Date Comparison- Calling All T-SQL Gurus

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)

Complex Date Comparison- Calling All T-SQL Gurus

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-21 : 13:38:45

I have a table (TableX) will the following records.

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

2~~~~1~~~~3/2/2007~~~~6/30/2007
2~~~~2~~~~8/2/2007~~~~11/2/2008
2~~~~3~~~~5/4/2007~~~~10/2/2008

3~~~~1~~~~4/3/2006~~~~10/1/2006
3~~~~2~~~~2/1/2005~~~~6/2/2006

4~~~~1~~~~1/1/2007~~~~4/3/2007
4~~~~2~~~~8/3/2007~~~~2/3/2008
4~~~~3~~~~2/20/2007~~~~11/12/2007

I will like to create TableY with out of TabeX such that the overlap between the Sdate
and Edate for records with same Pid is not more than a day.
Also when the Sdate and Edate of records with same Pid overlap, the record with higher
SeqNo takes priority over the lower Seqno.

My expected resuts are as follows:

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

2~~~~1~~~~03/02/2007~~~~06/30/2007~~~~03/02/2007~~~~05/04/2007
2~~~~2~~~~08/02/2007~~~~11/02/2008~~~~10/02/2008~~~~11/02/2008
2~~~~3~~~~05/04/2007~~~~10/02/2008~~~~05/04/2007~~~~10/02/2008

3~~~~1~~~~04/03/2006~~~~10/01/2006~~~~06/02/2006~~~~10/01/2006
3~~~~2~~~~02/01/2005~~~~06/02/2006~~~~02/01/2005~~~~06/02/2006

4~~~~1~~~~01/01/2007~~~~04/03/2007~~~~01/01/2007~~~~02/20/2007
4~~~~2~~~~08/03/2007~~~~02/03/2008~~~~11/12/2007~~~~02/03/2008
4~~~~3~~~~02/20/2007~~~~11/12/2007~~~~02/20/2007~~~~11/12/2007

Any assistance will be very much welcomed

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-22 : 00:26:09

Can u explain briefly what u want?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 01:27:02

your sample output doesnt make much sense. can you explain how you think will the date values be got?

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-22 : 08:48:21

Ok, I will explain :

Lets consider the records with Pid 1

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

-We start with the record with the highest SeqNo, in this case this will be Record 3 so Sdate2=6/5/22205
and Edate2 =9/6/2006
-We see that Record 3 overlaps with both Record 2 and 1 but the end date of record 2 is before record 1
and since record 2 takes priority over record 1 ,the Sdate2 of record 2 will be 4/2/2005
andEdate2=6/5/2005 Sdate of Recod 3)
-Next we see that the Edate of Record 1 is greater than Record 2 and 3,
hence the Sdate2 and Edate2 of record 1 are 9/6/2006 and 12/1/2006 respectively.

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

Final Ouput:

|bbbbb|ccccccccccccccccccccccccccccccccccccccccccccccccccccccc|aaaaaaaaaaaaaaaaaa|

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 08:54:30

Do you have any primary key column?

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-22 : 09:17:19

No, there is no primary key column.
But I think we can add an autoId field as a primary key

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-22 : 20:35:41

I am tring to use a cursor approach to solve this problem but believes there should
be a better approach.

Will be happy if somebody could point me in the right direction.

--Table to Hold Initial Data
DECLARE @TableX TABLE(Pid INT, SeqNo INT, SDate SMALLDATETIME, Edate SMALLDATETIME)
DECLARE @Pid AS INT,
@SeqNo AS INT,
@SDate AS SMALLDATETIME,
@Edate AS SMALLDATETIME,
@Sdate2 AS SMALLDATETIME,
@Edate2 AS SMALLDATETIME

--Table to hold results
DECLARE @TableY TABLE (Pid INT, SeqNo INT, SDate SMALLDATETIME, Edate SMALLDATETIME,
SDate2 SMALLDATETIME, Edate2 SMALLDATETIME)

INSERT INTO @Tablex(Pid, SeqNo, SDate, Edate)

SELECT 1,1,'4/2/2005','12/1/2006' UNION ALL
SELECT 1,2,'4/2/2005','7/10/2006' UNION ALL
SELECT 1,3,'6/5/2005','9/6/2006' UNION ALL
SELECT 2,1,'3/2/2007','6/30/2007' UNION ALL
SELECT 2,2,'8/2/2007','11/2/2008' UNION ALL
SELECT 2,3,'5/4/2007','10/2/2008' UNION ALL
SELECT 3,1,'4/3/2006','10/1/2006' UNION ALL
SELECT 3,2,'2/1/2005','6/2/2006' UNION ALL
SELECT 4,1,'1/1/2007','4/3/2007' UNION ALL
SELECT 4,2,'8/3/2007','2/3/2008' UNION ALL
SELECT 4,3,'2/20/2007','11/12/2007'

DECLARE TableX_Cursor CURSOR FOR
SELECT Pid, SeqNo, SDate, Edate
FROM @TableX ORDER BY Pid, SeqNo DESC

OPEN TableX_Cursor

FETCH NEXT FROM TableX_Cursor
INTO @Pid , @SeqNo, @SDate, @Edate

WHILE @@FETCH_STATUS = 0
BEGIN

/*
Not sure of the type of comparisons that should take place before inserting records into the
the final table

INSERT INTO @TableY VALUES (@Pid,@SeqNo,@SDate,@Edate,@Sdate2,@Edate2)

*/

FETCH NEXT FROM TableX_Cursor
INTO @Pid , @SeqNo, @SDate, @Edate

END

CLOSE TableX_Cursor
DEALLOCATE TableX_Cursor

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-23 : 07:34:59

Has somebody been able to Figure out how to resolve this?

ucal
Yak Posting Veteran

72 Posts

Posted - 2009-02-23 : 19:41:09

Will appreciate it if somebody can point me in the right direction.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources