I’m working on a report to calculate the physical inventory count accuracy.
I’m able to calculate the total number of lines counted, and how many of those lines had variances, but I would like to display the accuracy as a % value. This requires me to do some calculations on a single line, and I can’t quite figure out how to achieve that.
I have the following so far:
The corresponding SQL for the required outcome is:
SELECT
`INPIDetail`.`PIID`,
`INPIHeader`.`CountDate`,
COUNT(*) AS `TotalLines`,
SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END) AS `Variances`,
ROUND(((COUNT(*) - SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END)) / COUNT(*)) * 100, 2) AS `Accuracy`
FROM `INPIDetail` `INPIDetail`
INNER JOIN `INPIHeader` `INPIHeader`
ON `INPIHeader`.`PIID` = `INPIDetail`.`PIID`
AND `INPIHeader`.`CompanyID` = `INPIDetail`.`CompanyID`
WHERE `INPIDetail`.`CompanyID` = X
GROUP BY `INPIDetail`.`CompanyID`, `INPIDetail`.`PIID`, `INPIHeader`.`CountDate`
;
As seen above, I have the SUM(CASE WHEN `INPIDetail`.`VarQty` = 0 THEN 0 ELSE 1 END)
part of the query figured out, but the part that I’m struggling with is the ability to subtract this amount from the count of all records.
Any ideas on how I can achieve the desired outcome?
PS. I know I can very easily do this with a SQL view, and then create my own DAC and then simply just build a GI off of that DAC, but I’m wondering if there’s a way to achieve this without doing all of that.
Thanks in advance