Crazy Join Query

[edmana]

Hey all,

I have a real doozie of a query here. I have 4 tables:

tblProjects
ProjectKeyId
ProjectDescription

tblPriceBookLabor
ItemID
Description

tblEstimatedLabor
ProjectID
ItemID
TotalCost

tblActual Labor
ProjectID
ItemID
TotalCost

tblProjects is the table from which I will link. tblPriceBookLabor shows a list of all labor items. tblEstimatedLabor and tblActualLabor contains many line items per project that repeat (multiple items of labor can repeat). What I am looking to do is provide the master list from tblPriceBookLabor and show the corresponding Estimated and Actaul Costs. For example:

tblProjects
ProjectID ProjectDescription
1 Project 1
2 Project 2

tblPriceBookLabor
ItemID Description
LABOR1 Labor Type 1
LABOR2 Labor Type 2
LABOR3 Labor Type 3
LABOR4 Labor Type 4
LABOR5 Labor Type 5
LAOBR6 Labor Type 6

tblEstimatedLabor
ItemID TotalCost ProjectID
LABOR1 1,000 1
LABOR2 500 1
LABOR3 500 1
LABOR1 1,000 1

tblActualLabor
ItemID TotalCost ProjectID
LABOR1 1,500 1
LABOR4 500 1
LABOR5 500 1
LABOR5 500 1
LABOR5 500 1

The results would look like this:

ItemID TotalEstimated Total Actual ProjectID
LABOR1 2,000 1,500 1
LABOR2 500 0 1
LABOR3 500 0 1
LABOR4 0 500 1
LABOR5 0 1500 1
LABOR6 0 0 1

I was able to do a left outer join using the tblPriceBookLabor table and tblEstimatedLabor. This gave me all in the price book table. However, when I then put in the where clause for the ProjectID, I only received the relavent items from the estimated table, not all rows as I would expect.

Anyone have any suggestions or ideas?

Thanks!
Ed

 

[markros]

Try

select B.ItemID, TE.TotalEstimated, TA.[Total Actual], P.ProjectID from tblProject P inner join tblPriceBookLabor B on P.ProjectID = B.ProjectID LEFT JOIN 
(select ItemID, sum(TotalCost) as TotalEstimated, ProjectID from 
tblEstimatedLabor group by ProjectID, ItemID) TE
on B.ProjectID = TE.ProjectID and B.ItemID = TE.ItemID
LEFT JOIN 
select ItemID, sum(TotalCost) as [Total Actual], ProjectID from 
tblActualLabor group by ProjectID, ItemID) TA
on B.ProjectID = TA.ProjectID and B.ItemID = TA.ItemID

PluralSight Learning Library

 

[edmana]

Markros,

Thanks for the reply. This is erroring out. I believe it is because on the inner join, you are liking ProjectKeyID to the tblPriceBookLabor table. This table doesn't have that column in it.

Ed

 

[markros]

I see now. Looks like we may want to CROSS JOIN Projects and BookingLabor, e.g. remove join between these two tables, but use both of them in the from clause, e.g.

from tblProjects P, tblBooking B left join (...) TA

on P.ProjectID = TA.ProjectID and B.ItemID = TA.ItemID etc.

We will have cross join between projects and booking.


PluralSight Learning Library

 

[gmmastros]

I was able to do a left outer join using the tblPriceBookLabor table and tblEstimatedLabor. This gave me all in the price book table. However, when I then put in the where clause for the ProjectID, I only received the relavent items from the estimated table, not all rows as I would expect.

Can you show the query you used? Most of the time, when a left join doesn't return the results you expect, it's because you put a where clause condition for the right table. Usually, the solution is to put the condition in the ON clause. Show you're query and I'll show you what I mean.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

George,

I played around w/ the query and got the results to show up if I put stuff in the ON clause as you indicated. Here is the first query I got to work:

SELECT dbo.tvwrc_PriceBookLabor.ItemID, dbo.tvwrc_ProjectLaborEstimated.ProjectKeyID, COALESCE (SUM(dbo.tvwrc_ProjectLaborEstimated.Quantity), 0)
AS LaborQuantityEstimated, COALESCE (SUM(dbo.tvwrc_ProjectLaborEstimated.TotalCost), 0) AS LaborCostEstimated
FROM dbo.tvwrc_PriceBookLabor LEFT OUTER JOIN
dbo.tvwrc_ProjectLaborEstimated ON dbo.tvwrc_PriceBookLabor.ItemID = dbo.tvwrc_ProjectLaborEstimated.ItemID
GROUP BY dbo.tvwrc_PriceBookLabor.ItemID, dbo.tvwrc_ProjectLaborEstimated.ProjectKeyID

Here is the second query I used:

SELECT dbo.tvwrc_PriceBookLabor.ItemID, dbo.tvwrc_ProjectLaborLogs.ProjectKeyID, COALESCE (SUM(dbo.tvwrc_ProjectLaborLogs.TotalTime), 0)
AS LaborQuantityActual, COALESCE (SUM(dbo.tvwrc_ProjectLaborLogs.TotalCost), 0) AS LaborCostActual
FROM dbo.tvwrc_PriceBookLabor LEFT OUTER JOIN
dbo.tvwrc_ProjectLaborLogs ON dbo.tvwrc_PriceBookLabor.ItemID = dbo.tvwrc_ProjectLaborLogs.ItemID
GROUP BY dbo.tvwrc_PriceBookLabor.ItemID, dbo.tvwrc_ProjectLaborLogs.ProjectKeyID

I put in the second group by of ProjectID so I could show it on the returned recordset so I could filter by it in Crystal Reports.

The ultimate goal is to have all rows returned in tvwrc_PriceBookLabor and to have the relevant values returned within estimated and actual.

 

[markros]

You can now do a FULL OUTER JOIN between these two queries based on project ID and ItemID - use the first two queries as derived tables or CTE.

PluralSight Learning Library

 

[edmana]

I tried the above w/ the following query:

SELECT dbo.tvwrc_ProjectLaborEstimatedSummary.ProjectKeyID, dbo.tvwrc_ProjectLaborEstimatedSummary.ItemID,
COALESCE (dbo.tvwrc_ProjectLaborEstimatedSummary.LaborQuantityEstimated, 0) AS LaborQuantityEstimated,
COALESCE (dbo.tvwrc_ProjectLaborEstimatedSummary.LaborCostEstimated, 0) AS LaborCostEstimated,
COALESCE (dbo.tvwrc_ProjectLaborActualSummary.LaborQuantityActual, 0) AS LaborQuantityActual,
COALESCE (dbo.tvwrc_ProjectLaborActualSummary.LaborCostActual, 0) AS LaborCostActual,
COALESCE (dbo.tvwrc_ProjectLaborActualSummary.LaborQuantityActual / dbo.tvwrc_ProjectLaborEstimatedSummary.LaborQuantityEstimated, 0)
AS Variance
FROM dbo.tvwrc_ProjectLaborEstimatedSummary FULL OUTER JOIN
dbo.tvwrc_ProjectLaborActualSummary ON
dbo.tvwrc_ProjectLaborEstimatedSummary.ProjectKeyID = dbo.tvwrc_ProjectLaborActualSummary.ProjectKeyID AND
dbo.tvwrc_ProjectLaborEstimatedSummary.ItemID = dbo.tvwrc_ProjectLaborActualSummary.ItemID

I now get all of the data in estimated along with 0s for where it doesn't exist in actual. However, what is in actual but not in estimated fails to appear. I suspect it could be because it looks at both ItemID and ProjectKeyID.

 

[gmmastros]

I think you need a cross join and two left joins here. You need to be very careful when you use a cross join. Cross joins do NOT have an on clause. With a cross join, you get a cartesian product of all rows in both tables. So, if table1 has 20 rows and table2 has 2,000 rows, you will get 40,000 rows as a result. Obviously, as your tables grow in size, you could end up with a REALLY large result that could be very slow to execute.

I was able to duplicate the results you are looking for based on the sample data from your initial post. In the code below, I create some table variables with sample data. This allowed me to test the query. You can copy/paste this directly to a query window to see how it works. If you are satisfied that it returns the correct results, remove the table variables and change the bottom query to use your real tables instead of the table variables.

Declare @tblProjects Table(ProjectId Int, ProjectDescription VarChar(20))
Insert Into @tblProjects Values(1,'Project 1')
Insert Into @tblProjects Values(2,'Project 2')

Declare @tblPriceBookLabor Table(ItemId VarChar(20), Description VarChar(20))
Insert Into @tblPriceBookLabor Values('LABOR1','Labor Type 1')
Insert Into @tblPriceBookLabor Values('LABOR2','Labor Type 2')
Insert Into @tblPriceBookLabor Values('LABOR3','Labor Type 3')
Insert Into @tblPriceBookLabor Values('LABOR4','Labor Type 4')
Insert Into @tblPriceBookLabor Values('LABOR5','Labor Type 5')
Insert Into @tblPriceBookLabor Values('LAOBR6','Labor Type 6')

Declare @tblEstimatedLabor Table(ItemID VarChar(20), TotalCost Int, ProjectID Int)
Insert Into @tblEstimatedLabor Values('LABOR1',1000,1)
Insert Into @tblEstimatedLabor Values('LABOR2',500 ,1)
Insert Into @tblEstimatedLabor Values('LABOR3',500 ,1)
Insert Into @tblEstimatedLabor Values('LABOR1',1000,1)

Declare @tblActualLabor Table(ItemID VarChar(20), TotalCost Int, ProjectID Int)
Insert Into @tblActualLabor Values('LABOR1',1500,1)
Insert Into @tblActualLabor Values('LABOR4',500 ,1)
Insert Into @tblActualLabor Values('LABOR5',500 ,1)
Insert Into @tblActualLabor Values('LABOR5',500 ,1)
Insert Into @tblActualLabor Values('LABOR5',500 ,1)


Select PBL.ItemId, 
       Coalesce(EL_Totals.TotalCost, 0) As TotalEstimated, 
       Coalesce(A_Totals.TotalCost, 0) As TotalActual,
       P.ProjectId
From   @tblProjects P
       Cross Join @tblPriceBookLabor PBL
       Left Join (
          Select ProjectId, ItemId, Sum(TotalCost) As TotalCost
          From   @tblEstimatedLabor
          Group By ProjectId, ItemId
          ) As EL_Totals
          On  P.ProjectId = EL_Totals.ProjectId
          And PBL.ItemId = EL_Totals.ItemId
       Left Join (
          Select ProjectId, ItemId, Sum(TotalCost) As TotalCost
          From   @tblActualLabor
          Group By ProjectId, ItemId
          ) As A_Totals
          On  P.ProjectId = A_Totals.ProjectId
          And PBL.ItemId = A_Totals.ItemId
Where  P.ProjectId = 1
Order By P.ProjectId, PBL.ItemId

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You misread my suggestion. I suggested to start from your two working queries and full join them using them as either derived tables or CTEs.

Alternatively I originally suggested what George is now suggesting as well.

Either way I think is supposed to work.

PluralSight Learning Library

 

[gmmastros]

I don't think a full join would work because it would not return items that do not exist in either table (for a project).

Looking at the expected results:

[tt][blue]
LABOR6 0 0 1
[/blue][/tt]

LABOR6 doesn't appear in the estimated or actual tables for projectId = 1, but edmana wants a row in the output showing 0's for actual and estimated.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Then use cross join as I originally suggested.

PluralSight Learning Library

 

[edmana]

George,

This seems to work beautifully, thanks!

Markros,

I haven't executed a query like this before. Please excuse my "newbieness"

Ed

 

[gmmastros]

I'm glad [red][black]my[/black][/red] markros's suggestion was helpful.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[edmana]

George,

I have this query working just fine. The Crystal report takes forever to execute. As you mentioned, I believe the query runs on the entire Cartesian product before it then filters out on the report. Assuming I am ok with only Items that are existent in either table, how would I take the below query (the actual one I am using) and turn it into a Full Join query?

SELECT TOP 100 PERCENT PBL.ItemID, PBL.ItemDescription, COALESCE (E_Totals.LaborCostEstimated, 0) AS LaborCostEstimated,
COALESCE (E_Totals.LaborQuantityEstimated, 0) AS LaborQuantityEstimated, COALESCE (A_Totals.LaborCostActual, 0) AS LaborCostActual,
COALESCE (A_Totals.LaborQuantityActual, 0) AS LaborQuantityActual, (CASE WHEN COALESCE (E_Totals.LaborQuantityEstimated, 0)
> 0 THEN COALESCE (A_Totals.LaborQuantityActual, 0) / COALESCE (E_Totals.LaborQuantityEstimated, 0) * 100 ELSE 0 END) AS Variance,
COALESCE (A_Totals.LaborQuantityActual, 0) - COALESCE (E_Totals.LaborQuantityEstimated, 0) AS Difference, P.ProjectKeyID
FROM dbo.tblProjects P CROSS JOIN
dbo.tvwrc_PriceBookLabor PBL LEFT OUTER JOIN
(SELECT ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostEstimated, SUM(Quantity) AS LaborQuantityEstimated
FROM tvwrc_ProjectLaborEstimated
GROUP BY ProjectKeyId, ItemId) E_Totals ON P.ProjectKeyID = E_Totals.ProjectKeyId AND PBL.ItemID = E_Totals.ItemId LEFT OUTER JOIN
(SELECT ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostActual, SUM(TotalTime) AS LaborQuantityActual
FROM tvwrc_ProjectLaborLogs
GROUP BY ProjectKeyId, ItemId) A_Totals ON P.ProjectKeyID = A_Totals.ProjectKeyId AND PBL.ItemID = A_Totals.ItemId
ORDER BY P.ProjectKeyID, PBL.ItemDescription

I was playing with Full Join queries over the weekend and got it so that I got all of the estimated showing up but not all of the actual...

 

[gmmastros]

Does your report run for a single ProjectId? If so, we can improve performance a lot and still return the information you are looking for. It probably means using a stored procedure instead of a view.

I haven't used Crystal Reports in 10 years (or more). I KNOW you can use a stored procedure as the source for a crystal reports report, but I don't actually know how to do it. Do you know how to use a stored procedure with Crystal Reports?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

1. Assuming you don't need ItemDescription, then your query will be

select ISNULL(E_Totals.ProjectKeyID, A_Totals.ProjectKeyID) as ProjectKeyID,
ISNULL(E_Totals.ItemID, A_Totals.ItemID) as ItemID,
LaborCostEstimated, LaborQuantityEstimated,
LaborCostActual, LaborQuantityActual                            
from
   (SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostEstimated, SUM(Quantity) AS LaborQuantityEstimated                            FROM          tvwrc_ProjectLaborEstimated                            GROUP BY ProjectKeyId, ItemId) E_Totals ON P.ProjectKeyID = E_Totals.ProjectKeyId AND PBL.ItemID = E_Totals.ItemId FULL OUTER JOIN                          (SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostActual, SUM(TotalTime) AS LaborQuantityActual                            FROM          tvwrc_ProjectLaborLogs                            GROUP BY ProjectKeyId, ItemId) A_Totals ON E_Totals.ProjectKeyID = A_Totals.ProjectKeyId AND E_Totals.ItemID = A_Totals.ItemId
ORDER BY 1,2

Alternatively, it's very easy to add the Booking items and we will start from it.

We really don't even need to join with the Projects table (unless we also want a project description).

PluralSight Learning Library

 

[edmana]

George,

This will be for a single ProjectKeyID only. I can use a stored procedure within Crystal with no problem (It just adds in like a table or query). I have not yet created my own stored procedure. If you are willing to help, I am willing to try!

Markros,

I will need the item description. I don't necessarily need the Projects table as I am linking to that within the Crystal Report. I also ran the query above and got a parse error in SQL, incorrect syntax near the keyword ON...

 

[markros]

In the query above I left some stuff from the original. Corrected version would have been

select ISNULL(E_Totals.ProjectKeyID, A_Totals.ProjectKeyID) as ProjectKeyID,ISNULL(E_Totals.ItemID, A_Totals.ItemID) as ItemID,LaborCostEstimated, LaborQuantityEstimated,LaborCostActual, LaborQuantityActual                            from   (SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostEstimated, SUM(Quantity) AS LaborQuantityEstimated                            FROM          tvwrc_ProjectLaborEstimated                            GROUP BY ProjectKeyId, ItemId) E_Totals 
FULL OUTER JOIN 
(SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostActual, SUM(TotalTime) AS LaborQuantityActual                            FROM          tvwrc_ProjectLaborLogs                            GROUP BY ProjectKeyId, ItemId) A_Totals ON E_Totals.ProjectKeyID = A_Totals.ProjectKeyId AND E_Totals.ItemID = A_Totals.ItemIdORDER BY 1,2

If you need to have Item Descriptions, start from Booking table and left join with each of the costs.

In other words, use the same query as George provided, but remove Projects table and CROSS JOIN from it.

PluralSight Learning Library

 

[gmmastros]

To create a stored procedure...

Open SQL Server Management Studio.
Open a new query window.

Copy/paste this:

Use [!]YourDatabaseName[/!]
Go
Create Procedure [!]MyFancySchmancyReport[/!]
  @ProjectId Int
As
SET NOCOUNT ON
SELECT PBL.ItemID, PBL.ItemDescription, COALESCE (E_Totals.LaborCostEstimated, 0) AS LaborCostEstimated,
       COALESCE (E_Totals.LaborQuantityEstimated, 0) AS LaborQuantityEstimated, COALESCE (A_Totals.LaborCostActual, 0) AS LaborCostActual,
       COALESCE (A_Totals.LaborQuantityActual, 0) AS LaborQuantityActual, (CASE WHEN COALESCE (E_Totals.LaborQuantityEstimated, 0)
       > 0 THEN COALESCE (A_Totals.LaborQuantityActual, 0) / COALESCE (E_Totals.LaborQuantityEstimated, 0) * 100 ELSE 0 END) AS Variance,
       COALESCE (A_Totals.LaborQuantityActual, 0) - COALESCE (E_Totals.LaborQuantityEstimated, 0) AS Difference, P.ProjectKeyID
FROM   dbo.tblProjects P Left JOIN
       dbo.tvwrc_PriceBookLabor PBL On P.ProjectId = @ProjectId LEFT OUTER JOIN
       (SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostEstimated, SUM(Quantity) AS LaborQuantityEstimated
       FROM          tvwrc_ProjectLaborEstimated
       GROUP BY ProjectKeyId, ItemId) E_Totals ON P.ProjectKeyID = E_Totals.ProjectKeyId AND PBL.ItemID = E_Totals.ItemId LEFT OUTER JOIN
       (SELECT     ProjectKeyId, ItemId, SUM(TotalCost) AS LaborCostActual, SUM(TotalTime) AS LaborQuantityActual
       FROM          tvwrc_ProjectLaborLogs
       GROUP BY ProjectKeyId, ItemId) A_Totals ON P.ProjectKeyID = A_Totals.ProjectKeyId AND PBL.ItemID = A_Totals.ItemId
Where  P.ProjectId = @ProjectId
ORDER BY P.ProjectKeyID, PBL.ItemDescription

Now, press F5 (the hot key you use to run code in a query window).

Presto! You just created your first stored procedure.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom