CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Select Previous Date record to be calculated SQL

I have the following table:

ID     myDate       myTime     Value
1      2014-06-01   00:00:00   100
2      2014-06-01   01:00:00   125
3      2014-06-01   02:00:00   132
4      2014-06-01   03:00:00   139
5      2014-06-01   04:00:00   145
6      2014-06-01   05:00:00   148
FF.
24     2014-06-01   23:00:00   205
25     2014-06-02   00:00:00   209
26     2014-06-02   01:00:00   215
27     2014-06-02   02:00:00   223
FF.
48     2014-06-02   23:00:00   280
49     2014-06-03   00:00:00   290
50     2014-06-03   01:00:00   302
FF.
71     2014-06-03   22:00:00   389
72     2014-06-03   23:00:00   400
73     2014-06-04   00:00:00   405
FF.

What I want is the following result:

ID     myDate       ResultValue
1      2014-06-01   109
2      2014-06-02   81
3      2014-06-03   115
FF.

Where Result Value is Value on the nextdate at 00:00:00 time minus value on the current date at 00:00:00 time. For example, I want to know the Result value on '2014-06-01'. So the calculation to find ResultValue is, value on the next date ('2014-06-02') at 00:00:00 is 209 minus value on the current date ('2014-06-01') at 00:00:00 is 100. So the ResultValue is 109

2014-06-02 00:00:00 --> (209) - 2014-06-02 00:00:00 --> (100)
Result: 109

Does anyone know how to do this...?
Thank you.

Upvotes: 0

Views: 60

Answers (2)

sacse
sacse

Reputation: 3744

Try the following:

SELECT
  ROW_NUMBER() OVER (ORDER BY t1.myDate) AS ID,
  t1.myDate,
  t2.Value - t1.Value AS ResultValue
FROM YOUR_TABLE t1
JOIN YOUR_TABLE t2
  ON t2.myDate = DATEADD(D, 1, t1.myDate)
WHERE t1.myTime = '0:00:00'
AND t2.myTime = '0:00:00'

Upvotes: 1

TT.
TT.

Reputation: 16137

Yes you can. Take following example script.

DECLARE @tbl TABLE(dt DATETIME NOT NULL PRIMARY KEY,res INT NOT NULL);
INSERT INTO @tbl(dt,res)
VALUES
    ('2014-06-01T00:00:00',100),
    ('2014-06-01T01:00:00',125),
    ('2014-06-01T02:00:00',132),
    ('2014-06-02T00:00:00',209),
    ('2014-06-03T00:00:00',290),
    ('2014-06-04T00:00:00',405);

SELECT
    t1.dt,
    t2.res-t1.res AS diff
FROM
    @tbl AS t1
    INNER JOIN @tbl AS t2 ON
        t2.dt=t1.dt+1
WHERE
    (t1.dt - CAST(t1.dt AS DATE))=0
ORDER BY
    t1.dt;

You link the data table (t1) to itself (t2) by selecting the next date (t2.dt=t1.dt+1). Selected dates from data table (t1) is restricted to dates that have time part = 00:00:00.000. This is done by casting the date to a DATE type (which has not time part). Subtraction leaves the time part which should equal zero.

Upvotes: 0

Related Questions