0

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
  • can you post the output of the second select (the one you are selecting from). – Dominique Boucher Dec 29 '17 at 17:01
  • @DominiqueBoucher, i have included the inner select statement output in original post. – joshua ortiz Dec 29 '17 at 17:11
  • Also, why are you grouping the result of the second query as you are not using any agreagating function ? – Dominique Boucher Dec 29 '17 at 17:14
  • does the tServiceHours table contains an entry for every hours per ReportNo (including null values) ? – Dominique Boucher Dec 29 '17 at 17:23
  • Actually, if you could post a "select top 10 * from .." of every table involve, that will probably help to define where you need a right join instead of a left join :) (make sure that is those table contains null, it's reflected in the data you will show) – Dominique Boucher Dec 29 '17 at 17:25
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.

  • I have revised the entire query over the last couple hours. I will post it below. It is now complete, but I am looking for a way to use a for loop or while loop to shorten it. – joshua ortiz Dec 29 '17 at 19:56
  • actually it posted "above". I just need a way to loop the top part of my code, as to not have it repeated 14 times. It works, I am just trying to clean it up at the moment. Any help is appreciated. – joshua ortiz Dec 29 '17 at 20:11
0

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
  • I was able to add another variable (@countdate) and do a while != to @enddate, and increment to have it loop as i needed it to. Thanks All! – joshua ortiz Dec 29 '17 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.