Return Average build cost based on last 5 Orders

Question

Post reply

Return Average build cost based on last 5 Orders

2Tall

SSChampion

Points: 14644
More actions
December 30, 2008 at 5:26 am

#128867

Hi. Where to begin....I need to arrive at an average build cost for a Product taking into account all associated costs.
I have attached the table definition with some sample data.
In the sample data provided.
Cost is recorded against an ‘Element’, an ELEMENT can be 100,200,300,500-519,520-539. 8 Elements can be held per BKT.
In the sample data provided if more than 8 Elements are booked against an Order then these are written to BKT2. The OrderId is then listed against BKT2 for these additional Elements.
No more than 2 BKTs are used per Order.
Below are a list of values that can appear as Elements in BKT1 or BKT2
100 = MAN
200 = PUR
300 = SUB
500-519 = LB
520-539 = OH
Example:
Ordered: SO00001263 = 11 Elements (E)
Line 1: E1=100 E2=200 E3=300 E4=500 E5=501 E6=502 E7=503 E8=520
Line 2: E1=521 E2=522 E3=533
In the sample data a COST is returned for each individual Element with a TotalCost column to record the Total Build Cost for the ProductId (X-556937-P).
Requirement 1:
SUM Individual Element Costs per Order to give a single cost for that ELEMENT group
Example taken from attached sample data:
OrderId: SO00001263
The 9th COST is held in ELEMENT1, the 10th in ELEMENT 2 and the 11th in ELEMENT3 on line 2
i.e. E9=521 Cost = £680 E10=522 Cost=£21.43 E11=533 Cost=£1883.57
Output as:
ProductId = X-556937-P
SUM of 100 = £6.64 AS MAN
SUM of 200 = £18167.13AS PUR
SUM of 300 = £718.73 AS SUB
SUM of 500-519 = £1718.13AS LB
SUM of 520-539 = £7146.40 AS OH
TotalCost = £27757.03
Requirement 2:
Return Average Cost for ProductId based on last 5 Orders on which it has appeared.
OrderId is in Ascending Order within the sample data so the highest number is the last Order on which the Product has appeared.
The output would have Column(s)
ProductCount (number of times product has been ordered)
ProductId
MAN = SUM of ELEMENTS 100
PUR = SUM of ELEMENTS 200
SUB = SUM of ELEMENTS 300
LB = SUM of ELEMENTS 500 to 519
OH = SUM of ELEMENTS 520 to 539
AVGTotalCost = Average Total Build Cost
I appreciate the requirement is quite complex, if you feel you can assist but require additional information then do not hesitate to ask.
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

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply

2Tall SSChampion Points: 14644 More actions · Answer 1

I have the following basic query that gives an average build cost without the summation of the individual elements.

SELECT DISTINCT COUNT(ProductId) AS ProductCount,ProductId, AVG(TotalCost) AS 'AVGBuildCost'

FROM dbo.Cost

WHERE TotalCost >'0'

GROUP BY ProductId

ORDER BY ProductId

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

2Tall SSChampion Points: 14644 More actions · Answer 2

I thought I would break the problem down and now have the following:

SELECT DISTINCT ProductId,OrderId,

ELEMENT1=

CASE

WHEN ELEMENT1 = 100 THEN 'MAN'

WHEN ELEMENT1 = 200 THEN 'PUR'

WHEN ELEMENT1 = 300 THEN 'SUB'

WHEN ELEMENT1 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT1 BETWEEN 520 AND 539 THEN 'OH'

END,

COST1,

ELEMENT2=

CASE

WHEN ELEMENT2 = 100 THEN 'MAN'

WHEN ELEMENT2 = 200 THEN 'PUR'

WHEN ELEMENT2 = 300 THEN 'SUB'

WHEN ELEMENT2 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT2 BETWEEN 520 AND 539 THEN 'OH'

END,

COST2,

ELEMENT3=

CASE

WHEN ELEMENT3 = 100 THEN 'MAN'

WHEN ELEMENT3 = 200 THEN 'PUR'

WHEN ELEMENT3 = 300 THEN 'SUB'

WHEN ELEMENT3 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT3 BETWEEN 520 AND 539 THEN 'OH'

END,

COST3,

ELEMENT4=

CASE

WHEN ELEMENT4 = 100 THEN 'MAN'

WHEN ELEMENT4 = 200 THEN 'PUR'

WHEN ELEMENT4 = 300 THEN 'SUB'

WHEN ELEMENT4 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT4 BETWEEN 520 AND 539 THEN 'OH'

END,

COST4,

ELEMENT5=

CASE

WHEN ELEMENT5 = 100 THEN 'MAN'

WHEN ELEMENT5 = 200 THEN 'PUR'

WHEN ELEMENT5 = 300 THEN 'SUB'

WHEN ELEMENT5 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT5 BETWEEN 520 AND 539 THEN 'OH'

END,

COST5,

ELEMENT6=

CASE

WHEN ELEMENT6 = 100 THEN 'MAN'

WHEN ELEMENT6 = 200 THEN 'PUR'

WHEN ELEMENT6 = 300 THEN 'SUB'

WHEN ELEMENT6 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT6 BETWEEN 520 AND 539 THEN 'OH'

END,

COST6,

ELEMENT7=

CASE

WHEN ELEMENT7 = 100 THEN 'MAN'

WHEN ELEMENT7 = 200 THEN 'PUR'

WHEN ELEMENT7 = 300 THEN 'SUB'

WHEN ELEMENT7 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT7 BETWEEN 520 AND 539 THEN 'OH'

END,

COST7,

ELEMENT8=

CASE

WHEN ELEMENT8 = 100 THEN 'MAN'

WHEN ELEMENT8 = 200 THEN 'PUR'

WHEN ELEMENT8 = 300 THEN 'SUB'

WHEN ELEMENT8 BETWEEN 500 AND 519 THEN 'LB'

WHEN ELEMENT8 BETWEEN 520 AND 539 THEN 'OH'

END,

COST8,

TotalCost

FROM Cost

GROUP BY ProductId,OrderId,ELEMENT1,COST1,ELEMENT2,COST2,ELEMENT3,COST3,ELEMENT4,COST4,

ELEMENT5,COST5,ELEMENT6,COST6,ELEMENT7,COST7,ELEMENT8,COST8,TotalCost

ORDER BY ProductId

Not quite what I am after but a start. Not sure how to Group the Elements + Costs for the Elements.

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

null-512060 SSChasing Mays Points: 613 More actions · Answer 3

Not the most elegant solution, but maybe this will help:

SELECT ProductId

,COUNT(1) ProductCount

,SUM(MAN) MAN

,SUM(PUR) PUR

,SUM(SUB) SUB

,SUM(LB) LB

,SUM(OH) OH

,AVG(TotalCost) AvgTotalCost

FROM (SELECT DENSE_RANK() over (partition by ProductId order by OrderID desc) as OrderRank

,ProductId

,OrderID

,MAN

,PUR

,SUB

,LB

,OH

,TotalCost

FROM

(SELECT ProductId

,OrderID

,SUM( CASE WHEN ELEMENT1 = 100 THEN COST1 ELSE 0 END

+ CASE WHEN ELEMENT2 = 100 THEN COST2 ELSE 0 END

+ CASE WHEN ELEMENT3 = 100 THEN COST3 ELSE 0 END

+ CASE WHEN ELEMENT4 = 100 THEN COST4 ELSE 0 END

+ CASE WHEN ELEMENT5 = 100 THEN COST5 ELSE 0 END

+ CASE WHEN ELEMENT6 = 100 THEN COST6 ELSE 0 END

+ CASE WHEN ELEMENT7 = 100 THEN COST7 ELSE 0 END

+ CASE WHEN ELEMENT8 = 100 THEN COST8 ELSE 0 END) 'MAN'

, SUM(CASE WHEN ELEMENT1 = 200 THEN COST1 ELSE 0 END

+ CASE WHEN ELEMENT2 = 200 THEN COST2 ELSE 0 END

+ CASE WHEN ELEMENT3 = 200 THEN COST3 ELSE 0 END

+ CASE WHEN ELEMENT4 = 200 THEN COST4 ELSE 0 END

+ CASE WHEN ELEMENT5 = 200 THEN COST5 ELSE 0 END

+ CASE WHEN ELEMENT6 = 200 THEN COST6 ELSE 0 END

+ CASE WHEN ELEMENT7 = 200 THEN COST7 ELSE 0 END

+ CASE WHEN ELEMENT8 = 200 THEN COST8 ELSE 0 END) 'PUR'

, SUM(CASE WHEN ELEMENT1 = 300 THEN COST1 ELSE 0 END

+ CASE WHEN ELEMENT2 = 300 THEN COST2 ELSE 0 END

+ CASE WHEN ELEMENT3 = 300 THEN COST3 ELSE 0 END

+ CASE WHEN ELEMENT4 = 300 THEN COST4 ELSE 0 END

+ CASE WHEN ELEMENT5 = 300 THEN COST5 ELSE 0 END

+ CASE WHEN ELEMENT6 = 300 THEN COST6 ELSE 0 END

+ CASE WHEN ELEMENT7 = 300 THEN COST7 ELSE 0 END

+ CASE WHEN ELEMENT8 = 300 THEN COST8 ELSE 0 END) 'SUB'

, SUM(CASE WHEN ELEMENT1 BETWEEN 500 AND 519 THEN COST1 ELSE 0 END

+ CASE WHEN ELEMENT2 BETWEEN 500 AND 519 THEN COST2 ELSE 0 END

+ CASE WHEN ELEMENT3 BETWEEN 500 AND 519 THEN COST3 ELSE 0 END

+ CASE WHEN ELEMENT4 BETWEEN 500 AND 519 THEN COST4 ELSE 0 END

+ CASE WHEN ELEMENT5 BETWEEN 500 AND 519 THEN COST5 ELSE 0 END

+ CASE WHEN ELEMENT6 BETWEEN 500 AND 519 THEN COST6 ELSE 0 END

+ CASE WHEN ELEMENT7 BETWEEN 500 AND 519 THEN COST7 ELSE 0 END

+ CASE WHEN ELEMENT8 BETWEEN 500 AND 519 THEN COST8 ELSE 0 END) 'LB'

, SUM(CASE WHEN ELEMENT1 BETWEEN 520 AND 539 THEN COST1 ELSE 0 END

+ CASE WHEN ELEMENT2 BETWEEN 520 AND 539 THEN COST2 ELSE 0 END

+ CASE WHEN ELEMENT3 BETWEEN 520 AND 539 THEN COST3 ELSE 0 END

+ CASE WHEN ELEMENT4 BETWEEN 520 AND 539 THEN COST4 ELSE 0 END

+ CASE WHEN ELEMENT5 BETWEEN 520 AND 539 THEN COST5 ELSE 0 END

+ CASE WHEN ELEMENT6 BETWEEN 520 AND 539 THEN COST6 ELSE 0 END

+ CASE WHEN ELEMENT7 BETWEEN 520 AND 539 THEN COST7 ELSE 0 END

+ CASE WHEN ELEMENT8 BETWEEN 520 AND 539 THEN COST8 ELSE 0 END) 'OH'

,SUM(TotalCost) TotalCost

FROM [dbo].[Cost]

GROUP BY ProductId

,OrderID) t1) t2

WHERE OrderRank <= 5

GROUP BY ProductId

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks very much for your efforts, great work.

I amended to return average for the other costs rather than SUM.

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

2Tall SSChampion Points: 14644 More actions · Answer 5

Hello again. I have done a sanity check on the output when averaging the cost and the figures do not quite add up.

I averaged the costs of the last 5 orders from the sample data for ProductId 'X-556937-P'

SO00001775

SO00001776

SO00001777

SO00001778

SO00001779

If I run the query with AVG in place of SUM the AVGTotalCost = £24262.324

If I average using the Average function in excel the AVGTotalCost = £20219.44

If I manually add the values then divide by 5 excel agrees with the SQL query.

The excel Average Function request Number 1 and Number 2. For Number 1 I select the value range, for Number 2 I select 5.

Example for a Cost1 Column:

7.00

6.50

364.69

7+7+6.5+6.5+364.69 / 5 = £78.338

Excel Average Function = £66.12

Any thoughts on what might be causing the discrepancy?

Finally as you can see from the data above £364.69 skews the average. Is there a way I can ignore the highest and lowest values to obtain a more realistic average?

Many Thanks,

Phil.

UPDATE: I amended excel function to: =AVERAGE(RANGE) which returned the correct value. Previously I used =AVERAGE(RANGE,5) where 5 was the count of numbers (or so I thought!).

-------------------------------------------------------------------------------------
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

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Average in Excel is Number1, Number2, ... You were giving it a range and then an extra 5, which was bringing down your average.

(7+7+6.5+6.5+364.69+5) / 6 = 66.12

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

2Tall SSChampion Points: 14644 More actions · Answer 7

Thanks Seth.

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

2Tall SSChampion Points: 14644 More actions · Answer 8

Hi. I have joined code provided by Null to an additional table as follows:

FROM Cost

INNER JOIN Products ON dbo.Cost.ProductId = dbo.Products.CrossReference

GROUP BY Cost.ProductId,OrderId) t1) t2

WHERE OrderRank <=5

GROUP BY ProductId

Referring back to the code posted by Null I want to output additional column 'Type' from dbo.Products but having tried a number of variations have yet to succeed. Where do I add Products.Type within the query in order for it to appear as a column in the query output?

Any pointers?

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

DonaldW SSCarpal Tunnel Points: 4525 More actions · Answer 9

Philip Horan (12/30/2008)
Hi. Where to begin....I need to arrive at an average build cost for a Product taking into account all associated costs.
...

Hi, Phil. You may have already gotten what you need but I'd like to toss in this link. http://www.sqlservercentral.com/articles/Iterative/64699/. It was posted about a month ago and might help as an alternate.

Cheers,

Don

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Hi Phil

For future reference, you will find this exercise a heck of a lot easier if you work with normalised data...

[font="Courier New"]SELECT d.ProductId, d.OrderId, d.ElementNum, x.ElementName, d.CostValue

FROM (SELECT ProductId, OrderId,

CAST(number+((BKT-1)*8) AS INT) AS ElementNum,

CASE number WHEN 1 THEN ELEMENT1 WHEN 2 THEN ELEMENT2 WHEN 3 THEN ELEMENT3 WHEN 4 THEN ELEMENT4

WHEN 5 THEN ELEMENT5 WHEN 6 THEN ELEMENT6 WHEN 7 THEN ELEMENT7 WHEN 8 THEN ELEMENT8 END AS ElementValue,

CASE number WHEN 1 THEN COST1 WHEN 2 THEN COST2 WHEN 3 THEN COST3 WHEN 4 THEN COST4

WHEN 5 THEN COST5 WHEN 6 THEN COST6 WHEN 7 THEN COST7 WHEN 8 THEN COST8 END AS CostValue

FROM #Cost c,

Numbers n

WHERE c.OrderId = 'SO00001263'

AND n.number < 9

) d

LEFT JOIN (SELECT number,

CASE number WHEN 100 THEN 'MAN' WHEN 200 THEN 'PUR' WHEN 300 THEN 'SUB' ELSE

CASE WHEN number < 520 THEN 'LB' ELSE 'OH' END

END AS ElementName

FROM Numbers WHERE number IN (100, 200, 300) OR number BETWEEN 500 AND 539

) x ON x.number = d.ElementValue

WHERE d.ElementValue > 0 [/font]

Output:

ProductId OrderId ElementNum ElementName CostValue

------------ ------------ ----------- ----------- ---------------------

X-556937-P SO00001263 1 MAN 6.6400

X-556937-P SO00001263 2 PUR 18167.1300

X-556937-P SO00001263 3 SUB 718.7300

X-556937-P SO00001263 4 LB 1032.4800

X-556937-P SO00001263 5 LB 136.0000

X-556937-P SO00001263 6 LB 4.3900

X-556937-P SO00001263 7 LB 545.2600

X-556937-P SO00001263 8 OH 4561.4000

X-556937-P SO00001263 9 OH 680.0000

X-556937-P SO00001263 10 OH 21.4300

X-556937-P SO00001263 11 OH 1883.5700

The entire query can be used as the input for aggregation:

[font="Courier New"]SELECT ProductId, OrderId, ElementName, SUM(CostValue)

FROM (SELECT d.ProductId, d.OrderId, d.ElementNum, x.ElementName, d.CostValue

FROM (SELECT ProductId, OrderId,

CAST(number+((BKT-1)*8) AS INT) AS ElementNum,

CASE number WHEN 1 THEN ELEMENT1 WHEN 2 THEN ELEMENT2 WHEN 3 THEN ELEMENT3 WHEN 4 THEN ELEMENT4

WHEN 5 THEN ELEMENT5 WHEN 6 THEN ELEMENT6 WHEN 7 THEN ELEMENT7 WHEN 8 THEN ELEMENT8 END AS ElementValue,

CASE number WHEN 1 THEN COST1 WHEN 2 THEN COST2 WHEN 3 THEN COST3 WHEN 4 THEN COST4

WHEN 5 THEN COST5 WHEN 6 THEN COST6 WHEN 7 THEN COST7 WHEN 8 THEN COST8 END AS CostValue

FROM #Cost c,

Numbers n

WHERE c.OrderId = 'SO00001263'

AND n.number < 9

) d

LEFT JOIN (SELECT number,

CASE number WHEN 100 THEN 'MAN' WHEN 200 THEN 'PUR' WHEN 300 THEN 'SUB' ELSE

CASE WHEN number < 520 THEN 'LB' ELSE 'OH' END

END AS ElementName

FROM Numbers WHERE number IN (100, 200, 300) OR number BETWEEN 500 AND 539

) x ON x.number = d.ElementValue

WHERE d.ElementValue > 0

) norm

GROUP BY ProductId, OrderId, ElementName[/font]

Output:

ProductId OrderId ElementName Total Cost

X-556937-PSO00001263LB 1718.1300

X-556937-PSO00001263MAN 6.6400

X-556937-PSO00001263OH 7146.4000

X-556937-PSO00001263PUR 18167.1300

X-556937-PSO00001263SUB 718.7300

Of course, using SS2K5 you could always set this up as a CTE.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

null-512060 SSChasing Mays Points: 613 More actions · Answer 11

If Product can have only 1 Type:

SELECT ProductId

,[Type]

,COUNT(1) ProductCount

,AVG(MAN) MAN

,AVG(PUR) PUR

,AVG(SUB) SUB

,AVG(LB) LB

,AVG(OH) OH

,AVG(TotalCost) AvgTotalCost

FROM (SELECT DENSE_RANK() OVER (PARTITION BY ProductId ORDER BY OrderID DESC) as OrderRank

,ProductId

,[Type]

,OrderID

,MAN

,PUR

,SUB

,LB

,OH

,TotalCost

FROM

(SELECT c.ProductId

,p.Type

,c.OrderID