Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Aggregate issue


Aggregate issue

Author
Message
JimS-Indy
JimS-Indy
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 444
Here's my SQL sequence:
SELECT     M.BoMWOID, CAST(SUM(M.BoMEstimatedMaterialCost) AS money) AS BoMTotMaterialEstimate, 
CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty) AS money) AS BoMTotLaborHrsEstimate,
SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate) AS BoMTotLaborCostEstimate,
CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate + M.BoMEstimatedMaterialCost) AS money)
AS BoMTotCostEstimate
FROM dbo.tblBoM AS M INNER JOIN
dbo.tblBoMDetail AS D ON M.BoMID = D.BoMID INNER JOIN
dbo.tblWorkOrders AS WO ON M.BoMWOID = WO.ID INNER JOIN
dbo.tblDiscipline AS DI ON WO.WODiscipline = DI.ID
GROUP BY M.BoMWOID



It derives four budget summaries from Bills of Material. Three are no issue, but BoMTotMaterialsEstimate is not working right (my fault...) It should be the sum of M.BoMEstimatedMaterialCost, but it's being multiplied as if it were a detail field. I can't get it straight in my mind how to do this (other than a sub-query.)

"WO" is the Work Order Master. There may be zero or more "M" children. "M" is the BoM Master, and it contains a Materials Estimate for the entire BoM. The "D" table is the BoM Detail child of the "M" table. The hours and labor estimates are calculated and summed up from each "D" row.

So, while three of the totals are summed over the lowest child domain, one (BoMTotMaterialsEstimate) is summed over the BoM Master ("M") domain.

What's the best strategy for this?

Jim
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6010 Visits: 8314
table scripts, sample data and expected outputs would be VERY helpful here. And if you want good performance too please include index scripts and numbers of rows per table. Thanks!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
JimS-Indy
JimS-Indy
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 444
I bludgeoned this to death with a subquery that returns the correct results as follows:
SELECT     M.BoMWOID, m1.BoMTotMaterialEstimate, 
CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty) AS money) AS BoMTotLaborHrsEstimate,
CAST(SUM(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate) AS money) AS BoMTotLaborCostEstimate,
CAST((sum(D.BoMItemUnitLaborHrs * D.BoMLaborComplexity * D.BoMEItemQty * DI.BudgetHourlyRate) + M1.BoMTotMaterialEstimate) AS money)
AS BoMTotCostEstimate
FROM dbo.tblBoM AS M INNER JOIN
dbo.tblBoMDetail AS D ON M.BoMID = D.BoMID INNER JOIN
dbo.tblWorkOrders AS WO ON M.BoMWOID = WO.ID INNER JOIN
dbo.tblDiscipline AS DI ON WO.WODiscipline = DI.ID LEFT JOIN
(select BoMWOID,sum(BoMEstimatedMaterialCost) AS BoMTotMaterialEstimate from tblBoM Group By BoMWOID) as M1 on M.BoMWOID=M1.BoMWOID

Attached is an image of the table structures and relationships:

GROUP BY M.BoMWOID, m1.BoMTotMaterialEstimate



Is this the best approach?

Jim
Attachments
Capture.PNG (2 views, 27.00 KB)
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6010 Visits: 8314
we cannot make use of a picture of a schema to build a query from. I asked you specifically to supply what would enable us to help you. glad you have it working, because there isn't much we can do with what you provided.

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
JimS-Indy
JimS-Indy
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 444
Kevin, the table build scripts for these four tables are probably 200 lines long, containing columns and foreign keys that are not relevant, and may be confidential. I know that what you wanted to do was recreate the structure and code an answer. For that, I'm grateful, but the last time I posted all that information, everyone stopped reading the post because it was so big.

Thanks again.

Jim
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 6010 Visits: 8314
JimS-Indy (4/30/2013)
Kevin, the table build scripts for these four tables are probably 200 lines long, containing columns and foreign keys that are not relevant, and may be confidential. I know that what you wanted to do was recreate the structure and code an answer. For that, I'm grateful, but the last time I posted all that information, everyone stopped reading the post because it was so big.

Thanks again.


Which indicates that the problem is too big/complex for a forum post anyway. Many people don't seem to realize (or care) that forums are for short, mostly-simple, targeted stuff with answers provided by volunteers. :-)

BTW, hope you don't have either a) Cartesians or b) null values or you won't get the answers you expect out of your query. Hehe

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
JimS-Indy
JimS-Indy
Old Hand
Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)Old Hand (347 reputation)

Group: General Forum Members
Points: 347 Visits: 444
Not sure what "Cartesians" are (same as cross-product?), but I don't have nulls. I set a default value and prohibit nulls on these and many other fields where null would be meaningless.

Jim
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24278 Visits: 37987
In many cases we don't actually need the entire table structure to help splve a problem. Many times, only a subset of the columns are relevent to a problem and those are the columns that should be provided along with the PK and other indexed columns (and indexes) that may be a part of the current solution and/or could be used to help provide other solutions.

Sample data should be just that, sample data. Enough data to ensure that we understand the problem domain (should cover edge cases, data to be included in the solution and perhaps even data to be excluded). But not so much that is overwhelmes those trying to help.

And then the expected results based on the sample data. This is actually best shown using a table that is populated using INSERT INTO statements just like the sampel data is provided to populate the source tables. This serves two purposes. One, it gives us a visual of what the results should look like. Two, it actually provides something to test against.

Last, the code you have written so far in an effort to solve your problem or issue. This helps us to understand what you are trying to do and we may see a simple logic error that you have over looked (forest and trees syndrome) or we may come up with a totally different solution.

If some of these things seem like the may overwhelm your post, upload then as text files in a zip file instead.

A clear description of the problem and what you are attempting to accomplish is also very helpful.

The best thing to do is ask yourself, based on what I have posted could I provide an answer to problem based solely on what I have presented with no other knowledge of of the system I am working on. This will tel you if there is additional information that you should consider posting.

Please remember, we don't want production data or anything your company may consider IP or trade secrets.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search