June 11, 2009 at 11:15 am
I'm faced with an issue that I have a solution to but I don't know if it is the most elegant or best for performance. What I'm trying to do is get a running date difference of a column with the last row same column (table is ordered by this column). Example:
Item - Date - DateDiff
Activity 1 - 10/5/2008 - 0
Activity 2 - 10/15/2008 - 10
Activity 3 - 10/22/2008 - 7
The only way I know to do this is using a subquery like this:
select a1.item, a1.date, datediff(dd, a1.date, isnull((select max(a2.date) from activities a2 where a2.date < a1.date),a1.date))
from activities a1
[/code]
This has to hit the table once for each row. Is there any way to do this with a window function or something? Thanks!
June 11, 2009 at 11:44 am
I'm pretty sure your requirements are more specific than what you have provided in your original post. If you could be more specific, provide DDL for your table(s), sample data for the table(s) (in a readily consummable format that can be cut/paste/run in SSMS), expected results based on the sample data that we could easily help you come up with a solution.
If you need help with my request, please read the first article I have referenced below in my signature block regarding asking for assistance.
June 11, 2009 at 12:54 pm
Avoid the inline query in your SELECT statement. You can use a row-number to work with a JOIN.
; WITH
t (Act, Dt, RowNum) AS
(
SELECT
Act,
Dt,
ROW_NUMBER() OVER (ORDER BY Dt)
FROM @t
)
SELECT
t1.*,
CASE
WHEN t2.Dt IS NULL THEN 0
ELSE DATEDIFF(DAY, t2.Dt, t1.Dt)
END
FROM t t1
LEFT JOIN t t2 ON t1.RowNum = t2.RowNum + 1
Flo
June 11, 2009 at 1:39 pm
Florian Reischl (6/11/2009)
Avoid the inline query in your SELECT statement. You can use a row-number to work with a JOIN.
; WITH
t (Act, Dt, RowNum) AS
(
SELECT
Act,
Dt,
ROW_NUMBER() OVER (ORDER BY Dt)
FROM @t
)
SELECT
t1.*,
CASE
WHEN t2.Dt IS NULL THEN 0
ELSE DATEDIFF(DAY, t2.Dt, t1.Dt)
END
FROM t t1
LEFT JOIN t t2 ON t1.RowNum = t2.RowNum + 1
Flo
Awesome, this is exactly what I was looking for! Thank you very much!!
June 11, 2009 at 1:40 pm
Lynn Pettis (6/11/2009)
I'm pretty sure your requirements are more specific than what you have provided in your original post. If you could be more specific, provide DDL for your table(s), sample data for the table(s) (in a readily consummable format that can be cut/paste/run in SSMS), expected results based on the sample data that we could easily help you come up with a solution.If you need help with my request, please read the first article I have referenced below in my signature block regarding asking for assistance.
This is actually a bit different than I described (the dates are reversed) but here's the code for anyone that's interested in messing around with it:
create table #temp(
id int,
theDate datetime)
insert into #temp values (1, '10/10/2008')
insert into #temp values (2, '10/15/2008')
insert into #temp values (3, '10/25/2008')
select * from #temp
drop table #temp
My way:
select a1.id
, a1.thedate as ThisDate
, (select min(a2.thedate) from #temp a2 where a2.thedate > a1.thedate) as NextDate
, datediff(dd, a1.thedate, isnull((select min(a2.thedate) from #temp a2 where a2.thedate > a1.thedate),getdate()))
from #temp a1;
Florian's way:
with cte (id, thedate, rownumber) as(
select id, theDate, row_number()over(order by theDate)
from #temp)
select cte.id
, cte.thedate as ThisDate
, cte2.thedate as NextDate
, isnull(datediff(dd, cte.thedate, cte2.thedate),datediff(dd, cte.thedate, getdate())) as DayDifference
from cte
left outer join cte as cte2
on cte.rownumber + 1 = cte2.rownumber
June 11, 2009 at 1:43 pm
Glad we could help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply