Problem with a Case statement

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Problem with a Case statement

Post by Tom Zaccaria » Fri Aug 08, 2014 4:13 am

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
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Problem with a Case statement

Post by tgriswold » Mon Aug 11, 2014 2:00 pm

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.
Travis Griswold
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Problem with a Case statement

Post by Tom Zaccaria » Mon Aug 11, 2014 5:09 pm

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
Posts: 293
Joined: Mon Apr 22, 2013 8:49 am
Location: Dallas, TX
Contact:

Re: Problem with a Case statement

Post by KevinRossen » Mon Aug 11, 2014 10:39 pm

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';
Kevin Rossen
Office Manager, Rossen Dental
Founder, DivergentDental.com
Image

Post Reply