Sure. Clues.
In the query where you get the correct sum, you are *ONLY* joining 3 tables. (And I don't see why you are doing LEFT JOINs; I'm pretty sure you would get same results using INNER JOINs, which will be more efficient.)
In the merged query, you are doing an INNER JOIN to two tables not mentioned in the first query, so you are limiting which records you can get much more. Quite honestly, I don't see WHY the limiting affects your SUM, but then I don't know your DB.
OH!!! YES I DO!!!
The WHERE clause in that merged query (and, indeed, in your VERY FIRST QUERY) is basically WIPING OUT the LEFT JOINs and turning them into INNER JOINs!!!!
You CAN NOT use a WHERE clause on the right-side table of a LEFT JOIN without, effectively, converting the LEFT JOIN into an inner join!!
To see why, see this old old post of mine:
http://www.aspmessageboard.com/forum...96501&F=20&P=1
*MAYBE* you can fix it by moving that condition from WHERE to ON, thus:
Code:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production,
sum(EmployeeLaborEvent.Hours) as hours
FROM dbo.job
left outer join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
left outer join dbo.ProductionEvent on Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON Event.ItemGuid = dbo.Item.ItemGuid
inner join dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON (
dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
AND dbo.SourceType.CompanySourceTypeId IN ('PR','MA')
)
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
left outer join dbo.EmployeeLaborEvent ON Event.EventGuid = dbo.Employeelaborevent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048
GROUP BY dbo.Job.CompanyJobId, dbo.job.name, dbo.Job.ChangeDate, dbo.job.Name, dbo.Job.Active
But I'm suspicious. And you have several LEFT JOINs in there that seem to have no purpose at all. You never use any fields from the ITEM or REGION or PRODUCT tables. And since they are all LEFT JOINed, that makes them pointless (they aren't restricting any choices in the other tables).
So here's another attempt. Simplified somewhat, including using aliases to make the code more readable (well, to me at least). But really no guarantee, at all.
Code:
SELECT JB.CompanyJobId, JB.Name, JB.Name, JB.ChangeDate, JB.Active,
sum(case ST.CompanySourceTypeId WHEN 'MA' then PEV.AlternateQuantity ELSE 0 END) AS material,
sum(case ST.CompanySourceTypeId WHEN 'PR' THEN PEV.Quantity ELSE 0 END) AS production,
sum(ELE.Hours) as hours
FROM dbo.job AS JB
left outer join dbo.Event AS EV ON JB.JobGuid = EV.JobGuid
left outer join (
dbo.ProductionEvent AS PEV inner join dbo.Source AS S
ON PEV.SourceGuid = S.SourceGuid
) on EV.EventGuid = PEV.EventGuid
inner join (
dbo.Source inner JOIN dbo.SourceType AS ST
ON ( S.SourceTypeGuid = ST.SourceTypeGuid AND ST.CompanySourceTypeId IN ('PR','MA') )
) ON PEV.SourceGuid = S.SourceGuid
left outer join dbo.EmployeeLaborEvent AS ELE ON EV.EventGuid = ELE.EventGuid
WHERE JB.CompanyJobId = 3505048
GROUP BY JB.CompanyJobId, JB.name, JB.ChangeDate, JB.Name, JB.Active
You may have to resort to doing this the same way I showed you with the other one. Create a temp table and then join to it. Not sure.
Combine two queries
