on 12-30-2009 12:25 AM
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
need report to show
item A----regular item
24.00
item AB--master
220.00
item AB1component(regular item)-129.00--
125.00
item AB2component(regular item)-100.00--
95.00
item AC--master
250.00
item AC1component(regular item)-150.00--
150.00
item AC2component(regular item)-100.00--
100.00
PLEASE HELP.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
need report to show
item A----regular item
24.00
item AB--master
125.00
item AB--master
95.00
item AB1component
125.00
item AB1regular item
125.00
item AB2component
95.00
item AB2regular item
95.00
item AC--master
150.00
item AC--master
100.00
item AC1component
150.00
item AC1regular item
150.00
item AC2component
100.00
item AC2regular item
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
7 | |
7 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.