cancel
Showing results for 
Search instead for 
Did you mean: 

CALCULATE COST FOR ITEM

Former Member
0 Kudos

how do i calculate the cost of an item on a report

in my database i have boms. master and components. and regular items not bom

the regular items have a reorder cost, avgcost,. the master item does not have any of those. how do i calculate in a report to sum the avgcost of the components for the master item. if there is no avgcost then sum the reorder cost.

example

itemcode-itemtype


reorder cost
avgcost
--


need report to show

item A----regular item


25.00
24.00
--


24.00

item AB--master


0.00
0.00
--


220.00

item AB1component(regular item)-129.00--


125.00--


125.00

item AB2component(regular item)-100.00--


95.00--


95.00

item AC--master


0.00
0.00
--


250.00

item AC1component(regular item)-150.00--


0.00--


150.00

item AC2component(regular item)-100.00--


0.00--


100.00

PLEASE HELP.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Try this one:

SELECT T0.ItemCode, 'Regular Item' AS 'Item Type', Case T0.AvgPrice WHEN 0 THEN T0.LastPurPrc ELSE T0.AvgPrice END As 'Item Cost'

FROM dbo.OITM T0

WHERE T0.ItemCode NOT in (SELECT Code FROM OITT)

UNION ALL

SELECT T0.ItemCode, 'Master' AS 'Item Type', Case SUM(T0.AvgPrice) WHEN 0 THEN SUM(T0.LastPurPrc) ELSE SUM(T0.AvgPrice) END As 'Item Cost'

FROM dbo.OITM T0

INNER JOIN ITT1 T1 ON T0.ItemCode = T1.Father

GROUP BY T0.ItemCode

UNION ALL

SELECT Distinct T0.ItemCode, 'Componet Item' AS 'Item Type', Case T0.AvgPrice WHEN 0 THEN T0.LastPurPrc ELSE T0.AvgPrice END As 'Item Cost'

FROM dbo.OITM T0

INNER JOIN ITT1 T1 ON T0.ItemCode = T1.Code

ORDER BY T0.ItemCode

Former Member
0 Kudos

Thank you for the example Gordon. Very helpful

i created this query just changed the lastpurprc to be the pricelist 9 in opln as my reorder cost.

SELECT T0.ItemCode, 'Regular Item' AS 'Item Type',

CASE T0.AvgPrice WHEN 0 THEN T3.PRICE ELSE T0.AvgPrice END AS 'Item Cost'

FROM OITM AS T0 LEFT OUTER JOIN

ITM1 AS T3 ON T0.ItemCode = T3.ItemCode

WHERE (T3.PriceList = 9)

UNION ALL

SELECT T0.ItemCode, 'Master' AS 'Item Type',

CASE SUM(T0.AvgPrice) WHEN 0 THEN SUM(T3.PRICE) ELSE SUM(T0.AvgPrice) END AS 'Item Cost'

FROM OITM AS T0 INNER JOIN

ITT1 AS T1 ON T0.ItemCode = T1.Father INNER JOIN

ITM1 AS T3 ON T1.Code = T3.ItemCode

WHERE (T3.PriceList = 9)

GROUP BY T0.ItemCode, T3.Price, T3.PriceList, T1.Code

UNION ALL

SELECT DISTINCT

T0.ItemCode, 'Component' AS 'Item Type',

CASE T0.AvgPrice WHEN 0 THEN T3.PRICE ELSE T0.AvgPrice END AS 'Item Cost'

FROM OITM AS T0 INNER JOIN

ITT1 AS T1 ON T0.ItemCode = T1.Code INNER JOIN

ITM1 AS T3 ON T0.ItemCode = T3.ItemCode

WHERE (T3.PriceList = 9)

ORDER BY T0.ItemCode

however my query gives me this result.

itemcode-itemtype


reorder cost
avgcost
--


need report to show

item A----regular item


25.00
24.00
--


24.00

item AB--master


0.00
0.00
--


125.00

item AB--master


0.00
0.00
--


95.00

item AB1component


129.00
125.00
--


125.00

item AB1regular item


129.00
125.00
--


125.00

item AB2component


100.00
95.00
--


95.00

item AB2regular item


100.00
95.00
--


95.00

item AC--master


0.00
0.00
--


150.00

item AC--master


0.00
0.00
--


100.00

item AC1component


150.00
0.00
--


150.00

item AC1regular item


150.00
0.00
--


150.00

item AC2component


100.00
0.00
--


100.00

item AC2regular item


100.00
0.00
--


100.00

if you notice the master is repeated but with cost of the components or regular item instead of the sum.

also i only want to see only the component not to show as a regular item again.

can u check my query.

thank you in advance.

Edited by: RUSS on Dec 30, 2009 12:41 PM

Former Member
0 Kudos

Updated based on your case:


SELECT     T0.ItemCode, 'Regular Item' AS 'Item Type',
CASE T0.AvgPrice WHEN 0 THEN T3.PRICE ELSE T0.AvgPrice END AS 'Item Cost'
FROM         OITM T0 LEFT OUTER JOIN
                  ITM1 T3 ON T0.ItemCode = T3.ItemCode AND T3.PriceList = 9
WHERE     T0.ItemCode NOT in (SELECT Code FROM OITT)

UNION ALL

SELECT     T0.ItemCode, 'Master' AS 'Item Type',
CASE SUM(T2.AvgPrice) WHEN 0 THEN SUM(T3.PRICE) ELSE SUM(T2.AvgPrice) END AS 'Item Cost'
FROM         OITM AS T0 INNER JOIN
                     ITT1 AS T1 ON T0.ItemCode = T1.Father INNER JOIN
                     OITM AS T2 ON T1.Code = T2.ItemCode  INNER JOIN
                     ITM1 AS T3 ON T1.Code = T3.ItemCode AND T3.PriceList = 9
GROUP BY T0.ItemCode

UNION ALL

SELECT DISTINCT
                     T0.ItemCode, 'Component' AS 'Item Type',
CASE T0.AvgPrice WHEN 0 THEN T3.PRICE ELSE T0.AvgPrice END AS 'Item Cost'
FROM         OITM AS T0 INNER JOIN
                     ITT1 AS T1 ON T0.ItemCode = T1.Code INNER JOIN
                     ITM1 AS T3 ON T1.Code = T3.ItemCode AND T3.PriceList = 9
ORDER BY T0.ItemCode

Answers (1)

Answers (1)

Former Member
0 Kudos

You may use Union ALL to combine two queries' results to create this report. However the report may not be that easy to sort unless you have exact itemcodes like your example.

Thanks,

Gordon

Former Member
0 Kudos

do u mind posting an example based on my example.