Combining records - 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)

Combining records

Author

Topic

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-23 : 10:46:38

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.

There are a TON of records. I have them narrowed down to just the ones that have more than one POA (ones that have matching consultantid and employerid). Now I need to update these records. I just cant seem to do it SET based...


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'

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

[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-23 : 11:19:52

[code]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[/code]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-23 : 11:28:07

Awesome. Thanks. Now I should be able to apply that to the monster I have inherited.

[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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-09-23 : 11:42:18

Upon closer inspection, this did not quite work.

Expected output was :


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

Actual output from code:


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

It did not take into consideration that POAID 1898 is NOT contiguous with the previous records. :(

sorry. here is the CORRECT insert:

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'

[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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources