quote:

---

Originally posted by DonAtWork

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

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

select 59,1,3234,'2001-01-01 00:00:00','2008-12-31 00:00:00'
union all select 60,1,3234,'2002-01-01 00:00:00','2012-12-31 00:00:00'
union all select 1882,1,5564,'1996-10-01 00:00:00','1998-12-31 00:00:00'
union all select 1883,1,5564,'1996-10-01 00:00:00','1999-12-31 00:00:00'
union all select 1896,1,5786,'1996-10-01 00:00:00','2001-12-31 00:00:00'
union all select 1897,1,5786,'1996-10-01 00:00:00','2003-12-31 00:00:00'
union all select 1898,1,5786,'2004-01-01 00:00:00','2007-12-31 00:00:00'
union all select 1899,1,5786,'2008-03-01 00:00:00','2009-12-31 00:00:00'



Expected output:


POAID  C_ID    E_ID         Sdate                 Edate
59	   1	   3234	2001-01-01 00:00:00.000	2012-12-31 00:00:00.000
1882	   1	   5564	1996-10-01 00:00:00.000	1999-12-31 00:00:00.000
1896	   1	   5786	1996-10-01 00:00:00.000	2007-12-31 00:00:00.000
1899	   1	   5786	2008-03-01 00:00:00.000	2009-12-31 00:00:00.000

I asked this yesterday, but messed up the sample data. I double checked today.

To repost my original question:
I need to combine records that have overlapping dates, or that are contiguous (12/31 end date and next one is 1/1 start date) into one record, using the first POAID as the base ID. The start date would be the minimum date, the end date would be the max date.

POAID is the PK, consultantid and employerid are FKs.
All the datetime fields are rounded to date only (i.e. the timestamp is all '0') for each record.


The answer from yesterday was this:

SELECT MIN(poaid) AS POAID,
consultantid AS C_ID,
employerid AS E_ID,
MIN(sd) AS Sdate,
MAX(ed) AS Edate
FROM @yak
GROUP BY consultantid,employerid

This would combine the records with the minimum date and the maximum date, but does not check to see if the dates 1) Overlap or 2) are contiguous.

Sorry for the confusion. Work has been getting to me lately. :(



[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

---

SELECT MIN(y1.poaid) AS POAID,
MIN(y1.consultantid) AS C_ID,
y1.employerid,
MIN(y1.sd) AS Sdate,
MAX(y1.ed) AS Edate
FROM @yak y1
OUTER APPLY (SELECT TOP 1 poaid
             FROM @yak
             WHERE employerid=y1.employerid
             AND poaid>y1.poaid
             AND (sd<=y1.ed OR DATEDIFF(dd,y1.ed,sd)=1)
             AND ed>=y1.sd
             ORDER BY poaid DESC)m
GROUP BY y1.employerid,COALESCE(m.poaid,0)

quote:

---

Originally posted by DonAtWork

very nice. Not familiar with CROSS APPLY, but I will be soon. 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

---

quote:

---

Originally posted by DonAtWork

Got it. Thanks again

[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

---