Return Average build cost based on last 5 Orders

Question

Return Average build cost based on last 5 Orders

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

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

2Tall SSChampion Points: 14632 More actions · Answer 1

Hello again. Testing has thrown up a bit of a problem. Where the OrderId field = ' ' the values are being summed and not averaged?

I have attached more sample data, the query used provided by NULL + a table definition.

From the sample data ProductId '145-002' returns 8 records, 4 of which are zero, ELEMENT 100 = 0.0612 and then ELEMENT 200 =

0.0606, 0.0614,0.058

The attached query when run returns average cost as follows:

ProductId............ProductCount.....MAN.......PUR.....SUB....LB......OH......AvgTotalCost

145-002......................1.............0.0612....0.18....0.00....0.00....0.00...0.2412

This is incorrect. The average PUR cost should be 0.06 and not 18.

I would really appreciate some help on this as my head is spinning.

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

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

Please replace "SUM(CASE...." with "AVG(CASE...."

That should work.

2Tall SSChampion Points: 14632 More actions · Answer 3

Thanks once again NULL.

Firstly apologies for the error in the attached query. The original query was amended to add an INNER JOIN to table Products, this can be commented out (but know need to tell you guys that!).

If I AVG the CASE what affect does the following have in the outer SELECT:

SELECT ProductId

,COUNT(1) ProductCount

,AVG(MAN) MAN

,AVG(PUR) PUR

,AVG(SUB) SUB

,AVG(LB) LB

,AVG(OH) OH

,AVG(TotalCost) AvgTotalCost

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

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

Philip Horan (1/8/2009)
The original query was amended to add an INNER JOIN to table Products, this can be commented out (but know need to tell you guys that!).

While many of us may know it, it never hurts to state something like this for others who may read/learn from this post. Even though 9 out of 10 people don't use the search button before posting (and really, in a dedicated forum like this, half the time it would be really hard to find exactly what you needed unless you had a keyword that wouldn't likely be used elsewhere), I believe google picks up a lot of these, and the same people who won't search on the forum, WILL Google for it before posting =).

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: 14632 More actions · Answer 5

Hi. I amended SUM(CASE to AVG(CASE as suggested. Still not quite there. I have attached the output.

The costs against the ProductId I queried all appear as COST1 and are as follows:

ELEMENT 100 = 0.0612

ELEMENT 200 = 0.0606, 0.0614, 0.058

All other costs are 0.

THE CASE AVG returns:

MAN = 0.0076

PUR = 0.0225

This is appears to be the ELEMENT values / 8 (number of records returned).

I was hoping to return:

MAN = 0.0612

PUR = 0.06

This would be the SUM of the individual ELEMENTS (i.e. 100,200 etc) / ELEMENTS of same type with value > 0

I tried amending the CASE to read:

CASE WHEN ELEMENT1 = 100 AND COST1 > 0 THEN COST1 ELSE 0 END

This had no affect.

Hope that all makes sense.

Please advise if I am barking up the wrong tree.

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

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

yes, you are right, it should be sum(case...

It will calculate the total cost of the element per order and then average cost for the element based on last 5 orders, but for product '145-002' there is only 1 order so the avg is the same as total for the order.

2Tall SSChampion Points: 14632 More actions · Answer 7

Hi NULL. So the average cost is calculated by AVG(CASE of ELEMENTS / NumOfOrders ..emmmm.....

Recent testing has proved my original brief was incorrect to return the costs of the last 5 orders.

The sample data given for '145-002' in fact has no orders. The costs values were transferred to the part.

The costs table holds all associated costs (rolled up) of a product (standard assembly).

If a part has associated costs but does not have an order reference (or only 1 ) then I need to return the average cost per element group against the product. So if 4 costs are booked to Part 145-002 element 200 I need to sum the 4 values then return an average:

SUM ElementCost / NumOfElements

Currently changing the AVG(CASE returns an inaccurate value as it average across the 8 elements.

I am unsure if you can help further, but many thanks for all that you have done.

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

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

Please try:

SELECT ProductId

,COUNT(1) OrderCount

,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/MANCount ELSE AVG(MAN) END MAN

,CASE WHEN COUNT(1) = 1 THEN SUM(PUR)/PURCount ELSE AVG(PUR) END PUR

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

,ProductId

,OrderID

,MAN

,PUR

,MANCount

,PURCount

FROM

(SELECT Cost.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 = 100 AND COST1 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT2 = 100 AND COST2 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT3 = 100 AND COST3 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT4 = 100 AND COST4 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT5 = 100 AND COST5 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT6 = 100 AND COST6 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT7 = 100 AND COST7 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT8 = 100 AND COST8 > 0 THEN 1 ELSE 0 END) 'MANCount'

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

+ CASE WHEN ELEMENT2 = 200 AND COST2 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT3 = 200 AND COST3 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT4 = 200 AND COST4 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT5 = 200 AND COST5 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT6 = 200 AND COST6 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT7 = 200 AND COST7 > 0 THEN 1 ELSE 0 END

+ CASE WHEN ELEMENT8 = 200 AND COST8 > 0 THEN 1 ELSE 0 END) 'PURCount'

FROM Cost

GROUP BY Cost.ProductId,OrderId) t1) t2

WHERE OrderRank <=5

GROUP BY ProductId, MANCount, PURCount

Returns:

ProductIdOrderCountMANPUR

0-58595600Z578.33817173.848

145-00210.06120.06

2Tall SSChampion Points: 14632 More actions · Answer 9

Hi NULL. When I ran against the table I received:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Many thanks for your time and efforts.

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: 14632 More actions · Answer 10

Hi. I resolved the divide by zero by adding:

SELECT ProductId

,COUNT(1) OrderCount

,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/NULLIF(MANCount,0) ELSE AVG(MAN) END MAN

,CASE WHEN COUNT(1) = 1 THEN SUM(PUR)/NULLIF (PURCount,0) ELSE AVG(PUR) END PUR

NULL thanks for your help. I have made amendments to include calculation for SUB,LB & OH.

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: 14632 More actions · Answer 11

Testing has proved my amendments to the query are incorrect as the results are not as expected.

I posted a small sample set of data but have removed as I want to give more detail

I will extract some sample data and post back.

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: 14632 More actions · Answer 12

I have run the following query (small sample of output attached).

SELECT ProductId

,COUNT(1) OrderCount

,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/NULLIF(MANCount,0) ELSE AVG(MAN) END MAN

,CASE WHEN COUNT(1) = 1 THEN SUM(PUR)/NULLIF (PURCount,0) ELSE AVG(PUR) END PUR

,CASE WHEN COUNT(1) = 1 THEN SUM(SUB)/NULLIF (SUBCount,0) ELSE AVG(SUB) END SUB

,CASE WHEN COUNT(1) = 1 THEN SUM(LB)/NULLIF (LBCount,0) ELSE AVG(LB) END LB

,CASE WHEN COUNT(1) = 1 THEN SUM(OH)/NULLIF (OHCount,0) ELSE AVG(OH) END 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

,MANCount

,PURCount

,SUBCount

,LBCount

,OHCount

,TotalCost

FROM

(SELECT Cost.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'