combining rows with overlapping dates

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

Other Forums

MS Access

combining rows with overlapping dates

Author

Topic

zimmermm
Starting Member

1 Post

Posted - 2008-08-05 : 15:40:20

I have a set of records that I want to group together and potentially place in a single row, am not sure how to do so, or if it is possible. Each record has a two dates that denote the begin_date and end_date of a particular person (personID) being in a certain location. I want to group records where the same personID is sent to multiple locations (location)--i.e., when sorted by end date in descending order, if the end_date in the first listed_record matches the begin date in the next listed record. Any ideas??

for ex,
the current records look like:

Table: Placement

PersonID#, begin_date, end_date, location
01, begin_dateA, end_dateA, locationnameA
01, begin_dateB, end_dateB, locationnameB

The result I'd like is:
PersonID#01, begin_dateA, end_dateA, locationnameA, begin_dateB, end_dateB, locationnameB

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-06 : 07:38:23

SELECT *
FROM Table1 AS t1
INNER JOIN Table1 AS t2 ON t2.PersonID = t1.PersonID
WHERE t1.StartDate < t2.EndDate AND t1.EndDate > t2.StartDate

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources