Total Sum exclude from condition.
-
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 ..
-
If I understand your requirements correctly,
you can try changing this
sum((dutyhours)-(minutes /60.0)) as OTto
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
-
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.
-
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply