Merging pain - 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 2000 Forums

Transact-SQL (2000)

Merging pain

Author

Topic

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-29 : 09:49:50

Ok. Working with SQL 2000

Asked about this on 2k5, but it never quite worked. That is neither here nor there.

The problem: I have a bunch of records that need to be merged by certain criteria.

If the consultantid and employerid of one record match the same on another record, i need to compare the sd and ed (startdate and enddate).
IF they overlap(the startdate of record#2 is equal to the startdate of record#1, or the startdate of a record#2 falls within the date range of the startdate and enddate of record#1),then they get merged (min startdate and max end date).
Also, if they are contiguous(e.g. record#1 ends on 12/31/2000, and record#2 starts on 1/1/2001), the get merged in the same way.
Otherwise, they will stay as separate records.

I can most likely do this with a loop, but there must be a way to do it set based.

again, I am on SQL 2000.

Here is the sample input, and expected output:


declare @yak table (poaid int, consultantid int, employerid int,sd datetime,ed datetime)

insert into @yak(poaid,consultantid,employerid,sd,ed)


select 1790,1,225,'1999-01-01 00:00:00.000','2001-12-31 00:00:00.000'
union all select 1791,1,225,'2003-01-01 00:00:00.000','2007-12-31 00:00:00.000'
union all select 21,1,276,'1999-01-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 22,1,276,'2000-01-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1815,1,599,'1999-01-01 00:00:00.000','2004-12-31 00:00:00.000'
union all select 4479,1,599,'2007-01-01 00:00:00.000','2009-12-31 00:00:00.000'
union all select 1084,16,6588,'1996-10-01 00:00:00.000','2006-09-30 00:00:00.000'
union all select 4584,16,6588,'2007-10-01 00:00:00.000','2008-09-30 00:00:00.000'
union all select 1100,21,167,'1996-10-01 00:00:00.000','1998-12-31 00:00:00.000'
union all select 1101,21,167,'1997-01-01 00:00:00.000','1999-12-31 00:00:00.000'
union all select 1103,21,270,'1996-10-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1104,21,270,'1996-10-01 00:00:00.000','2009-12-31 00:00:00.000'
union all select 1415,30,4123,'1996-10-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1416,30,4123,'2003-01-01 00:00:00.000','2005-01-01 00:00:00.000'


Expected output:

poaid	c_id	e_id	sd			          ed
1790	1	225	1999-01-01 00:00:00.000	2001-12-31 00:00:00.000
1791	1	225	2003-01-01 00:00:00.000	2007-12-31 00:00:00.000
21	1	276	1999-01-01 00:00:00.000	2002-12-31 00:00:00.000
1815	1	599	1999-01-01 00:00:00.000	2004-12-31 00:00:00.000
4479	1	599	2007-01-01 00:00:00.000	2009-12-31 00:00:00.000
1084	16	6588	1996-10-01 00:00:00.000	2006-09-30 00:00:00.000
4584	16	6588	2007-10-01 00:00:00.000	2008-09-30 00:00:00.000
1100	21	167	1996-10-01 00:00:00.000	1999-12-31 00:00:00.000
1103	21	270	1996-10-01 00:00:00.000	2009-12-31 00:00:00.000
1415	30	4123	1996-10-01 00:00:00.000	2005-01-01 00:00:00.000

record 22 merged into 21, 1104 merged into 1103
record 1415 merged into 1416 because they are contiguous.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 11:12:54

[code]SELECT MIN(poaid) AS poaid,
consultantid,
employerid,
MIN(sd) AS sd,
MAX(ed) AS ed
FROM
(
SELECT *,
(SELECT MAX(poaid) FROM @yak
WHERE consultantid=y.consultantid
AND employerid=y.employerid
AND (sd<y.ed OR DATEDIFF(dd,y.ed,sd)=1)
AND ed>y.sd
AND poaid>y.poaid) AS MaxSeq
FROM @yak y
)t
GROUP BY t.consultantid,t.employerid,t.MaxSeq[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 11:47:13

See
http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 11:55:39

[code]declare @yak table (poaid int, consultantid int, employerid int,sd datetime,ed datetime,
primary key clustered (consultantid, employerid,sd, ed), seq int
)

insert into @yak(poaid,consultantid,employerid,sd,ed)
select 1790,1,225,'1999-01-01 00:00:00.000','2001-12-31 00:00:00.000'
union all select 1791,1,225,'2003-01-01 00:00:00.000','2007-12-31 00:00:00.000'
union all select 21,1,276,'1999-01-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 22,1,276,'2000-01-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1815,1,599,'1999-01-01 00:00:00.000','2004-12-31 00:00:00.000'
union all select 4479,1,599,'2007-01-01 00:00:00.000','2009-12-31 00:00:00.000'
union all select 1084,16,6588,'1996-10-01 00:00:00.000','2006-09-30 00:00:00.000'
union all select 4584,16,6588,'2007-10-01 00:00:00.000','2008-09-30 00:00:00.000'
union all select 1100,21,167,'1996-10-01 00:00:00.000','1998-12-31 00:00:00.000'
union all select 1101,21,167,'1997-01-01 00:00:00.000','1999-12-31 00:00:00.000'
union all select 1103,21,270,'1996-10-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1104,21,270,'1996-10-01 00:00:00.000','2009-12-31 00:00:00.000'
union all select 1415,30,4123,'1996-10-01 00:00:00.000','2002-12-31 00:00:00.000'
union all select 1416,30,4123,'2003-01-01 00:00:00.000','2005-01-01 00:00:00.000'

DECLARE @Seq INT,
@cid int,
@eid int,
@sd DATETIME,
@ed DATETIME

SELECT TOP 1
@Seq = 0,
@cid= consultantid,
@eid = employerid,
@sd = sd,
@ed = ed
FROM @yak
ORDER BY consultantid, employerid,
sd, ed

UPDATE @yak
SET @Seq = CASE
WHEN sd > @ed + 1 THEN @Seq + 1
WHEN consultantid > @cid THEN @Seq + 1
WHEN employerid > @eid THEN @Seq + 1
ELSE @Seq
END,
@sd = CASE
WHEN ed > @ed THEN sd
WHEN consultantid > @cid THEN sd
WHEN employerid > @eid THEN sd
ELSE @sd
END,
@ed= CASE
WHEN ed > @ed THEN ed
WHEN consultantid > @cid THEN ed
WHEN employerid > @eid THEN ed
ELSE @ed
END,
Seq = @Seq,
@cid = consultantid,
@eid = employerid

SELECT consultantid,
employerid,
MIN(sd) AS DateFrom,
MAX(ed) AS DateTo
FROM @yak
GROUP BY consultantid,
employerid,
Seq
ORDER BY Seq[/code]

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-29 : 12:35:07

Visakh: That still does not work. It still does a simple min and max, without regard to weather the dates intersect or not. Thanks for the effort though.

Peso: Very, very nice. Wish I had access to the weblogs here at work, but they block them. Your code works like a charm. Slight tweaking on my end and I should be set! (I didn't give you guys the WHOLE picture, just the part that was vexing me). Thank you very much!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 14:37:12

Just make sure you use "not equal to <>" instead of "greater than >".

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 23:56:03

quote:
Originally posted by DonAtWork

Visakh: That still does not work. It still does a simple min and max, without regard to weather the dates intersect or not. Thanks for the effort though.

Peso: Very, very nice. Wish I had access to the weblogs here at work, but they block them. Your code works like a charm. Slight tweaking on my end and I should be set! (I didn't give you guys the WHOLE picture, just the part that was vexing me). Thank you very much!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

actually it needed a small tweak

SELECT MIN(poaid) AS poaid,
consultantid,
employerid,
MIN(sd) AS sd,
MAX(ed) AS ed
FROM
(
SELECT *,
(SELECT MAX(poaid) FROM @yak
WHERE consultantid=y.consultantid
AND employerid=y.employerid
AND (sd<y.ed OR DATEDIFF(dd,y.ed,sd)=1)
AND ed>y.sd
AND poaid>=y.poaid) AS MaxSeq
FROM @yak y
)t
GROUP BY t.consultantid,t.employerid,t.MaxSeq

output

---------------------------------
poaid       consultantid employerid  sd                      ed
----------- ------------ ----------- ----------------------- -----------------------
1790        1            225         1999-01-01 00:00:00.000 2001-12-31 00:00:00.000
1791        1            225         2003-01-01 00:00:00.000 2007-12-31 00:00:00.000
21          1            276         1999-01-01 00:00:00.000 2002-12-31 00:00:00.000
1815        1            599         1999-01-01 00:00:00.000 2004-12-31 00:00:00.000
4479        1            599         2007-01-01 00:00:00.000 2009-12-31 00:00:00.000
1084        16           6588        1996-10-01 00:00:00.000 2006-09-30 00:00:00.000
4584        16           6588        2007-10-01 00:00:00.000 2008-09-30 00:00:00.000
1100        21           167         1996-10-01 00:00:00.000 1999-12-31 00:00:00.000
1103        21           270         1996-10-01 00:00:00.000 2009-12-31 00:00:00.000
1415        30           4123        1996-10-01 00:00:00.000 2005-01-01 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources