MySQL/SQL Syntax help when comparing military times within a cross-tab query
Experts,
Much like my last post, I am trying to obtain several counts when certain conditions are met. I am attempting to utilize a cross-tab query so that all results appear in one table.
In this example, I'd like to get a total count of each shift type. A shift type or "need" is determined by when the employee starts and stops working. The counts would then be ordered by Site, Department, and employee classification.
I'm not sure if my SUM() counts are off because I am comparing TIMEs incorrectly or, maybe I am grouping incorrectly? Can anyone offer any suggestions?
Both "Job_Start_Time" and the "Job_End_Time" fields are stored as varchar(8) in my database. Both fields store time values in military time: HH:MM:SS.
Much like my last post, I am trying to obtain several counts when certain conditions are met. I am attempting to utilize a cross-tab query so that all results appear in one table.
In this example, I'd like to get a total count of each shift type. A shift type or "need" is determined by when the employee starts and stops working. The counts would then be ordered by Site, Department, and employee classification.
I'm not sure if my SUM() counts are off because I am comparing TIMEs incorrectly or, maybe I am grouping incorrectly? Can anyone offer any suggestions?
Both "Job_Start_Time" and the "Job_End_Time" fields are stored as varchar(8) in my database. Both fields store time values in military time: HH:MM:SS.
SELECT
jobs.Job_Site,
jobs.Job_Department,
employees.User_Field_13,
SUM(CASE WHEN CAST(schedule.Job_Start_Time AS TIME) < CAST('09:59:00' AS TIME) THEN 1 ELSE 0 END) AS 'AM Needs',
SUM(CASE WHEN (CAST(schedule.Job_Start_Time AS TIME) < CAST('14:59:00' AS TIME)) && (CAST(schedule.Job_End_Time AS TIME) > CAST('14:59:00' AS TIME)) THEN 1 ELSE 0 END) AS 'PM Needs',
SUM(CASE WHEN CAST(schedule.Job_Start_Time AS TIME) >= CAST('14:59:00' AS TIME) THEN 1 ELSE 0 END) AS 'Eve Needs'
FROM
mydb.employees
LEFT JOIN mydb.schedule ON employees.Emp_Id=schedule.Emp_Id
LEFT JOIN mydb.jobs ON schedule.JobAssignment_Id=jobs.JobAssignment_Id
LEFT JOIN mydb.status ON status.Emp_Id=employees.Emp_Id
WHERE
schedule.DeleteFlag!='1'
AND schedule.Schedule_for_Date='2019-09-30'
AND jobs.User_Field_3!='1'
AND jobs.Job_Site LIKE 'Jonestown'
AND jobs.Job_Department LIKE 'Shipping'
GROUP BY
jobs.Job_Site,
jobs.Job_Department,
employees.User_Field_13
ORDER BY
jobs.Job_Site,
jobs.Job_Department,
employees.User_Field_13 ASC
First thing I'd ask is how frequently do you need this information?
creating a view, converting.
When doing date/time you have to convert both date and time to handle third shift time transitions.
You have to make sure your sums are mutually exclusive,
I.e. If you count something that started before 10am, your second sum shoukd only count if it started after 10 am but before 3pm.
To avoid the same entry, row counting twice.
Commonly counting events,
There is a way to partition by the variable, I.e. First 8 hours, second, third, etc.
It is difficult for me to untangle your data and what you are looking to do from the query.
I'm no DBA, but sufficient to cause trouble.
First thing I would look to do is avoid all computation, and just straight up attempt to assemble the data on a mall scale I.e. A week worth if data.
Once I eork out the query on a small data set, it can be expanded ....
Point being it is simpler to validate and confirm the assumption.s.
Errors will show up right away.
creating a view, converting.
When doing date/time you have to convert both date and time to handle third shift time transitions.
You have to make sure your sums are mutually exclusive,
I.e. If you count something that started before 10am, your second sum shoukd only count if it started after 10 am but before 3pm.
To avoid the same entry, row counting twice.
Commonly counting events,
There is a way to partition by the variable, I.e. First 8 hours, second, third, etc.
It is difficult for me to untangle your data and what you are looking to do from the query.
I'm no DBA, but sufficient to cause trouble.
First thing I would look to do is avoid all computation, and just straight up attempt to assemble the data on a mall scale I.e. A week worth if data.
Once I eork out the query on a small data set, it can be expanded ....
Point being it is simpler to validate and confirm the assumption.s.
Errors will show up right away.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...
SUM(CASE WHEN (CAST(schedule.Job_Start_T
...