Date Diff - 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

General SQL Server Forums

New to SQL Server Programming

Date Diff

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-04 : 13:18:21

ln ed
29845 2011-01-07
29845 2011-01-19
29845 2011-01-28
29845 2011-02-01
29845 2011-02-09
29845 2011-02-28

I need to find the diff between the ed to the next ed in new column.so in first case 0,then diff b/w 2011-01-07 and 2011-01-19,then diff b/w 2011-01-19 and 2011-01-28 and so on.
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-04 : 13:45:18

Check the DATEDIFF function:
http://msdn.microsoft.com/en-us/library/ms189794.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 13:46:25

This:

select
	a.*,
	datediff(dd,coalesce(b.ed,a.ed),a.ed)
from
	YourTable a
	outer apply
	( select top 1 x.ed from YourTable x where x.ed <a.ed order by x.ed desc) b

On the outside chance that you are on SQL 2012, it is a little easier and perhaps faster to do it like this:

select
	*,
	datediff(dd,COALESCE(max(ed) over( order by ed rows between 1 preceding and 1 preceding ),ed), ed)
from
	YourTable

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-04 : 13:46:43

WITH cte
AS (SELECT ln
,ed
FROM #abc
)
SELECT [current].ln
,[current].ed
,DATEDIFF(DAY,next.ed,[current].ed)
FROM cte AS [current]
LEFT JOIN cte AS [next]
ON [next].ed = (SELECT MIN(ed)
FROM cte

I have come this so far,,but actually i need the diff between two consecutives,,above query gives me this result---

ln ed (No column name)
29845 2011-01-07 0
29845 2011-01-19 12
29845 2011-01-28 21
29845 2011-02-01 25
29845 2011-02-09 33
29845 2011-02-28 52

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-04 : 13:48:37

quote:
Originally posted by sunitabeck

This:
select
a.*,
datediff(dd,coalesce(b.ed,a.ed),a.ed)
from
YourTable a
outer apply
( select top 1 x.ed from YourTable x where x.ed <a.ed order by x.ed desc) b
On the outside chance that you are on SQL 2012, it is a little easier and perhaps faster to do it like this:
select
*,
datediff(dd,COALESCE(max(ed) over( order by ed rows between 1 preceding and 1 preceding ),ed), ed)
from
YourTable

Great.Thanks Sunita.Superb

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 15:27:24

You are welcome. Glad to help.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-04 : 16:51:53

Why use MAX instead if LEAD or LAG?

DECLARE	@Sample TABLE
	(
		[In] INT,
		Ed DATE
	)

INSERT	@Sample
VALUES	(29845, '2011-01-07'),
	(29845, '2011-01-19'),
	(29845, '2011-01-28'),
	(29845, '2011-02-01'),
	(29845, '2011-02-09'),
	(29845, '2011-02-28')

-- SwePeso
SELECT	[In],
	Ed,
	DATEDIFF(DAY, Ed, LEAD(Ed) OVER (PARTITION BY [In] ORDER BY Ed)),
	DATEDIFF(DAY, Ed, LEAD(Ed, 1, Ed) OVER (PARTITION BY [In] ORDER BY Ed)),
	DATEDIFF(DAY, Ed, LAG(Ed) OVER (PARTITION BY [In] ORDER BY Ed)),
	DATEDIFF(DAY, Ed, LAG(Ed, 1, Ed) OVER (PARTITION BY [In] ORDER BY Ed))
FROM	@Sample

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-04 : 17:18:02

quote:
Originally posted by SwePeso

Why use MAX instead if LEAD or LAG?

Ha! That is easy to answer. Because I haven't gotten that far in BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources