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.
Best Answer
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 theWHERE
clause, you turn an outer join to an inner join, because it will exclude any rows that areNULL
on that side (because it doesn't know ifNULL
would match the filter or not).I modified the first query to get a row for every member:
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:
please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like
mm-dd-yyyy
consider using aliases to make your queries easier to read. E.g. the above could be re-written as:
... a lot tidier, IMHO, as long as you use sensible aliases.