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