| Author |
 Topic  |
|
|
shilpash
Yak Posting Veteran
72 Posts |
 Posted - 05/04/2012 : 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
Flowing Fount of Yak Knowledge
3833 Posts |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/04/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 05/04/2012 : 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
Yak Posting Veteran
72 Posts |
Posted - 05/04/2012 : 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 |
Edited by - shilpash on 05/04/2012 13:49:24 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/04/2012 : 15:27:24
|
| You are welcome. Glad to help.) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 05/04/2012 : 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" |
Edited by - SwePeso on 05/04/2012 16:53:22 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/04/2012 : 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. |
|
|
| |
Topic |
|
|
|
Date Diff
