April 20, 2011 at 10:20 am
I've created a view in SQL 2008 with the sql code below.
The problem column is i.QTY
I need the per row value not an aggregated value.
I cannot use it in the group by clause because it breaks the grouping by commodity_code.
Which is all that is needed.
SELECT
TOP (100) PERCENT p.COMMODITY_CODE,
p.ID, SUM(i.QTY) AS [90 Day Usage],
SUM(p.UNIT_MATERIAL_COST + p.UNIT_LABOR_COST + p.UNIT_BURDEN_COST + p.UNIT_SERVICE_COST) AS [Unit Cost],
SUM(p.UNIT_MATERIAL_COST + p.UNIT_LABOR_COST + p.UNIT_BURDEN_COST + p.UNIT_SERVICE_COST) * i.QTYAS [90 Day Value],
SUM(p.QTY_ON_HAND) * SUM(p.UNIT_MATERIAL_COST + p.UNIT_LABOR_COST + p.UNIT_BURDEN_COST + p.UNIT_SERVICE_COST) AS [Inventory Value]
FROM
dbo.INVENTORY_TRANS AS i INNER JOIN dbo.PART AS p ON i.PART_ID = p.ID
WHERE
(i.TRANSACTION_DATE > DATEADD(day, - 90, GETDATE())) AND (i.TYPE = 'O') AND (i.CLASS IN ('A', 'I')) AND (i.WAREHOUSE_ID = '01')
GROUP BY p.COMMODITY_CODE, p.ID, i.QTY
ORDER BY p.COMMODITY_CODE
The result illustrates the problem:
Commodity Code Part ID 90 Day Usage Unit Cost 90 Day value Inventory Value
CM 1010 PTS 0500000414900.00000.98000098.000000292249.720000
CM 1010 PTS 05000004150.00000.1000001.0000003043.000000
CM 1010 PTS 0500000418.00000.1600000.1600007790.080000
I only want one value per part ID and the 90 day value causes a grouping.
Help?
tia
April 20, 2011 at 11:47 am
I'm a little bit confused...
You need "the per row value not an aggregated value", but you're using SUM(i.QTY).
Additionally, i.QTY is included in the GROUP BY clause.
Would it help if you'd just remove the i.QTY from the GROUP BY to get your expected result?
If not, you might want to rephrase what you're looking for. The best way would be to pos some sample data and expected result.
April 20, 2011 at 12:19 pm
Sorry,
The SUM(i.QTY) is not the problem.
It is the i.QTY used in the 90 Day value calculation
SUM(p.UNIT_MATERIAL_COST + p.UNIT_LABOR_COST + p.UNIT_BURDEN_COST + p.UNIT_SERVICE_COST) * i.QTY AS [90 Day Value]
What would be great is an aggregate that has no impact.
Oh man I just thought of something.....
So simple.....use .... sum(i.QTY+0) which does the trick.
Thanks 🙂
April 20, 2011 at 12:23 pm
Well that did not do the trick.
I can't do a sum on the i.QTY
April 20, 2011 at 2:02 pm
May I ask for some sample data and expected result?
It's still not clear what you're looking for.
April 20, 2011 at 2:37 pm
Lutz,
My whole problem description was wrong from the beginning.
The commodity code needed to be trimmed.
Users apparently entered spaces at the end of the code when entering transactions.
It was the commodity code causing the group breaks not the calculation as I thought and posted.
Thank you for your time and attention.
Its nice to know someone is willing to help and I sincerely appreciate that.
Thanks again,
Todd
April 20, 2011 at 3:26 pm
Glad it finally worked out for you. Thank you for the feedback!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply