Total Sum exclude from condition.

Question

Post reply

Total Sum exclude from condition.

ShawnBryan

SSCrazy

Points: 2157
More actions
October 28, 2016 at 4:05 pm

#322401

experts,
i am trying to calculate Overtime hours . we have got employee who has Friday and Saturday holidays. so mentioned here in table column Friday , saturday value 1.
i need to exclude the dutyhours in OT if that employee friday and saturday is 1.
current sql code is ,
WITH Prep AS
(SELECT EMAIL_address1 as Sub_Department,friday,saturday,preffered_name as Department,government_num as Dutyhours,ci.person_num, ci.full_name, person_id,ci.event_name, CAST(DATEADD(hour,1,dbo.UtcToLocal(ci.creation_date)) AS DATE) AS event_date,DATEname(weekday,dbo.UtcToLocal(co.creation_date)) AS Day,
CAST(dbo.UtcToLocal(ci.creation_date) AS date) AS in_date, CAST(dbo.UtcToLocal(ci.creation_date) AS time) AS in_time,
CAST(dbo.UtcToLocal(co.creation_date) AS date) AS out_date, CAST(dbo.UtcToLocal(co.creation_date) AS time) AS out_time,
DATEDIFF(minute, ci.creation_date, co.creation_date) AS minutes
FROM TEST ci
OUTER APPLY (SELECT TOP 1 creation_date FROM TEST t WHERE t.person_num = ci.person_num AND t.creation_date >= ci.creation_date AND t.event_name = 'CLOCK_OUT' ORDER BY t.creation_date) co
WHERE ci.event_name = 'CLOCK_IN'
AND preffered_name='FINANCE'),
CalcPart AS
(SELECT day,friday,saturday,Dutyhours, person_id,Sub_Department, Department,person_num, full_name, event_date, in_time, out_time, minutes,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep)
SELECT Department,event_date, --full_name,
MAX(CASE WHEN daypart = 1 THEN in_time END) AS CLOCK_IN1,
MAX(CASE WHEN daypart = 1 THEN out_time END) AS CLOCK_OUT1,
MAX(CASE WHEN daypart = 2 THEN in_time END) AS CLOCK_IN2,
MAX(CASE WHEN daypart = 2 THEN out_time END) AS CLOCK_OUT2,
dutyhours,Day,friday,saturday,
SUM(minutes) / 60.0 AS HoursWorked,
sum((dutyhours)-(minutes /60.0)) as OT
FROM CalcPart
GROUP BY Department,Day,friday,saturday,dutyhours,event_date, full_name
sql output,
expected result,
OT should be considered full hours worked if Day column row is (Saturday ,friday) and column friday & saturday row is 1.
example.
below employee OT should be 6.416 since his saturday is matching with column saturday ..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24387 More actions · Answer 1

If I understand your requirements correctly,

you can try changing this

sum((dutyhours)-(minutes /60.0)) as OT

to

case when ( [friday] = 1 and [Day] = 'Friday' )

or ( [saturday] = 1 and [Day] = 'Saturday' )

then sum( [dutyhours] )

else sum( [dutyhours] - ([minutes] /60.0) )

end as OT

How to post data/code on a forum to get the best help.

ShawnBryan SSCrazy Points: 2157 More actions · Answer 2

Hello tes,

Thanks, with given condition i am unable to bring following result . could you guide pls.i am trying to bring group sum.

expected results.

DesNorton SSC-Insane Points: 24387 More actions · Answer 3

philand3 (10/29/2016)
Hello tes,
Thanks, with given condition i am unable to bring following result . could you guide pls.i am trying to bring group sum.
expected results.

OK, so move the entire case statement into the SUM() ...

SUM(case when ( [friday] = 1 and [Day] = 'Friday' )

or ( [saturday] = 1 and [Day] = 'Saturday' )

then [dutyhours]

else [dutyhours] - ([minutes] /60.0)

end) as OT

How to post data/code on a forum to get the best help.