13

I am trying to get some reporting done for employee time records.

We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of work they've performed and is stored in the Time_Entry table.

Example setup with SQL Fiddle: http://sqlfiddle.com/#!3/e3806/7

The end result I'm going for is a table which shows ALL the Members in a column list and then will show their sum hours for the date queried in the other columns.

The problem seems to be that if there is no row in the Time_Entry table for a particular member, there is now row for that member. I've tried several different join types (Left, Right, Inner, Outer, Full Outer, etc.) but none seem to give me what I want, which would be (based on the last example in SQL Fiddle):

/*** Desired End Result ***/

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
ADavis      | 0               | 11-10-2013    | 0               | 0
BTronton    | 0               | 11-10-2013    | 0               | 0
CJones      | 0               | 11-10-2013    | 0               | 0
DSmith      | 0               | 11-10-2013    | 0               | 0
EGirsch     | 1               | 11-10-2013    | 0.92            | 1
FRowden     | 0               | 11-10-2013    | 0               | 0

What I'm currently getting when I query for specific date of 11-1:

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
EGirsch     | 1               | 11-10-2013    | 0.92            | 1

Which is correct based on the one Time Entry row that is dated 11-10-2013 for EGirsch, but I need to see zeros for the other members in order to get reports and eventually a web dashboard/report for this information.

This is my first question, and while I searched for Join queries, etc. I'm honestly not sure what this function might be called, so I hope that this isn't a duplicate and will help others too trying to find a solution to similar problems.

2 Answers 2

12

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

2
  • Aaron, thanks so much for the feedback. SQL newbie here, and had no idea the difference between WHERE and AND. I had used aliases originally, but sqlfiddle didn't seem to like it so I just went full format. Thanks for the other SQL tips as well. Would you recommend ISNULL or COALESCE to make the data 0 instead of NULL? Thanks again! Nov 12, 2013 at 14:41
  • 1
    @farewelldave I prefer COALESCE because it's standard and doesn't deviate from its functionality in other languages (compare how ISNULL works in SQL Server vs. VB, for example). In almost all cases the performance difference is inconsequential, except one. Lots more details here. Nov 12, 2013 at 15:15
4

When I've been faced with this type of problem in the past, I have created a "numbers" table to help deal with the missing rows.

I created my numbers table specifically to deal with dates as so:

CREATE TABLE Dates
(
    dDate DATETIME NOT NULL CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED
);

INSERT INTO Dates (dDate)
SELECT TOP(73049) DATEADD(d, -1, ROW_NUMBER() OVER (ORDER BY o.object_id)) AS dDate
FROM master.sys.objects o, master.sys.objects o1, master.sys.objects o2

This creates a table with a single row for each and every date between 1900-01-01 and 2099-12-31. I use TOP(73049) to limit the date range generated in my example to thos dates - if you work with a different date range, you could adjust that number.

Next, I add the dDates table to my query so that a row is returned for every date in the desired range for every member_id. The result is then joined to the Time_Entry table as such:

SELECT MD.Member_ID,
    MD.dDate,
    T.Date_Start,
    T.Hours_Actual,
    T.Hours_Bill
FROM 
    (
        SELECT M.Member_ID, D.dDate
        FROM dbo.Dates D, dbo.Members M
        WHERE D.dDate >= '20131110' AND D.dDate < '20131112'
    ) AS MD
    LEFT JOIN dbo.Time_Entry T ON MD.Member_ID = T.Member_ID AND MD.dDate = T.Date_Start
ORDER BY MD.Member_ID, MD.dDate

This allows you to specify a date range for the report.

You can further refine the results by adding COALESCE(...) and SUM(...) as per:

SELECT MD.Member_ID,
    MD.dDate,
    T.Date_Start,
    SUM(COALESCE(T.Hours_Actual, 0)) AS TotalHoursActual,
    SUM(COALESCE(T.Hours_Bill, 0)) AS TotalHoursBill
FROM 
    (
        SELECT M.Member_ID, D.dDate
        FROM dbo.Dates D, dbo.Members M
        WHERE D.dDate >= '20131110' AND D.dDate < '20131112'
    ) AS MD
    LEFT JOIN dbo.Time_Entry T ON MD.Member_ID = T.Member_ID AND MD.dDate = T.Date_Start
GROUP BY MD.Member_ID, MD.dDate, T.Date_Start
ORDER BY MD.Member_ID, MD.dDate

This results in the following output for your sample data:

enter image description here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.