locked
Total which excludes negative numbers RRS feed

  • 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