jump to navigation

NOTE: The spam filter is being unusually aggressive. If you comment does not immediately appear, it has simply been placed in moderation and I will approve it as quickly as possible. Thank you for your patience.

"Murphy was an optimist!"

Today’s Technical Brain Cloud January 2, 2009 11:39 am

Posted by Doug McCaughan in : MySQL, Programming, Technology
, trackback

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-Fri

SELECT 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.

Comments after advertisement

Comments»

1. Daryl - January 5, 2009

Maybe a subselect to get the count? Not very efficient, I guess.

2. Doug McCaughan - January 5, 2009

I had the same thought but I suppose at this point efficiency could go out the window.

3. Daryl - January 5, 2009

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. 😉

4. Doug McCaughan - January 5, 2009

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.


trackback