Problem with a Case statement

[Tom Zaccaria]

I am trying to run a query of employee hours any day of the week and get the same results.
The query works but the Case statement does not. Anyone have any ideas as to why this wont work?
Thanks
drtmz
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*Time Card Summary*/
CASE daterun
WHEN DAYOFWEEK(Curdate()) = 1 /*Sunday*/
SET @FromDate=(Now()- interval 14 day) , @ToDate=(Now()- interval 1 day);
WHEN DAYOFWEEK(Curdate()) = 2 /*Monday*/
SET @FromDate=(Now()- interval 15 day) , @ToDate=(Now()- interval 1 day);
WHEN DAYOFWEEK(Curdate()) = 3 /*Tuesday*/
SET @FromDate=(Now()- interval 16 day) , @ToDate=(Now()- interval 2 day);
WHEN DAYOFWEEK(Curdate()) = 4 /*Wednesday*/
SET @FromDate=(Now()- interval 17 day) , @ToDate=(Now()- interval 3 day);
WHEN DAYOFWEEK(Curdate()) = 5 /*Thursday*/
SET @FromDate=(Now()- interval 18 day) , @ToDate=(Now()- interval 4 day);
WHEN DAYOFWEEK(Curdate()) = 6 /*Friday*/
SET @FromDate=(Now()- interval 19 day) , @ToDate=(Now()- interval 5 day);
ELSE
SET @FromDate=(Now()- interval 20 day) , @ToDate=(Now()- interval 6 day);
ENDCASE;

/*This section works great*/
SELECT e.FName,e.LName,
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(TimeDisplayed2, TimeDisplayed1),0)))/3600,2) AS RawHours,
FORMAT(
(IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
IFNULL((SELECT SUM(TIME_TO_SEC(ta.OTimeHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj OT hours*/ +
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0))))/3600
,2) AS AdHours,
FORMAT((IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum =e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
SUM(TIME_TO_SEC(IF((TIME_TO_SEC(c.TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(c.TimeDisplayed2, c.TimeDisplayed1),0)))+
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0)))- /*minus (+) adjustments*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) AND c.OTimeHours<>'-01:00:00', c.OTimeHours,0)))- /*minus Overtime*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) , c.OTimeAuto,0))))/3600,2) AS RegHours /*minus Overtime Auto*/
FROM employee e
INNER JOIN clockEvent c ON c.EmployeeNum=e.EmployeeNum
WHERE (DATE(c.TimeDisplayed1) BETWEEN @FromDate AND @ToDate)
AND LName <> " "
GROUP BY c.EmployeeNum;

[tgriswold]

I am not quite sure of the exact thing you are trying to do, but this looks like it should work for what you are trying to do, or can be slightly modified to get the day numbers you are looking for.

Code: Select all

SET @FromDate=(CURDATE() - INTERVAL 14 DAY) - INTERVAL (DAYOFWEEK(CURDATE())-1) DAY;
SET @ToDate=(CURDATE()) - INTERVAL (DAYOFWEEK(CURDATE())-1) DAY;

The issue with your case statement is that you cannot use a semicolon in the middle of a query (case statement). I also don't think you can use the SET command inside of a case statement, you'd have to use :=, but i'm not 100% sure.

[Tom Zaccaria]

What I am trying to do is run the query on any day of this current week and get the hours totals for the previous two full weeks.
For example if I run the query on Tuesday, August 12, 2014 I want the hours from July 28 to August 8.
If I run the query on Wednesday, August 13, 2014 I want the same days included.
Always the previous two full weeks for payroll.

This way if I forget to run the query I can just run it on another day and get the same result.
Thanks for you input,

drtmz

[KevinRossen]

This should do it. The case statement you're trying might eventually work, but you don't need it. I set the between dates as the Sunday from two weeks ago through the previous Saturday using a convoluted WHERE date range.

Code: Select all

SELECT e.FName,e.LName, 
FORMAT(SUM(TIME_TO_SEC(IF((TIME_TO_SEC(TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(TimeDisplayed2, TimeDisplayed1),0)))/3600,2) AS RawHours,
FORMAT(
(IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
IFNULL((SELECT SUM(TIME_TO_SEC(ta.OTimeHours)) FROM timeadjust ta WHERE ta.EmployeeNum=e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj OT hours*/ +
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0))))/3600
,2) AS AdHours,
FORMAT((IFNULL((SELECT SUM(TIME_TO_SEC(ta.RegHours)) FROM timeadjust ta WHERE ta.EmployeeNum =e.EmployeeNum AND DATE(ta.TimeEntry) BETWEEN @FromDate AND @ToDate),0) /*adj reg hours*/ +
SUM(TIME_TO_SEC(IF((TIME_TO_SEC(c.TimeDisplayed2)>0 AND c.ClockStatus IN(0,1)), TIMEDIFF(c.TimeDisplayed2, c.TimeDisplayed1),0)))+
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1),IF(c.AdjustIsOverRidden, c.Adjust, c.AdjustAuto),0)))- /*minus (+) adjustments*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) AND c.OTimeHours<>'-01:00:00', c.OTimeHours,0)))- /*minus Overtime*/
SUM(TIME_TO_SEC(IF(c.ClockStatus IN(0,1) , c.OTimeAuto,0))))/3600,2) AS RegHours /*minus Overtime Auto*/
FROM employee e
INNER JOIN clockEvent c ON c.EmployeeNum=e.EmployeeNum
WHERE (DATE(c.TimeDisplayed1) BETWEEN SUBDATE(ADDDATE(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY), INTERVAL 14 DAY) AND SUBDATE(ADDDATE(CURDATE(), INTERVAL 7-DAYOFWEEK(CURDATE()) DAY), INTERVAL 7 DAY))
AND LName <> " "
GROUP BY c.EmployeeNum;

You can see the date range it's limiting to using this query:

Code: Select all

SELECT SUBDATE(ADDDATE(CURDATE(), INTERVAL 1-DAYOFWEEK(CURDATE()) DAY), INTERVAL 14 DAY) AS 'From', SUBDATE(ADDDATE(CURDATE(), INTERVAL 7-DAYOFWEEK(CURDATE()) DAY), INTERVAL 7 DAY) AS 'To';