Can someone assist me with writing a select sql that groups everything in one line. We have to Sum (Hours) and Sum ( PTO or Hours outside of PTO). If the final output, we have to execute a formula that is If (hours > 80) then [ (hours - (hours - 80)) - Previous Reg Hours - PTO].
Raw Table Below:
This is a two week pay period from the 7-21-2015 to 8-4-2015, Previous week is from the 7-21-2015 to 7-30-2015.
ID EmpNum Hours PayType StartDate EndDate
1 2223-ZZ 8.00 SICK 2015-7-21 2015-7-30
1 2223-ZZ 55.25 REG 2015-7-21 2015-7-30
2 2223-ZZ 5.00 VAC 2015-7-21 2015-8-4
2 2223-ZZ 44.75 REG 2015-7-21 2015-8-4
Temp Output Table 1:
Now Execute Formula from Temp Output Table 1 to Produce Temp Output Table 2:
If (hours > 80) then [ (hours - (hours - 80)) - Previous Reg Hours - PTO].
[(100 - (100 - 80)) - 55.25 - 13] = 11.75
EmpNum Hours PTO Hours StartDate EndDate
2223-ZZ 100.00 13 2015-7-21 2015-8-4
Final Output Table 2:
EmpNum Hours StartDate EndDate
2223-ZZ 11.75 2015-7-21 2015-8-4