Having a stupid moment and maybe you can help. I’m trying to count how many times an employee is scheduled using MySQL. I would like to have a column that for George says "3" and for Lucy says "2" but if I group on EmployeeID and use count(e.EmployeeID) I end up with 2 rows instead of 5. How do I retain the 5 rows of data and still get a count of 3 for George and 2 for Lucy? My output will show that George has schedule Sat-Mon, Tues-Fri, and Wed-Sun and will say "George is scheduled 3 times."
CompanyID AffiliateID EmployeeID EmployeeName Schedule
1 23 11 George Sat-Mon
1 23 11 George Tues-Fri
1 23 11 George Wed-Sun
1 23 15 Lucy Sun-Mon
1 23 15 Lucy Thur-FriSELECT c.CompanyID,
a.AffiliateID,
e.EmployeeID,
e.EmployeeName,
s.Schedule
FROM companies c
LEFT OUTER JOIN subscription s
ON c.CompanyID = s.CopmanyID
LEFT OUTER JOIN affiliates a
ON a.AffiliateID = s.AffiliateID
LEFT OUTER JOIN employees e
ON ae.AffiliateID = a.AffiliateID
LEFT OUTER JOIN schedules s
ON s.EmployeeID = e.EmployeeID
AND s.CompanyID = c.CompanyID
AND s.AffiliateID = a.AffiliateID
WHERE (s.StartDate <= FROM_UNIXTIME(1227243599)
AND s.EndDate >= FROM_UNIXTIME(1227157200))
AND c.CompanyID = 1
ORDER BY c.CompanyName, s.AffiliateID ASC
I know this is not a complex SQL problem. I’ve done this in the past but today I am being dense and am stumped.
Maybe a subselect to get the count? Not very efficient, I guess.
I had the same thought but I suppose at this point efficiency could go out the window.
Another option would be a cron job that updates some table (workweek: EmployeeID, start_time, end_time, days) periodically with the work schedule count, making for more efficient queries on the front end. I’ve had to do all kinds of ugly little tricks like this for some data warehousing-type stuff I’ve done over the last few years. It’s not really optimal for a real-time web app, but desperate situations call for desperate measures, etc. 😉
I think I’ve worked around this with an IF() statement in the select and using some PHP logic for presentation. I’ll probably come back to this with a “duh” moment and refine the code later.
cron jobs and housekeeping routines. Been there before.