I am trying to incorporate so that each ContactCd does not create a new row. I don’t know how to make my example less complex. If I dumb it down won’t it take out all the joins that are necessary for my query?
SELECT
jobNew_HeaderFile.JobCd,
GRP = max( case when jobCtrl_Rollup.RollupCatCd = 'GRP' then jobCtrl_Rollup.RollupCd end ),
REG = max( case when jobCtrl_Rollup.RollupCatCd = 'REG' then jobCtrl_Rollup.RollupCd end ),
' ' AS 'JOB_1',
' ' AS 'FIELD OPS MANAGER',
' ' AS 'NATIONAL OPS MANAGER',
DIV = max( case when jobCtrl_Rollup.RollupCatCd = 'DIV' then jobCtrl_Rollup.RollupCd end ),
jobCtrl_Company.Company,
jobNew_HeaderFile.JobName AS LOCATION,
jobNew_SiteDetail.JobAddr1 AS Addr1,
jobNew_SiteDetail.JobAddr2 AS Addr2,
jobNew_SiteDetail.JobCity AS City,
jobNew_SiteDetail.JobState AS State,
jobNew_SiteDetail.JobZip1 AS Zip,
jobNew_SiteDetail_Contacts.ContactCd,
jobNew_SiteDetail_Contacts.FirstName AS FNAME1,
jobNew_SiteDetail_Contacts.LastName AS LNAME1,
jobNew_SiteDetail_Contacts.Title AS TITLE1,
jobNew_SiteDetail_Contacts.FirstName AS FNAME2,
jobNew_SiteDetail_Contacts.LastName AS LNAME2,
jobNew_SiteDetail_Contacts.Title AS TITLE2,
jobNew_SiteDetail_Contacts.FirstName AS FNAME3,
jobNew_SiteDetail_Contacts.LastName AS LNAME3,
jobNew_SiteDetail_Contacts.Title AS TITLE3,
jobNew_SiteDetail_Contacts.Phone_wk,
jobNew_SiteDetail_Contacts.Phone_cell,
jobNew_SiteDetail_Contacts.email AS Email,
jobNew_SiteDetail.ICCPhone AS ICC_Phone,
jobNew_SiteDetail.ICCPhoneNote AS ICC_Phone_Note,
jobNew_SiteDetail.Email AS ICC_Email,
mstClients.ClientCd,
mstClients.ClientName,
jobCtrl_Rollup.RollupCatCd,
jobCtrl_Rollup.RollupCd
FROM jobNew_HeaderFile
INNER JOIN jobNew_SiteDetail ON jobNew_HeaderFile.JobCd = jobNew_SiteDetail.JobCd
INNER JOIN jobNew_SiteDetail_Contacts ON jobNew_HeaderFile.JobCd = jobNew_SiteDetail_Contacts.JobCd
INNER JOIN jobCtrl_Company ON jobNew_HeaderFile.JobCd = jobCtrl_Company.JobCd
INNER JOIN mstClients ON jobNew_SiteDetail.ClientCd = mstClients.ClientCd
INNER JOIN jobCtrl_Rollup ON jobNew_HeaderFile.JobCd = jobCtrl_Rollup.JobCd
WHERE
jobNew_HeaderFile.JobCd = 990
--AND jobNew_SiteDetail.ActiveStatus = '1'
AND jobCtrl_Rollup.CurrentYN = '1'
GROUP BY
jobNew_HeaderFile.JobCd,
jobCtrl_Company.Company,
jobNew_HeaderFile.JobName,
jobNew_SiteDetail.JobAddr1,
jobNew_SiteDetail.JobAddr2,
jobNew_SiteDetail.JobCity,
jobNew_SiteDetail.JobState,
jobNew_SiteDetail.JobZip1,
jobNew_SiteDetail_Contacts.ContactCd,
jobNew_SiteDetail_Contacts.FirstName,
jobNew_SiteDetail_Contacts.LastName,
jobNew_SiteDetail_Contacts.Title,
jobNew_SiteDetail_Contacts.Phone_wk,
jobNew_SiteDetail_Contacts.Phone_cell,
jobNew_SiteDetail_Contacts.email,
jobNew_SiteDetail.ICCPhone,
jobNew_SiteDetail.ICCPhoneNote,
jobNew_SiteDetail.Email,
mstClients.ClientCd,
mstClients.ClientName,
jobCtrl_Rollup.RollupCatCd,
jobCtrl_Rollup.RollupCd
The AGGREGATE I was attempting to include is:
Select JobCd
,FName1 = max( case when RN=1 then FName end)
,LName1 = max( case when RN=1 then LName end)
,FName2 = max( case when RN=2 then FName end)
,LName2 = max( case when RN=2 then LName end)
,FName3 = max( case when RN=3 then FName end)
,LName3 = max( case when RN=3 then LName end)
From (
Select JobCd
,RN = row_number() over (partition by JobCd order by [ContactCd])
,FName = parsename(replace([FirstName],' ','.'),1)
,LName = parsename(replace([LastName],' ','.'),1)
From jobNew_SiteDetail_Contacts
) A
Group By JobCd
Desired result:
| JobCd | FName1 | LName1 | FName2 | LName2 | FName3 | LName3 |
|---|---|---|---|---|---|---|
| 990 | Becky | Smith | Jeffrey | Mason | Christian | Faultenberger |
6
This is impossible to answer, for example, we don’t know what are the relations between the Job table and the rest. Is it 1-many etc.
But why not do the aggregation you need first, and then join rest of the tables again? This would simplify your query.
Something like:
select * from (
select ID, MAX(...) from
Sometable GROUP BY ID
) x
INNER JOIN SomeDetails sd
ON sd.ID = x.ID
INNER JOIN SomeMoreDetails sd2 ON sd2.ID = x.ID`
... rince and repeat
This way you don’t need to aggregate a billion fields.
Also, your query example has:
jobNew_HeaderFile.JobName AS LOCATION, jobNew_SiteDetail.JobAddr1 AS Addr1, jobNew_SiteDetail.JobAddr2 AS Addr2, jobNew_SiteDetail.JobCity AS City, jobNew_SiteDetail.JobState AS State, jobNew_SiteDetail.JobZip1 AS Zip,
Do you think you have to specify all the columns in the question? In my opinion, your question would be much easier if it was only:
jobNew_HeaderFile.JobName AS LOCATION
Further with meaningful short aliases its much easier to read e.g.
jobNew_SiteDetail.JobAddr1 AS Addr1,
jobNew_SiteDetail.JobAddr2 AS Addr2,
jobNew_SiteDetail.JobCity AS City,
jobNew_SiteDetail.JobState AS State,
jobNew_SiteDetail.JobZip1 AS Zip,
becomes with alias jsd (JobSiteDetail)
jsd.JobAddr1 AS Addr1,
jsd.JobAddr2 AS Addr2,
jsd.JobCity AS City,
jsd.JobState AS State,
jsd.JobZip1 AS Zip,
4