how to find date difference between records

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)

how to find date difference between records

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2009-01-15 : 07:11:59

Hi,

How to find the date different in hour between two records with nearest date time value, and it must be compared in same Group...

Group FinishDateTime
------- ---------------
1 03-01-2009 00:00
1 13-01-2009 22:00
1 08-01-2009 03:00
2 01-01-2009 20:00
2 13-01-2009 10:00
2 10-01-2009 10:00
2 27-10-2008 09:00
3 20-10-2008 00:00
3 29-11-2008 00:00
3 20-10-2008 09:00
3 01-01-2009 00:00

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 07:18:01

[code]DECLARE @Sample TABLE
(
[Group] INT,
FinishDateTime DATETIME
)

SET DATEFORMAT DMY

INSERT @Sample
(
[Group],
FinishDateTime
)
SELECT 1, '03-01-2009 00:00' UNION ALL
SELECT 1, '13-01-2009 22:00' UNION ALL
SELECT 1, '08-01-2009 03:00' UNION ALL
SELECT 2, '01-01-2009 20:00' UNION ALL
SELECT 2, '13-01-2009 10:00' UNION ALL
SELECT 2, '10-01-2009 10:00' UNION ALL
SELECT 2, '27-10-2008 09:00' UNION ALL
SELECT 3, '20-10-2008 00:00' UNION ALL
SELECT 3, '29-11-2008 00:00' UNION ALL
SELECT 3, '20-10-2008 09:00' UNION ALL
SELECT 3, '01-01-2009 00:00'

SELECT s.[Group],
s.FinishDateTime,
DATEDIFF(MINUTE, f.g, s.FinishDateTime) / 60.0E
FROM @Sample AS s
CROSS APPLY (
SELECT MAX(FinishDateTime)
FROM @Sample AS x
WHERE x.[Group] = s.[Group]
AND x.FinishDateTime < s.FinishDateTime
) AS f(g)
ORDER BY s.[Group],
s.FinishDateTime[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources