CODEWITHSUNDEEP
sqlsql-server
Kirill
Kirill

Reputation: 193

Compare values for consecutive dates of same month

I have a table

ID Value Date
1   10   2017-10-02 02:50:04.480
2   20   2017-10-01 07:28:53.593
3   30   2017-09-30 23:59:59.000
4   40   2017-09-30 23:59:59.000
5   50   2017-09-30 02:36:07.520

I compare Value with previous date. But, I don't need compare result between first day in current month and last day in previous month. For this table, I don't need to compare result between 2017-10-01 07:28:53.593 and 2017-09-30 23:59:59.000 How it can be done?

Result table for this example:

ID Value Date                      Diff
1   10   2017-10-02 02:50:04.480   10
2   20   2017-10-01 07:28:53.593   NULL
3   30   2017-09-30 23:59:59.000   10
4   40   2017-09-29 23:59:59.000   10
5   50   2017-09-28 02:36:07.520   NULL

Upvotes: 2

Views: 189

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can use a query like below

select *,
diff=LEAD(Value) OVER( PARTITION BY Month(Date),Year(Date) ORDER BY Date desc)-Value
from t 
order by id asc

see working demo

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this.

SELECT * , 
    LEAD(Value) OVER( PARTITION BY DATEPART(YEAR,[Date]), DATEPART(MONTH,[Date]) ORDER BY ID ) - Value AS Diff 
FROM MyTable
ORDER BY ID

Upvotes: 1

Related Questions