Add another condition to Case statement

BobRosas
BobRosas used Ask the Experts™
on
Below is code that runs and kind of works but when someone has a 60 minute lunch it shows on the report with a "TimeOver" of 0.  I'm using Report Services and I can try to code the field in the report so if the result is 0 then don't show it.  But I was wondering if there was an easy way to change my SQL code instead of the report.  If there is an easy fix I can't see it.  I could really use your help.
My code is attached.
---This runs but the report shows 0 as the "TimeOver".  
SELECT 	Supervisor,
	'Lunch' as 'Type',
	EmployeeID, 
	Name, 
	CONVERT(varchar(24), TS_Day,100) as TimeOut, 
	CONVERT(varchar(24), (dateadd(ss,sum(ts_seconds),TS_Day)),100) as TimeIn,
	CASE when  convert(varchar(10), (dateadd(ss,sum(ts_seconds),TS_Day)),101) < '12/08/2011'
		THEN '30+'
	when BreakFlag = 3
		THEN '60'
	when BreakFlag = 2
		THEN '30'
	ELSE '30+' 
	END as 'LunchLength',
	CONVERT(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
		substring((CONVERT(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin,
	CASE when Breakflag = 3  --60 minute lunch
	THEN convert(varchar(5), dateadd(minute, -60, dateadd(second, sum(ts_seconds), 0)), 8)
	ELSE convert(varchar(5), dateadd(minute, -30, dateadd(second, sum(ts_seconds), 0)), 8)
	END AS TimeOver,
	ShiftNote
FROM
(
 SELECT	TS_Day, 
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName AS Name, 
	U.UserName, 
	U.UserName as Supervisor,
        N.ShiftNote,
        times.BreakFlag,
	Sum(TS_Seconds) as ts_Seconds
 FROM
 (
   SELECT company,
	employeeid, 
	recordid,
        convert(datetime,convert(varchar(20),[timeOut],100)) as TS_Day, 
        timein, 
	[timeout], 
	BreakFlag,
        IsNull ( 
      ( SELECT TOP 1 case  
	   WHEN t1.Breakflag =  2 OR t1.BreakFlag = 3 THEN case 
	   WHEN datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) > 1860 THEN 
           datediff(ss,t1.timeout,isnull(t2.timein,t1.timeout)) else 0 end else 0 end
       
        FROM EmployeeHours t2 
	WHERE t2.employeeid = t1.employeeid 
          AND t2.company=t1.company 
          AND convert(varchar(8),t2.timein) = convert(varchar(8),t1.timein)                     -- must be same day
          AND (t2.timein > t1.timein or (t2.timeout > t1.timeout and t2.timein = t1.timein))
        ORDER BY t2.timein asc, t2.timeout asc, recordid asc),0) as ts_seconds
    FROM EmployeeHours t1
  ) times
 
  INNER JOIN EmployeeList L ON (times.EmployeeId = L.EmployeeId) AND (times.Company = L.Company) 
  LEFT JOIN UserList U ON L.ManagerUID = U.UID and L.Company = U.Company
  LEFT JOIN EmployeeShiftNotes N ON L.RecordId = N.RecordId AND L.EmployeeId = N.EmployeeId
 
  WHERE convert(datetime,convert(char(8),times.TimeIn,112)) between @From and @To
    AND L.Suspend = 0
    AND L.Class in (2,4,6,7)
    AND times.Company=1
    AND U.UserName = @Supervisor
 
  GROUP BY times.employeeid,  
	L.EmployeeId, 
	L.LastName + ', ' + L.FirstName, 
	U.UserName, 
        N.ShiftNote,
	TS_Day, 
	times.recordid,
	times.Breakflag
 ) daily
 
GROUP BY name, 
	employeeid,
	Supervisor, 
	Shiftnote,
	TS_Day, 
	ts_seconds,
	BreakFlag
 
HAVING (sum(ts_seconds) / 60) >31

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I tried adding the following code to the last "HAVING" statement.  Is format the reason it doesn't work?  Because the code compiles and runs but 60 Lunch Length still shows.

Author

Commented:
Oops...here is the code.
HAVING ((sum(ts_seconds) / 60) >31) or  ((sum(ts_seconds) / 60) <> 60)  

Author

Commented:
Should be AND  not OR...gets me every time.
HAVING ((sum(ts_seconds) / 60) >31) or  ((sum(ts_seconds) / 60) <> 60)  
Commented:
Should be AND  not OR...gets me every time.
HAVING ((sum(ts_seconds) / 60) >31) AND  ((sum(ts_seconds) / 60) <> 60)  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial