June 10, 2009 at 8:28 am
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
June 10, 2009 at 1:25 pm
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
OverheadValue
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...
So please take the time and help us help you.
Edit: Fields in pseudo tables added...
June 10, 2009 at 2:09 pm
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
June 10, 2009 at 2:32 pm
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
OverheadValue1731,125
TotalValue6100,976
Is this assumption correct?
Edit: wording modified.
June 10, 2009 at 3:22 pm
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
June 10, 2009 at 4:11 pm
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
June 10, 2009 at 4:55 pm
Did you actually try your sample script before posting?
Reason for asking:
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.
June 10, 2009 at 6:38 pm
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.
June 11, 2009 at 9:48 am
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!
Would the following help you?
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
June 11, 2009 at 12:20 pm
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....
June 11, 2009 at 12:33 pm
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
June 11, 2009 at 1:02 pm
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.
June 12, 2009 at 4:32 am
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply