November 16, 2006 at 3:54 am
Dear all
I would like to calculate the difference of a field A between two dates. For example:
Table:
Date Client A
------------------------
2006-11-01 1 100
2006-11-02 1 110
2006-11-03 1 140
The result should be:
Date Client Diff
------------------------
2006-11-02 1 10
2006-11-03 1 30
Your help is very much appreciated!!
Regards, Urs
November 16, 2006 at 4:32 am
Declare @Table Table(Date datetime,client int, A int)
Insert Into @Table Values ('2006-11-01', 1, 100)
Insert Into @Table Values ('2006-11-02', 1, 110)
Insert Into @Table Values ('2006-11-03', 1, 140)
Select *
From
(
Select Date,
client,
T.A-(Select Top 1 A from @Table T2 where T2.client=T.client And T2.Date<T.Date Order by T2.Date Desc) As Diff
from @Table T
) as X
Where X.Diff is not null
November 16, 2006 at 4:37 am
-- Simple; assuming no time component in date and consecutive days
DECLARE @t TABLE
(
tDate datetime NOT NULL
,Client int NOT NULL
,A int NOT NULL
)
INSERT INTO @t
SELECT '20061101', 1, 100 UNION ALL
SELECT '20061102', 1, 110 UNION ALL
SELECT '20061103', 1, 140
SELECT T2.tDate AS FromDate
,T1.tDate AS ToDate
,T1.Client
,T1.A - T2.A AS Diff
FROM @t T1
JOIN @t T2 ON T1.Client = T2.Client AND T1.tDate = T2.tDate + 1
-- with time component
DECLARE @t TABLE
(
tDate datetime NOT NULL
,Client int NOT NULL
,A int NOT NULL
)
INSERT INTO @t
SELECT '20061101 08:23', 1, 100 UNION ALL
SELECT '20061102 09:24', 1, 110 UNION ALL
SELECT '20061105 10:25', 1, 140
SELECT T2.tDate AS FromDate
,T1.tDate AS ToDate
,T1.Client
,T1.A - T2.A AS Diff
FROM @t T1
JOIN @t T2 ON T1.Client = T2.Client
WHERE T2.tDate = (
SELECT MAX(T3.tDate)
FROM @t T3
WHERE T3.tDate < T1.tDate)
November 16, 2006 at 6:09 am
Thank you very much to both of you !! Best regards, Urs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply