Have Geek Brain

Write a SQL query to replace values ​​and include all dates

Well, I have this -

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

Now the Employee table has dots for day to day if they didn't come ... so Date doesn't have all the Dates records ... I mean, for example, after a week it didn't come for 2 days, the Employee table is only 5 rows .. so I have this dimdate table that has all the dates up to 2050 that I want to join with and adding zeros for dates when it has no points. So I wrote this request but doesn't 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 gives multiple dates and I tried group by date but doesn't work.

+2


a source to share


2 answers


You probably don't want to join two tables in a date range other than date. Then filter the entry 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'
+3


a source


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 should be left external, connected to the table in which the employee records are recorded.

Sort of:

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 for this task).

It also captures employees without points.

Between expression and / or filtering, EmployeeId can be moved to a cross join if it suits your requirements. This would make the cross more effective.

+2


a source






More articles:


All Articles