Differenc between two record sets

  •  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

     

  • 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

  • -- 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)

     

  • 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