changing from inner joins to left joins to include null values? I need to have this query return all NULL Values for hours for each jobcode. I have tried to change the inner joins to left joins, but the output is the same (only showing the 1 jobcode for one date in the range). Do I need to change the cross join to an outer join as well?

SELECT EmployeeNumber,
   JobCode,
   [Date],
   SUM(Hours) as [Hours]
FROM
(
SELECT        tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]

FROM          AdjustUserRoleRequestStatusLookup 
CROSS JOIN    tServiceJobCodes 
INNER JOIN    tServiceHours 
ON            tServiceJobCodes.ServiceJobCodes = tServiceHours.JobCode 
INNER JOIN    tServiceReports 
ON            tServiceHours.ReportNo = tServiceReports.ReportNo 
INNER JOIN    tServiceReps  
ON            tservicereports.employee = tservicereps.repid 
INNER JOIN    tRegions 
ON            tServiceReports.Region = tRegions.RegionCode

WHERE         Date between '2017-01-01' and '2017-01-05'

GROUP BY      tServiceReps.EmployeeNumber, 
              tServiceHours.JobCode, 
              tServiceReports.[Date], 
              tServiceHours.[Hours]
) ctHours
GROUP BY EmployeeNumber, JobCode, [Date];


Output for inner select query


EmployeeNumber  JobCode      Date               Hours
10203             82    2017-01-03 00:00:00.000 3.00
10203             82    2017-01-04 00:00:00.000 3.50
10203             82    2017-01-05 00:00:00.000 3.00
10203             86    2017-01-02 00:00:00.000 8.00
10203            210    2017-01-03 00:00:00.000 5.00
10203            210    2017-01-05 00:00:00.000 5.00
10203            215    2017-01-04 00:00:00.000 4.50


@DominiqueBoucher  i understand your confusion. this was built by non-dba admins. servicehours and servicereports both contain the ReportNo column. servicehours also contains the jobcode column.

The query works where there is data to report against, the issue is, I need to see Null where no data exist. 

an example of this, would be if I pull from 2018/01/05, no records should exist, but I need to see:

 employee#     jobcode      date             Hours
 12345                1              01/05/2018        0
 12345                2              01/05/2018        0
As I don't have the table definition and values Inside them, here's the code I used to do something "similar".

You can use it as inspiration... You may be able to adapt it to your situation.

 /*------------------------------------------------------------------ */
--create table tservicehours ([hours] float,jobcode int, reportno int);
--create table tServiceJobCodes (Servicejobcode int);
--create table tServiceReports (ReportNo int, [date] date, employee int);

--insert into tServiceReports values (1, '2017-01-03',10204), (2, '2017-01-04',10203), (3, '2017-01-05',10203), (4, '2017-01-02',10203), (5, '2017-01-03',10203), (6, '2017-01-05',10203), (7, '2017-01-04',10203);
--insert into tServiceJobCodes values (82),(86),(210),(215);
--insert into tservicehours values (3,82,1),(3.5,82,2),(3,82,3),(8,86,4),(5,210,5),(5,210,6),(4.5,215,7);

create table #date ([date] date);
insert into #date values ('2017-01-01'),('2017-01-02'),('2017-01-03'),('2017-01-04'),('2017-01-05');

select distinct #date.[date],tServiceJobCodes.Servicejobcode, tServiceReports.employee  into #temp from #date cross apply tServiceJobCodes cross apply tServiceReports;


with datas as (
select  date, [hours],  jobcode, employee
from tServiceReports r
join tservicehours h on h.reportno = r.reportno) 

select distinct #temp.employee, #temp.[date], #temp.servicejobcode, datas.[hours] from #temp
left join datas on jobcode = #temp.Servicejobcode and datas.[date] = #temp.[date] and #temp.employee=datas.employee
where #temp.[date] between '2017-01-01' and '2017-01-05'
order by employee, servicejobcode;

drop table #date;
drop table #temp;
/* ----------------------------------------------------------------- */



The general idea here is to create a temp table containing each day for every jobcode/employee and then use that table to join on the actual data.


I hope it will help.