Write a SQL Query to replace values and include all the Dates.

Question

Well I have this -

Table DimDate- Date 
Table Employee-  Id,Name,Points,Date

Now the Employee table has points for everyday unless they did not come...so the Date does not have all the Dates entries... I mean for e.g in a week he did not come for 2 days the Employee table has only 5 rows...so I have this dimdate table which has all the dates till 2050 which I want to join with and add Zeros for the dates he does not have points. So I have written this query but does not work -

Select E.EmployeeId,D.Date,isNull(E.Points,0) from DimDate D left join Employee E on D.Date between '01-01-2009'and '06-01-2009' where E.EmployeeId=1

The above query give multiple dates and I tried group by on Date but does not work.

Answer 1

I think you need a cross join between the dimdates table and the table where your employees are defined. This will give you a list of records with all employee/date combinations. Then the result of that needs to be left outer joined to the table that has the employee points records.

Something like:

Select CJ.EmployeeId,CJ.Date,isNull(E.Points,0) 
    from (SELECT EmployeeID, D.Date
          from DimDate D CROSS JOIN [EmployeeDefinitionTable] as edt) as CJ
        left outer join Employee E on CJ.Date =E.Date AND CJ.EmployeeId = E.EmployeeId
where CJ.Date between '01-01-2009'and '06-01-2009'
  and E.EmployeeId = 1

Where EmployeeDefinitionTable is a table that uniquely lists all employees (or at least their id's for this problem statement).

This also captures employees with no points entries.

The between statement and/or EmployeeId filtering could be moved up into the cross join if it fits your requirements. It would make the cross join more efficient.

Answer 2

You probably dont want to join the two tables on a date range but a date. Then filter the record set by the date range. example

Select 
  E.EmployeeId,
  D.Date,
  isNull(E.Points,0)  
from DimDate D 
left join Employee E on D.Date = E.Date 
where E.EmployeeId=1 
  AND D.Date Between '01-01-2009'and '06-01-2009'

Edited:

Select 
  E.EmployeeId,
  D.Date,
  isNull(E.Points,0)  
from DimDate D 
left join Employee E on D.Date = E.Date And E.EmployeeId=1
where D.Date Between '01-01-2009'and '06-01-2009'

OR

Select 
  E.EmployeeId,
  D.Date,
  isNull(E.Points,0)  
from DimDate D 
left join Employee E on D.Date = E.Date 
where (E.EmployeeId = 1 OR E.EmployeeId is NULL) 
  AND D.Date Between '01-01-2009'and '06-01-2009'