changing from inner joins to left joins to include null values?

Question

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

Answer 1

Is there a way to make this top part loop 13 times after the initial run? only included the first 3 to save space.

DECLARE              @BegDate AS DATE, @EndDate as Date
SET                  @BegDate = '2017-11-19'
SET                  @EndDate = DATEADD(day, 13, @BegDate)

IF OBJECT_ID('tempdb..#TEMPLIST') IS NOT NULL DROP TABLE #TEMPLIST 

SELECT *
INTO #TEMPLIST
FROM
   (      SELECT               tRegions.Region, COALESCE(tSubregions.Description,'') as SubRegion, tServiceReps.[Service Rep] as Service_Rep, tServiceReps.EmployeeNumber, 
                                     DATEADD(day, 0, @BegDate) as Date, DATENAME(dw,@BegDate) as DOW 
          FROM                 UnitDatabase.dbo.tServiceReps as tServiceReps
          LEFT OUTER JOIN      UnitDatabase.dbo.tSubregions as tSubregions on tServiceReps.Subregion = tSubregions.ID
          LEFT OUTER JOIN      UnitDatabase.dbo.tRegions as tRegions on tServiceReps.RegionCode = tRegions.RegionAcctCode and tRegions.Deactivated = 0
          Where                tServiceReps.RepID IN
                              (      SELECT        SR.RepID
                                     FROM          tServiceReps SR
                                     LEFT JOIN     tServiceReports SREP ON SR.RepID = SREP.Employee AND SREP.Date between @BegDate and @EndDate
                                     LEFT JOIN     tServiceHours AS SREPHRS ON SREP.ReportNo = SREPHRS.ReportNo
                                     WHERE         (SR.Deactivated = 0 AND SR.AllowUserToCreateServiceReport = 1)
                                            OR            (COALESCE(SREPHRS.Hours,0) + COALESCE(SREPHRS.OTHours,0)) > 0
                                     GROUP BY      SR.RepID      )
          ) AS TMP

INSERT 
INTO #TEMPLIST (Region, Subregion, Service_Rep, EmployeeNumber, Date, DOW)
   (      SELECT               tRegions.Region, COALESCE(tSubregions.Description,'') as SubRegion, tServiceReps.[Service Rep] as Service_Rep, tServiceReps.EmployeeNumber, 
                                     DATEADD(day, 1, @BegDate) as Date, DATENAME(dw,DATEADD(day, 1, @BegDate)) as DOW 
          FROM                 UnitDatabase.dbo.tServiceReps as tServiceReps
          LEFT OUTER JOIN      UnitDatabase.dbo.tSubregions as tSubregions on tServiceReps.Subregion = tSubregions.ID
          LEFT OUTER JOIN      UnitDatabase.dbo.tRegions as tRegions on tServiceReps.RegionCode = tRegions.RegionAcctCode and tRegions.Deactivated = 0
          Where                tServiceReps.RepID IN
                              (      SELECT        SR.RepID
                                     FROM          tServiceReps SR
                                     LEFT JOIN     tServiceReports SREP ON SR.RepID = SREP.Employee AND SREP.Date between @BegDate and @EndDate
                                     LEFT JOIN     tServiceHours AS SREPHRS ON SREP.ReportNo = SREPHRS.ReportNo
                                     WHERE         (SR.Deactivated = 0 AND SR.AllowUserToCreateServiceReport = 1)
                                            OR            (COALESCE(SREPHRS.Hours,0) + COALESCE(SREPHRS.OTHours,0)) > 0
                                     GROUP BY      SR.RepID      ))

INSERT 
INTO #TEMPLIST (Region, Subregion, Service_Rep, EmployeeNumber, Date, DOW)
   (      SELECT               tRegions.Region, COALESCE(tSubregions.Description,'') as SubRegion, tServiceReps.[Service Rep] as Service_Rep, tServiceReps.EmployeeNumber, 
                                     DATEADD(day, 2, @BegDate) as Date, DATENAME(dw,DATEADD(day, 2, @BegDate)) as DOW 
          FROM                 UnitDatabase.dbo.tServiceReps as tServiceReps
          LEFT OUTER JOIN      UnitDatabase.dbo.tSubregions as tSubregions on tServiceReps.Subregion = tSubregions.ID
          LEFT OUTER JOIN      UnitDatabase.dbo.tRegions as tRegions on tServiceReps.RegionCode = tRegions.RegionAcctCode and tRegions.Deactivated = 0
          Where                tServiceReps.RepID IN
                              (      SELECT        SR.RepID
                                     FROM          tServiceReps SR
                                     LEFT JOIN     tServiceReports SREP ON SR.RepID = SREP.Employee AND SREP.Date between @BegDate and @EndDate
                                     LEFT JOIN     tServiceHours AS SREPHRS ON SREP.ReportNo = SREPHRS.ReportNo
                                     WHERE         (SR.Deactivated = 0 AND SR.AllowUserToCreateServiceReport = 1)
                                            OR            (COALESCE(SREPHRS.Hours,0) + COALESCE(SREPHRS.OTHours,0)) > 0
                                     GROUP BY      SR.RepID      ))

INSERT 
INTO #TEMPLIST (Region, Subregion, Service_Rep, EmployeeNumber, Date, DOW)
   (      SELECT               tRegions.Region, COALESCE(tSubregions.Description,'') as SubRegion, tServiceReps.[Service Rep] as Service_Rep, tServiceReps.EmployeeNumber, 
                                     DATEADD(day, 3, @BegDate) as Date, DATENAME(dw,DATEADD(day, 3, @BegDate)) as DOW 
          FROM                 UnitDatabase.dbo.tServiceReps as tServiceReps
          LEFT OUTER JOIN      UnitDatabase.dbo.tSubregions as tSubregions on tServiceReps.Subregion = tSubregions.ID
          LEFT OUTER JOIN      UnitDatabase.dbo.tRegions as tRegions on tServiceReps.RegionCode = tRegions.RegionAcctCode and tRegions.Deactivated = 0
          Where                tServiceReps.RepID IN
                              (      SELECT        SR.RepID
                                     FROM          tServiceReps SR
                                     LEFT JOIN     tServiceReports SREP ON SR.RepID = SREP.Employee AND SREP.Date between @BegDate and @EndDate
                                     LEFT JOIN     tServiceHours AS SREPHRS ON SREP.ReportNo = SREPHRS.ReportNo
                                     WHERE         (SR.Deactivated = 0 AND SR.AllowUserToCreateServiceReport = 1)
                                            OR            (COALESCE(SREPHRS.Hours,0) + COALESCE(SREPHRS.OTHours,0)) > 0
                                     GROUP BY      SR.RepID      ))
--repeats 14 times total

select TMPLST.Region, TMPLST.Subregion, TMPLST.Service_Rep, 
TMPLST.EmployeeNumber, TMPLST.Date, TMPLST.DOW, COALESCE(REST.REGWORK,0) AS 
REGWORK, COALESCE(REST.OTWORK,0) AS OTWORK, COALESCE(REST.PTO,0) AS PTO, 
COALESCE(REST.HOL,0) AS HOL, COALESCE(REST.SICK,0) AS SICK, 
COALESCE(REST.JURY,0) AS JURY, COALESCE(REST.MIL,0) AS MIL, 
COALESCE(REST.OTHERPTO,0) AS OTHERPOT, COALESCE(REST.TOTALWORK,0) AS 
TOTALWORK, COALESCE(REST.TOTALOFF,0) AS TOTALOFF

FROM   #TEMPLIST TMPLST
LEFT JOIN (

SELECT Region, SubRegion, Service_Rep, EmployeeNumber, Date, DOW, 
SUM(REGWORK) AS REGWORK, SUM(OTWORK) AS OTWORK, SUM(PTO) AS PTO, SUM(HOL) AS 
HOL, SUM(SICK) AS SICK, SUM(JURY) AS JURY, SUM(MIL) AS MIL, SUM(OTHERPTO) AS 
OTHERPTO, SUM(REGWORK) + SUM(OTWORK) AS TOTALWORK, SUM(PTO) + SUM(HOL) + 
SUM(SICK) + SUM(JURY) + SUM(MIL) + SUM(OTHERPTO) AS TOTALOFF
FROM (
                 Select tRegions.Region
                       ,      COALESCE(tSubregions.Description,'') as 
SubRegion
                       ,      tServiceReps.[Service Rep] as Service_Rep
                       ,      tServiceReps.EmployeeNumber
                       ,      CAST(tServiceReports.Date AS DATE) DATE
                       ,      DATENAME(dw,tServiceReports.Date) as DOW
                       ,      CASE WHEN JobCode = 85 THEN 
SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS PTO
                       ,      CASE WHEN JobCode = 86 THEN 
SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS HOL
                       ,      CASE WHEN JobCode = 92 THEN 
SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS JURY
                       ,      CASE WHEN JobCode = 93 THEN SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS MIL
                       ,      CASE WHEN JobCode = 89 THEN SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS OTHERPTO
                       ,      CASE WHEN JobCode = 88 THEN SUM(COALESCE(tServiceHours.Hours,0) + COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS SICK
                       ,      CASE WHEN JobCode NOT IN (85,86,92,93,89,88) THEN SUM(COALESCE(tServiceHours.Hours,0)) ELSE 0 END AS REGWORK
                       ,      CASE WHEN JobCode NOT IN (85,86,92,93,89,88) THEN SUM(COALESCE(tServiceHours.OTHours,0)) ELSE 0 END AS OTWORK
                 From   UnitDatabase.dbo.tServiceReps as tServiceReps
                 LEFT OUTER JOIN      UnitDatabase.dbo.tServiceReports as tServiceReports ON (tServiceReports.Employee = tServiceReps.RepID) AND (tServiceReports.Date between @BegDate and @EndDate)
                 LEFT OUTER JOIN UnitDatabase.dbo.tServiceHours as tServiceHours on (tServiceReports.ReportNo = tServiceHours.ReportNo)
                 LEFT OUTER JOIN UnitDatabase.dbo.tServiceJobCodes as tServiceJobCodes on (tServiceHours.JobCode = tServiceJobCodes.ServiceJobCodes)
                 LEFT OUTER JOIN      UnitDatabase.dbo.tSubregions as tSubregions on tServiceReps.Subregion = tSubregions.ID
                 LEFT OUTER JOIN      UnitDatabase.dbo.tRegions as tRegions on tServiceReps.RegionCode = tRegions.RegionAcctCode and tRegions.Deactivated = 0
                 GROUP BY      tRegions.Region, COALESCE(tSubregions.Description,''), tServiceReps.[Service Rep], tServiceReps.EmployeeNumber, tServiceReports.Date, DATENAME(dw,tServiceReports.Date), JobCode
          ) TMP 
GROUP BY      Region, SubRegion, Service_Rep, EmployeeNumber, Date, DOW
) AS REST ON TMPLST.Service_Rep = REST.Service_Rep AND TMPLST.Date = REST.Date
Order by      TMPLST.Region,
                 TMPLST.Service_Rep,
                 TMPLST.Date

Answer 2

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.