Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: Querying for missing timesheets

    Hello. Part of my timesheet application schema is as follows:
    Code:
    TimesheetDate
    WeekID, StartDate, EndDate
    
    
    TimesheetData
    UserID, WeekID, Activity, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
    
    
    Users
    UserID
    TimesheetDate stores the week periods for which timesheets are recorded in TimesheetData. The latter, obviously records timesheet data i.e. the UserID, the WeekID, Activity and the different hours spent from Monday to Sunday on that particular activity. Thus, it's possible to have multiple rows for a particular user as it depends on the different activities through that week.

    I would like to retrieve the list of missing timesheets, and most probably it'll need to be via Left Join. Any idea how I could achieve it?

    I started with the following in order to retrieve the WeekID for which timesheets are expected:
    Code:
    SELECT WeekID from TimesheetDate WHERE now()>EndDate
    How do I proceed next?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because this sounds like a classroom assignment, I'll give you a hint but not an answer.

    What you have requested sounds like you want a list of users that don't have TimeSheetData in a given period. This is a great candidate for a left join.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    What I've thought of:

    - Do a Left Join between TimesheetDate and TimesheetData
    - Then do a Left Join between the above and User

    Is it right? Or going in the wrong direction?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Both the wrong direction and I'm pretty sure that it is too complicated. Re-read my problem restatement, it gives very pointed clues.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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