how to find difference between two successive date records

Question

Ten Centuries

Points: 1219

May 10, 2005 at 6:38 am

/**A strong positive mental attitude will create more miracles than any wonder drug**/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

CREATE TABLE #test

(

dt DATETIME

)

INSERT INTO #test

SELECT '20050504'

UNION ALL

SELECT '20050510'

UNION ALL

SELECT '20050511'

UNION ALL

SELECT '20050515'

UNION ALL

SELECT '20050517'

SELECT MIN(t1.dt) [DATE], ISNULL(DATEDIFF(dd,MAX(t2.dt), MAX(t1.dt)),0) [DATE DIFF]

FROM #test t1 LEFT JOIN #test t2 ON t1.dt>t2.dt

GROUP BY t1.dt

ORDER BY t1.dt

DROP TABLE #test

DATE DATE DIFF

------------------------------------------------------ -----------

2005-05-04 00:00:00.000 0

2005-05-10 00:00:00.000 6

2005-05-11 00:00:00.000 1

2005-05-15 00:00:00.000 4

2005-05-17 00:00:00.000 2

(5 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

VivekKumar Gupta Ten Centuries Points: 1219 More actions · Answer 2

Thanks Frank! This really helped me solving my problem.

/**A strong positive mental attitude will create more miracles than any wonder drug**/