I have the following tables/columns:

Table Dates:

Columns: Date

contains all dates in a range say from 1.7.15 to 31.7.15

Table EmployeeHours

Columns: EmployeeID, Entrydate, NumberOfHours

in this table an employee can have entries for all dates or just some dates.

I need a query which  shows all the dates from Table Dates for each Employee in Table EmployeeHours regardless if there are entries in EmployeeHours for all dates.

Example:

Dates contains 01.07.15, 02.07.15, 03.07.15

Employeehours contains these records:

Employeeid 1 entrydate 01.07.15, NumberOfHours 5

Employeeid 1 entrydate 03.07.15 NumberOfHours 7

Output should be:

Date       EmployeeID NumberOfHours

01.07.15 1                 5

02.07.15  1                NULL

03.07.15  1               7

I just cannot figure out how to achieve this. Any help appreciated.

Thomas

if those really your column name - you need to rework on those names

select a.[date],b.employeeid,b.numberofhours from dates a left outer join employeehours b on a.[date]=B.[date]

 ;with mycte as (
 Select EmployeeID, [Date] From Dates d,  (Select Distinct EmployeeID From EmployeeHours) e
 )

 Select  m.EmployeeID, m.Entrydate, e.NumberOfHours 
 from mycte m Left Join EmployeeHours e on e.EmployeeID=m.EmployeeID

• Marked as answer by tosch99 42 minutes ago

Hi toshch99,

To get the expected output, you may reference the below sample.

DECLARE @Employeehours TABLE(Employeeid INT,entrydate DATE,NumberOfHours INT)
INSERT INTO @Employeehours 
VALUES
(1,'20150701',5),(1,'20150703',7),
(2,'20150702',6),(2,'20150705',8);
 
SELECT * FROM @Employeehours

;WITH Cte AS
(
SELECT MIN(entrydate) startDT,MAX(entrydate) EndDate,Employeeid FROM @Employeehours
GROUP BY Employeeid
),
DT AS
(
SELECT CAST('20150101' AS DATE) DT
UNION ALL
SELECT DATEADD(DAY,1,DT) FROM DT
WHERE DT<'20160101'
)
SELECT c.Employeeid,d.DT,e.NumberOfHours FROM CTE c LEFT JOIN DT d
						ON d.DT BETWEEN c.startDT AND c.EndDate
					LEFT JOIN @Employeehours e
						ON d.DT = e.entrydate AND c.Employeeid=e.Employeeid
ORDER BY 1,2
OPTION(MAXRECURSION 0)

If you have any feedback on our support, you can click here.

Thanks for your help everyone, finally figured it out with your samples.

Thomas

Thanks for your help everyone, finally figured it out with your samples.

Thomas

The long term solution is to create a maintain a calendar table and use it for queries like these. You can create it using logic like below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html