Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Aggregate issue Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 7:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:02 PM
Points: 323, Visits: 441
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
Post #1448036
Posted Tuesday, April 30, 2013 10:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 4,411, Visits: 6,284
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
Post #1448126
Posted Tuesday, April 30, 2013 11:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:02 PM
Points: 323, Visits: 441
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

  Post Attachments 
Capture.PNG (2 views, 27.09 KB)
Post #1448147
Posted Tuesday, April 30, 2013 11:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 4,411, Visits: 6,284
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
Post #1448177
Posted Tuesday, April 30, 2013 12:10 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:02 PM
Points: 323, Visits: 441
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
Post #1448185
Posted Tuesday, April 30, 2013 1:30 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 4,411, Visits: 6,284
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.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1448216
Posted Tuesday, April 30, 2013 2:02 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 3:02 PM
Points: 323, Visits: 441
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
Post #1448228
Posted Tuesday, April 30, 2013 2:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:15 PM
Points: 20,755, Visits: 32,572
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.



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)
Post #1448238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse