April 30, 2013 at 7:47 am
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
April 30, 2013 at 10:12 am
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 on googles mail service
April 30, 2013 at 11:01 am
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
April 30, 2013 at 11:56 am
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 on googles mail service
April 30, 2013 at 12:10 pm
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
April 30, 2013 at 1:30 pm
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 on googles mail service
April 30, 2013 at 2:02 pm
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
April 30, 2013 at 2:37 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply