Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Placeholder rows

    Hi,

    I'm creating a stored procedure that reports on the number of times employees clock in and out of work per day (breaks and lunches are part of this). The final result for a particular employee group with the way the query is currently set up would look something like this:

    Group--------Employee---ClockedIn-----ClockedOut----Date
    Deli----------Smith-------3-------------3-------------2012-07-28
    Deli----------Smith-------3-------------3-------------2012-07-30
    Bakery-------Brown-------3-------------3-------------2012-07-28
    Bakery-------Brown-------2-------------3-------------2012-07-29

    There will be days when an employee doesn't clock in or clock out (because the employee is not working that day). When I group by the group, employee and date, I still need the recordset to show a record for such days. So the recordset would look like this:

    Group--------Employee---ClockedIn-----ClockedOut----Date
    Deli----------Smith-------3-------------3-------------2012-07-28
    Deli----------Smith-------0-------------0-------------2012-07-29
    Deli----------Smith-------3-------------3-------------2012-07-30
    Bakery-------Brown-------3-------------3-------------2012-07-28
    Bakery-------Brown-------2-------------3-------------2012-07-29
    Deli----------Brown-------0-------------0-------------2012-07-30

    I've been using a subquery with unions bringing together a query that gets the employees, a query that gets the clockedIn, and a query that gets the ClockedOut. Then the outer query groups this based on the group, employee and date. That produces the first data example I showed you.

    My plan was to write another query to union to the others inside the subquery that would just grab each date within my parameter date range regardless of whether or not any data exists for that date. Any ideas on how I might achieve this?

    Thank you,

    Hammerklavier

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like a perfect candidate for a calendar table. (example of how to create and populate one here: http://www.dbforums.com/microsoft-sq...day-frida.html)

    So you would take your calendar table and outer join your employee data to get a record for every date.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2011
    Posts
    11
    Gvee,

    I was hoping there might be a simpler solution!

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Having a good calendar table is always a good idea. Like having a Numbers table. In the case of clocking in/out - does the process distinguish between IN and OUT?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •