CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Haminteu
Haminteu

Reputation: 1334

Select value on next date to be calculated on current date SQL

I have the following table:

ID   GroupID    oDate       oTime     oValue
1    A          2014-06-01  00:00:00  100
2    A          2014-06-01  01:00:00  200
3    A          2014-06-01  02:00:00  300
4    A          2014-06-02  00:00:00  400
5    A          2014-06-02  01:00:00  425
6    A          2014-06-02  02:00:00  475
7    B          2014-06-01  00:00:00  1000
8    B          2014-06-01  01:00:00  1500
9    B          2014-06-01  02:00:00  2000
10   B          2014-06-02  00:00:00  3000
11   B          2014-06-02  01:00:00  3100
12   A          2014-06-03  00:00:00  525
13   A          2014-06-03  01:00:00  600
14   A          2014-06-03  02:00:00  625

I want to have the following result:

GroupID   oDate         oResult
A         2014-06-01    300
A         2014-06-02    125
B         2014-06-01    2000

oResult is coming from:

Value on next date at 00:00:00 subtract value on selected date at 00:00:00. For example, I want to know the Result for 2014-06-01. Then,

2014-06-02 00:00:00 400 substract 2014-06-01 00:00:00 100
oResult = 400 - 100 = 300

How can I achieve this in SQL syntax? Thank you.

Upvotes: 1

Views: 73

Answers (2)

Deepshikha
Deepshikha

Reputation: 10264

You can write a query using Common Table Expression as :

;with CTE as
( select row_number() over ( partition by GroupID, oDate order by oTime Asc) as rownum, 
         GroupID, oDate, oValue,oTime
  from Test
)
select CTE.GroupID,CTE1.oDate, (CTE.oValue - CTE1.oValue) as oResult
from CTE
inner join CTE as CTE1 on datediff (day,CTE1.oDate, CTE.oDate) = 1  
and CTE1.rownum= CTE.rownum 
and  CTE1.GroupID= CTE.GroupID
where CTE.rownum = 1

Check Demo here ...

Upvotes: 1

AK47
AK47

Reputation: 3797

You can use cross apply operator here Please check this,

select a.GroupID,a.oDate, (ab.oValue - a.oValue) oResult from T as a
cross apply 
(
    select top 1 * from T  as b
    where a.oDate < b.oDate
    and oTime = '00:00:00.0000000'
    and a.ID < b.ID

)as ab
where a.ID in(1,4,7)

Demo

Upvotes: 0

Related Questions