October 29, 2009 at 7:24 am
Guys:
I have following table with two columns in my DB.
c1 c2
26/10/2009 38
27/10/2009 34
28/10/2009 29
29/10/2009 30
I want Output should be like :
From date of C1 To date of C1 Difference
26/10/2009 27/10/2009 4
27/10/2009 28/10/2009 5
28/10/2009 20/10/2009 -1
Can anyone please tell me the script for it...
Regards
Jus
October 29, 2009 at 7:54 am
What have you tried so far?
Look up ROW_NUMBER in BOL and have a look. The idea would be to number all the rows then join the table to itself on RN + 1.
October 29, 2009 at 8:05 am
Garadin..This was plain self join i know....Some time back i have seen a solution to simillar kind of problem in SSC in a different form, i guess they were using CTEs...I wanted to see that solution...
October 29, 2009 at 8:11 am
Jus (10/29/2009)
Garadin..This was plain self join i know....Some time back i have seen a solution to simillar kind of problem in SSC in a different form, i guess they were using CTEs...I wanted to see that solution...
There are many ways to solve this kind of problem. I will note that most people use ROW_NUMBER in a CTE.
October 29, 2009 at 8:15 am
You can solve it even without ROW_NUMBER().
DECLARE @Table TABLE (
c1 datetime,
c2 int
)
INSERT INTO @Table
SELECT
'10/26/2009', 38 UNION ALL SELECT
'10/27/2009', 34 UNION ALL SELECT
'10/28/2009', 29 UNION ALL SELECT
'10/29/2009', 30
SELECT *, diff = c2 - (
SELECT c2
FROM @Table
WHERE c1 = nextDate
)
FROM (
SELECT T.c1, T.c2,
nextDate = (
SELECT MIN(T1.c1)
FROM @Table AS T1
WHERE T.c1 < T1.c1
)
FROM @Table AS T
) AS SubQry
WHERE nextDate IS NOT NULL
-- Gianluca Sartori
October 29, 2009 at 8:19 am
And with ROW_NUMBER
;WITH CTE (N, c1, c2) AS (
SELECT ROW_NUMBER() OVER (ORDER BY c1), c1, c2
FROM @Table
)
SELECT A.c1, B.c1, A.c2 - B.c2
FROM CTE AS A
INNER JOIN CTE AS B
ON A.N + 1 = B.N
-- Gianluca Sartori
October 29, 2009 at 8:58 am
Thanks all replied...
Create table TestDiff (DateCol datetime,daysdiff int)
WITH DaysDIffCTE AS
(SELECT Datecol,daysdiff,ROW_NUMBER() OVER (order by Datecol asc)as RowNumber FROM TestDiff)
select a.datecol 'Fromdate', b.datecol 'Todate',b.daysdiff - a.daysdiff 'DIff'
from DaysDIffCTE a ,DaysDIffCTE b where a.RowNumber=b.RowNumber+1
This solution works...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply