I have the following table basically hour reading of the equipment based on the shifts. It contained over 1500 rows. What I want to do is subtract the next shifts reading from the previous one so that I can find the hours worked for that equipment.

Id      Shift   Eqpmt   HourReading
--      -----   -----   ------------
1       Shift1  E21     2488
2       Shift1  E52     36882
3       Shift1  Q53     2384
4       Shift1  S54     44874
.         .      .        .
.         .      .        .
11      Shift2  E21     2500
12      Shift2  E52     36900
13      Shift2  Q53     2388
14      Shift2  S54     44875
.         .      .        .
.         .      .        .
.         .      .        .
         distinct sh.Shift
        ,(a.HourReading-sh.HourReading) WorkedHrs 
from sh

join (select id,Shift, Eqpmt, HourReading from sh) a on 
a.Eqpmt=sh.Eqpmt and a.id>sh.id

I tried the above script but it subtracts every shift's value and giving me over 30000 records. Actually, I added myself that id column to do that operation but it seems it's not working still.

and this is what I want to get actually

Shift   Eqpmt   WorkedHours
------  -----   ------------
Shift1  E21     12
Shift1  E52     8
Shift1  Q53     4
Shift1  S54     1

1 Answers

EdmCoff On Best Solutions

I think your attempted query would work for this sample data, but I take it you have more than 2 shifts in the actual data (e.g. "Shift3", "Shift4", etc). Therefore I think you might want to look at using lead.

Something like:

SELECT shift, eqpmt, lead(hourreading) OVER (PARTITION BY eqpmt ORDER BY id) - hourreading as workedhours
) a
WHERE workedhours is not null;

The lead(hourreading) OVER (PARTITION BY eqpmt ORDER BY id) bit will get the next row (ordered by id) that has the same eqpmt value. I wrapped it in the outer query checking for workedhours is not null so that the last shift (that isn't finished yet) doesn't show up in the result.