# Total which excludes negative numbers

• ### Question

• User-47788187 posted

experts ,

how to exclude the negative value in this "OT" Calculation.

```WITH Prep AS
(
SELECT
preffered_name as Department ,email_address1 as subdepartment,friday,saturday,(cast(government_num as decimal(10,0))) as dutyhours,ci.person_num,  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 dbo.test ci
OUTER APPLY
(
SELECT TOP 1 creation_date
FROM dbo.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  email_address1='CFO'
)

,CalcPart AS
(
SELECT
department,subdepartment,dutyhours,person_num,  event_date, in_time, out_time, minutes,day,friday,saturday,
ROW_NUMBER() OVER (PARTITION BY event_date, person_num ORDER BY in_time) AS daypart
FROM Prep
)
,tmpResult as
(
SELECT
subdepartment,--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,
MAX(CASE WHEN daypart = 3 THEN in_time END) AS CLOCK_IN3,
MAX(CASE WHEN daypart = 3 THEN out_time END) AS CLOCK_OUT3,
SUM(minutes) / 60.0 AS HoursWorked,

dutyhours,Day,friday,saturday,event_date,
--SUM(minutes) / 60.0 AS HoursWorked,-----This column is duplicate and could be removed

case
when ( [friday] = 1 and [day]  = 'Friday' ) or ( [saturday] = 1 and [day] = 'Saturday' ) then
SUM(minutes) / 60.0
else

sum(( [minutes] /60.0)) - [Dutyhours]
end as OT	```

Wednesday, November 2, 2016 9:39 PM

### All replies

• User-1716253493 posted

i think you can do SUM(CASE WHEN expression <0 THEN 0 ELSE expression END)

Thursday, November 3, 2016 12:07 AM