# Calculate Average Build Cost

• Hi. I would like to return the average build cost for a product.

Each product manufactured has a Manufacturing Order.

I have come up with the following:

`SELECT`

` DENSE_RANK() OVER(PARTITION BY P.ProductId ORDER BY m.ManufacturingOrderId) AS 'Dense Rank',`

` P.ProductId,`

` M.ManufacturingOrderId,`

` I.MaterialValue,`

` I.LabourValue,`

` I.OverheadValue,`

` I.TotalValue`

`FROM inventory I`

` INNER JOIN ManufacturingOrders M ON I.ManufacturingOrder = M.ManufacturingOrder`

`AND I.Product = M.Product`

` INNER JOIN Products P ON M.Product = P.Product`

`WHERE I.SourceType = 'M'`

Not sure if the use of DENSE_RANK() is required.

I have attached sample output (not as good as table defs, sample data) but I am unable to post that on public forum.

Output: Number Of Orders = x, AvgMaterialValue = x, AvgLabourValue = x, AvgOverheadValue = x, AvgTotal = x

Any help as always much appreciated.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• Hi Tommy,

Based on the Excel data you attached I kinda assumed the relevant structure of your tables.

Is it really an issue that you're unable to post the following on this public forum as an SQL create statement?

Products

ProductId

Product

ManufacturingOrders

Product

ManufacturingOrderId

ManufacturingOrder

Inventory

Product

ManufacturingOrder

MaterialValue

LabourValue

TotalValue

SourceType

Also, would you mind to provide some ready to use sample data together with the create statements of your tables?

Something like

`INSERT INTO Products(ProductId)`

`SELECT '101-ABC26A' UNION ALL`

`SELECT '102-ABC500CK'`

Finally, could you add the expected result set so we have something to compare against? This would just be great.

I don't want to sound picky but the two facts you provided (being able to post sample data in Excel format but being unable provide the same data in a ready to use format) simply don't match at all...

Edit: Fields in pseudo tables added...

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Hi Lutz, the excel data was sample data (not reflective of the real world) but point taken I can also provide sample scripts......

With regards the expected result......

When the manufacturing order is complete inventory is incremented for each item finished (they may finish at different times). The Manufacturing Order ID is added as the source reference in inventory so may appear multiple times for a particular product. When the record is written to inventory the Material Cost, Labour Cost, Overhead cost are included. I want to find the average build costs for the products.

100 Products are Manufactured.

25 for Product A

50 for product B

10 for Product C

15 for Product D

Find the average build cost.

Hope that makes sense, I will post sample script at the earliest opportunity.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• Hi Phil/Tommy (?),

based on your Excel file, what would be the expected result?

I just used some Excel formulas and I'd assume the following:

Number of products20

MaterialValue4098,5205

LabourValue271,3305

TotalValue6100,976

Is this assumption correct?

Edit: wording modified.

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Hi, see attached, still working on the insert scripts for the data but I have the table defs...

The ManufacturingOrderId appears multiple times as does the Products (for each build, entry into Inventory) each record with its own value.

So say we have 20 orders for a single product each with its own set of values. I want to return the average build cost.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• OK attached is a sample script.....:)

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• Did you actually try your sample script before posting?

table Inventory:

column TotalValue is missing.

column ManufacturingOrder is missing.

Insert statement fails on table Inventory: trying to insert value 'MO019001' into column [SourceType]

You're trying to insert a value from table ManufacturingOrders, column ManufacturingOrderId but using column ManufacturingOrder in your join condition.

So, the sample data cannot be used. Therefore, the query cannot be tested. It also would make it really hard to verify the result set due to the lack of any expected values.

I'm taking some time off (it's almost one o'clock in the morning over here...).

You might want to use the time in between to provide some usable data.

Or maybe there will be somebody else joining the challenge in between.

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• FYI, It is always a good idea to test any code you provide in a sandbox database to ensure it works correctly. We are volunteers on this site, and we do have other things we could be doing rather than fixing broken code that is provided that should be helping us to help you.

• OK back to the correct post....

Lutz posted:

Hi Phil,

even though we change the thread, the subject is still the same... (for those that might want to look into the "old" story: http://www.sqlservercentral.com/Forums/FindPost732338.aspx

With one BIG difference: your sample data made it easy to work with!

SELECT

DATEPART(YEAR,I.EffectiveDate) AS Production_Year,

DATENAME(MONTH,I.EffectiveDate) AS Production_Month,

COUNT (DISTINCT P.ProductId) AS Units_Built,

AVG(I.MaterialValue) AS AvgMaterialCost,

AVG(I.LabourValue) AS AvgLabourCost,

AVG(I.TotalValue) AS AverageTotalCost

FROM inventory I

INNER JOIN ManufacturingOrders M ON I.ManufacturingOrder = M.ManufacturingOrder

AND I.Product = M.Product

INNER JOIN Products P ON M.Product = P.Product

GROUP BY DATEPART(YEAR,I.EffectiveDate),DATEname(MONTH,I.EffectiveDate), CONVERT(CHAR(6),I.EffectiveDate,112)

ORDER BY CONVERT(CHAR(6),I.EffectiveDate,112)

/* Result set

Production_Year Production_Month Units_Built AvgMaterialCost AvgLabourCost AverageTotalCost

2009 January 1 70.00 0.00 70.00

2009 February 2 5856.22 250.66 7851.94

2009 March 2 13904.795 594.73 16984.625

2009 May 1 13902.15 587.2828 16055.2271

2009 June 1 13916.05 588.14 16400.27

*/

Edit: Note: I don't know how you'd count the Units_Built so it might be required to change that part...

Lutz

Lutz tested on system this end and the output looks good. One point, they want to be able to identify the actual product they have built. So a record would be returned for each Product. Using your example:

ProductionYear--------ProductionMonth------ProductId--------UnitsBuilt....etc....

2009------------------------Jan--------------125-AAA-5---------50----------

2009------------------------Jan--------------130-BBB-5---------20----------

2009------------------------Feb--------------125-AAA-5---------10----------

2009------------------------Feb--------------130-BBB-5---------70----------

I suppose I could add a Parameter; WHERE p.ProductId = @ProductId

Many Thanks,

Phil.

PS: Script now attached to this thread 🙂

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• You just need to add the ProductID to the SELECT statement as well as the GROUP BY clause:

`SELECT`

`DATEPART(YEAR,I.EffectiveDate) AS Production_Year,`

`DATENAME(MONTH,I.EffectiveDate) AS Production_Month,`

`P.ProductId,`

`COUNT (P.ProductId) AS Units_Built,`

`AVG(I.MaterialValue) AS AvgMaterialCost,`

`AVG(I.LabourValue) AS AvgLabourCost,`

`AVG(I.TotalValue) AS AverageTotalCost`

`FROM inventory I`

`INNER JOIN #ManufacturingOrders M ON I.ManufacturingOrder = M.ManufacturingOrder`

`AND I.Product = M.Product`

`INNER JOIN Products P ON M.Product = P.Product`

`GROUP BY DATEPART(YEAR,I.EffectiveDate),DATEname(MONTH,I.EffectiveDate), CONVERT(CHAR(6),I.EffectiveDate,112),P.ProductId`

`ORDER BY CONVERT(CHAR(6),I.EffectiveDate,112)`

Normally, date formatting is done at frontend side. Instead of using the DATEPART and DATENAME functions I normally just would provide the CHAR6-Format and the frontend would do the split into year and month....

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Lutz many thanks for all your assistance, I will run against a larger data set and post my results.

Once again, many thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

• You're welcome!

Glad it finally worked out for you.

As you could see: the more easy it is to work on your code the easier (and faster) you'll get an answer.

Lutz
A pessimist is an optimist with experience.

How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Lutz the values returned in production look good.

Great work,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Viewing 13 posts - 1 through 13 (of 13 total)