[RESOLVED] Simple Math Problem

Rampage-Obidos

I hope this community can help me, I have been having trouble with an SQL query.

I have taught myself everything I know, so please excuse what is probably a very badly written bit of code.

SELECT 
CASE WHEN [StockQTY] = 0 OR [TotalStock] = 0 THEN 0 ELSE CAST(([StockQTY]/[TotalStock])*'100' AS DECIMAL(10,0)) END AS [Percentage],
[Season],[PLU],[ProductDescription],[SupplierCode],[StockQTY],[StockQTY1],[StockQTY2],[StockQTY3],[TotalStock],[StockRoomLocation],[StockRoomLocation2],[SalePrice1],[StockTakeCount1],[TempStockRoomLocation3],[StockTakeCount2],[StockTakeCount3],[TempStockRoomLocation2] 
FROM [PLU] 
INNER JOIN [Stock] ON PLU.PLU = Stock.StockID 
INNER JOIN GroupStock ON PLU.PLU = GroupStock.StockID 
WHERE '$ShowPercent'<'Percentage' AND [TotalStock]>'1' AND [StoreID]='004' AND [DeptID] LIKE '$ShowDepartment'$SeasonShow$NotLikes
GROUP BY [PLU],[Season],[ProductDescription],[SupplierCode],[StockQTY],[StockQTY1],[StockQTY2],[StockQTY3],[TotalStock],[StockRoomLocation],[StockRoomLocation2],[SalePrice1],[StockTakeCount1],[TempStockRoomLocation3],[StockTakeCount2],[StockTakeCount3],[TempStockRoomLocation2] 
ORDER BY [TempStockRoomLocation2] ASC, [StockRoomLocation] ASC, [SupplierCode] ASC";

The result I get when I run this query with values.

CASE WHEN [1] = 0 OR [2] = 0 THEN 0 ELSE CAST(([1]/[2])*'100' AS DECIMAL(10,0)) END AS [0]

The END AS value should be 50 (being 50%) but yet it returns as 0.

If anyone knows how I can get the SQL query to return the correct value please let me know.

Thanks

Lars_Berg

You will get an integer division and 1/2 equals 0

else 100.0*StockQTY/TotalStock end as Percentage,

WHERE '$ShowPercent'<'Percentage'

This will not be a reference to the result of the case expression. If you want to put some restriction on that result, you can either repeat the complete expression or put it into a derived table. As it is witten now, your code will only make a string comparison.

Why do you have a group by clause?

Rampage-Obidos

I don't really understand the group by clause yet, I didn't know if that was why I wasn't getting the results when I was typing up the query, so I added it.

As I said, I have taught myself everything I know, which in the grand scale isn't much, but I have usually been able to make all the code I write do what I need it to.

The Percentage now shows correctly, thank you very much. I must have tried everything bar that solution.

I am afraid you have lost me with the case expression part. I have noticed that the results seem to ignore that comparison, but I am not sure what I need to do to make that comparison.