Hi - I am building a query (Stored Procedure) against a single table. The objective is to return statistics on technicians that are testing new products. Each product is tested and if it passes it is shipped out the door. If the product fails it goes back for software reload. Each test records the product's serial number. In the table a serial number will only appear once for a passed test, however the serial number could appear multiple times when the test is failed. What I want to see as a result set is the total number of devices tested in a given date range, the number of devices passed, and the number of the devices that failed. If a serialed device failed more than once I only want it counted once. Can anyone assist?
My query:
SELECT C_UserName, Count(t.id) as Total,
SUM(CASE WHEN C_STATUS = 0 Then 1 ELSE 0 END) as Pass,
(CASE WHEN C_STATUS <> 0 Then 1 ELSE 0 END) as Fail -- only want to count first failure
from TableResMLT t
Where C_SITE like 'SH%'
and (t.DateEntered >= @DateFrom and t.DateEntered < DateAdd(day, 1, @DateTo))
Group by C_Username