Add another condition to Case statement

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

BobRosasAsked:
Who is Participating?
 
BobRosasAuthor Commented:
Should be AND  not OR...gets me every time.
HAVING ((sum(ts_seconds) / 60) >31) AND  ((sum(ts_seconds) / 60) <> 60)  
0
 
BobRosasAuthor 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.
0
 
BobRosasAuthor Commented:
Oops...here is the code.
HAVING ((sum(ts_seconds) / 60) >31) or  ((sum(ts_seconds) / 60) <> 60)  
0
 
BobRosasAuthor Commented:
Should be AND  not OR...gets me every time.
HAVING ((sum(ts_seconds) / 60) >31) or  ((sum(ts_seconds) / 60) <> 60)  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.