Last post May 03, 2012 10:55 PM by Decker Dong - MSFT
May 02, 2012 05:02 PM|adrian_az|LINK
Im in need of a bit of help since my team lead is out. Im trying to figure out how to manipulate my results set that is returned from my initial query. Which is below here. How do I go about looping through and identifying where the same part number would
have multiple entries, and if so then covert down to the smallest unit of measure? Im assuming a loop but I have not yet written one of those yet in SQL.
l.partNum AS [Part Number]
, l.pricePerUOM AS [Price Per UOM]
, l.uom AS UOM
,SUM(CONVERT(NUMERIC(18,12), l.totalCostPerWS/l.pricePerUOM)) AS [Quantity/WS]
FROM tbl_step s
JOIN tbl_line l ON s.stepId = l.stepId
WHERE s.revisionId = 150
AND ISNUMERIC(l.totalCostPerWS) > 0
AND ISNUMERIC(l.pricePerUOM) > 0
GROUP BY l.partNum, l.pricePerUOM, l.uom
ORDER BY l.partNum,l.uom
My Psuedo Code:
--2. Loop through the data already established(temp table?), and identify quart, milliliter, liter, and gallon.
--If the same part number has multiple entries (1 milliliter, 1 liter)
--(Quantity/WS) * 1000 (result A) Converting from liter to milliliter.
--(Quantity/WS)for the same part number but milliliters(result B)added to the result from above. (result A + result B)= result C
--(result C) * Price Per UOM = Final Answer
--If there is only one entry, for either liter or milliliter then leave it alone.
--going from liter to milliliter * 1000
--going from gallon to liter * 3.7854118
--Declare 3 variables? (resultA, resultB, resultC?)
Any suggestions would be greatly appreciated.
May 03, 2012 10:55 PM|Decker Dong - MSFT|LINK
I think the problem seems to be a little complicated……So you can use SQL View to create a temporary datatable and then use SqlCommand to fetch all the things out，and then use foreach to loop the DataTable.Rows and then use if statements to do what