Joining dates - 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)

Joining dates

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2009-06-19 : 09:48:08

Is it possible to join 2 rows into one when the end and start times are the same.

Example

UserId TypeId StartDate EndDate
1 1 1/1/2009 8:00:00 AM 1/1/2009 3:00:00 PM
1 2 1/1/2009 3:00:00 PM 1/1/2009 5:00:00 PM

I would like to get this:
UserId StartDate EndDate
1 1/1/2009 8:00:00 AM 1/1/2009 5:00:00 PM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-19 : 10:05:34

[code]
select UserId, min(StartDate), max(EndDate)
from yourtable
group by UserId
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-19 : 10:09:48

You can do a self-join

Jim

DECLARE @Table TABLE (UserId int, TypeId int, StartDate datetime ,EndDate datetime)

INSERT INTO @table
SELECT 1, 1, '1/1/2009 8:00:00 AM', '1/1/2009 3:00:00 PM' UNION ALL
SELECT 1, 2, '1/1/2009 3:00:00 PM ','1/1/2009 5:00:00 PM'
/*
1 1/1/2009 8:00:00 AM 1/1/2009 5:00:00 PM
*/

SELECT a.UserID,a.StartDate,b.EndDate
FROM @table a
INNER JOIN
@table b
ON
a.userid = b.userid
and a.enddate = b.startdate

Togaspoon
Starting Member

42 Posts

Posted - 2009-06-19 : 10:26:54

khtan,

That doesn't work if there is a time gap between the 2 dates.

Jim,

That works if I have 2 dates but not 3, or if I have dates that have no matching time

DECLARE @Table TABLE (UserId int, TypeId int, StartDate datetime ,EndDate datetime)

INSERT INTO @table
SELECT 1, 1, '1/1/2009 8:00:00 AM', '1/1/2009 1:00:00 PM' UNION ALL
SELECT 1, 1, '1/1/2009 1:00:00 PM', '1/1/2009 3:00:00 PM' UNION ALL
SELECT 1, 2, '1/1/2009 3:00:00 PM ','1/1/2009 5:00:00 PM' UNION ALL
SELECT 2, 2, '1/2/2009 3:00:00 PM ','1/2/2009 4:00:00 PM' UNION ALL
SELECT 2, 2, '1/2/2009 4:30:00 PM ','1/2/2009 5:00:00 PM'

SELECT *
FROM @Table

SELECT a.UserID,a.StartDate,b.EndDate
FROM @table a
INNER JOIN
@table b
ON
a.userid = b.userid
and a.enddate = b.startdate

I would like this to give me
UserId.....StartDate.....EndDate
1......1/1/2009 08:00:00 AM.....1/1/2009 05:00:00 PM
2......1/2/2009 03:00:00 PM.....1/1/2009 04:00:00 PM
2......1/2/2009 04:30:00 PM.....1/1/2009 05:00:00 PM

Lattice
Starting Member

1 Post

Posted - 2009-06-19 : 11:24:33

quote:
Originally posted by Togaspoon

khtan,

That doesn't work if there is a time gap between the 2 dates.

Jim,

That works if I have 2 dates but not 3, or if I have dates that have no matching time

DECLARE @Table TABLE (UserId int, TypeId int, StartDate datetime ,EndDate datetime)

INSERT INTO @table
SELECT 1, 1, '1/1/2009 8:00:00 AM', '1/1/2009 1:00:00 PM' UNION ALL
SELECT 1, 1, '1/1/2009 1:00:00 PM', '1/1/2009 3:00:00 PM' UNION ALL
SELECT 1, 2, '1/1/2009 3:00:00 PM ','1/1/2009 5:00:00 PM' UNION ALL
SELECT 2, 2, '1/2/2009 3:00:00 PM ','1/2/2009 4:00:00 PM' UNION ALL
SELECT 2, 2, '1/2/2009 4:30:00 PM ','1/2/2009 5:00:00 PM'

SELECT *
FROM @Table

SELECT a.UserID,a.StartDate,b.EndDate
FROM @table a
INNER JOIN
@table b
ON
a.userid = b.userid
and a.enddate = b.startdate

I would like this to give me
UserId.....StartDate.....EndDate
1......1/1/2009 08:00:00 AM.....1/1/2009 05:00:00 PM
2......1/2/2009 03:00:00 PM.....1/1/2009 04:00:00 PM
2......1/2/2009 04:30:00 PM.....1/1/2009 05:00:00 PM

You can try this

DECLARE @Table TABLE (UserId int, TypeId int, StartDate datetime ,EndDate datetime)

INSERT INTO @table
SELECT 1, 1, '1/1/2009 8:00:00 AM', '1/1/2009 1:00:00 PM' UNION ALL
SELECT 1, 1, '1/1/2009 1:00:00 PM', '1/1/2009 3:00:00 PM' UNION ALL
SELECT 1, 2, '1/1/2009 3:30:00 PM ','1/1/2009 5:35:12 PM' UNION ALL
SELECT 2, 2, '1/1/2009 3:30:00 PM ','1/2/2009 4:30:00 PM' UNION ALL
SELECT 2, 2, '1/2/2009 4:30:00 PM ','1/2/2009 5:00:00 PM'

SELECT * FROM @Table

-- for consecutives dates
select a.UserId,min(a.StartDate) as StartDate,max(a.EndDate) as EndDate
from
( SELECT a.UserId,a.StartDate,a.EndDate
FROM @table a INNER JOIN @table b
ON a.userid = b.userid
and a.enddate = b.startdate

union
SELECT a.UserId,a.StartDate,a.EndDate
FROM @table a INNER JOIN @table b
ON b.userid = a.userid
and b.enddate = a.startdate
) a
group by a.UserId

union

-- for interrupted dates
select a.UserId,a.StartDate,a.EndDate
from (
SELECT a.UserId,a.StartDate,a.EndDate
FROM @table a left JOIN @table b
ON a.userid = b.userid
and a.enddate = b.startdate
where b.startdate is null) a
inner join
( SELECT a.UserId,a.StartDate,a.EndDate
FROM @table a left JOIN @table b
ON b.userid = a.userid
and b.enddate = a.startdate
where b.startdate is null) b
on a.UserId = b.UserId
and a.StartDate= b.StartDate
and a.EndDate= b.EndDate

Togaspoon
Starting Member

42 Posts

Posted - 2009-06-19 : 11:59:23

Wow, nice job!

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources