changing from inner joins to left joins to include null values?LEFT JOIN conversion to INNER JOIN, can't...
How could a scammer know the apps on my phone / iTunes account?
How to deal with a cynical class?
Rules about breaking the rules. How do I do it well?
I need to drive a 7/16" nut but am unsure how to use the socket I bought for my screwdriver
Happy pi day, everyone!
What is IP squat space
The use of "touch" and "touch on" in context
How to deal with taxi scam when on vacation?
Bastion server: use TCP forwarding VS placing private key on server
At what level can a dragon innately cast its spells?
My adviser wants to be the first author
Theorems like the Lovász Local Lemma?
How do I hide Chekhov's Gun?
Schematic conventions for different supply rails
Font with correct density?
Did CPM support custom hardware using device drivers?
Life insurance that covers only simultaneous/dual deaths
Cultural lunch issues
Can anyone tell me why this program fails?
Informing my boss about remarks from a nasty colleague
Does splitting a potentially monolithic application into several smaller ones help prevent bugs?
Where is the 1/8 CR apprentice in Volo's Guide to Monsters?
Official degrees of earth’s rotation per day
What are the possible solutions of the given equation?
changing from inner joins to left joins to include null values?
LEFT JOIN conversion to INNER JOIN, can't change FROM clauseOptimizing multi-table left joinsInner Joins between Subsets of the Same TableSingle value from a list of comma separated values when used in inner join in SQL ServerCan I make this multiple join query faster?Include NULL row on joinSimilar query, run times much differentReplace Subquery with JOIN - MYSQLSQL left join returns null columnInner join on output of a subquery
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
sql-server join
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 4 more comments
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
sql-server join
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
|
show 4 more comments
0
0
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
sql-server join
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
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
sql-server join
sql-server join
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
edited Dec 29 '17 at 18:09
joshua ortiz
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
asked Dec 29 '17 at 16:51
joshua ortizjoshua ortiz
34
34
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
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
|
show 4 more comments
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
can you post the output of the second select (the one you are selecting from).
– Dominique Boucher
Dec 29 '17 at 17:01
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
@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
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
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
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
|
show 4 more comments
2 Answers
2
active
oldest
votes
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
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
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
add a comment |
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.
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
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
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f194198%2fchanging-from-inner-joins-to-left-joins-to-include-null-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
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
add a comment |
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
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
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
add a comment |
0
0
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
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
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
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
answered Dec 29 '17 at 20:07
joshua ortizjoshua ortiz
34
34
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
add a comment |
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
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
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
add a comment |
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.
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
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
add a comment |
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.
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
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
add a comment |
0
0
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.
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
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.
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
edited Dec 30 '17 at 16:26
Pieter Geerkens
1,602816
1,602816
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
answered Dec 29 '17 at 19:38
Dominique BoucherDominique Boucher
37117
37117
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
add a comment |
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
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
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
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
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f194198%2fchanging-from-inner-joins-to-left-joins-to-include-null-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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