>Hi all,
>
>I have the following query that performs some statistical calculations:
>
>
>select a.cCustNo, a.cSerialNo, a.iAssayId, a.iSampleId, a.cReagentLot, a.nResult,
> iCount = (select count(c.nResult) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ),
> nMean = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ),
> nSd = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END,
> nCv = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END/(select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot )*100,
> n2sdminus = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) - (CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END *2),
> n2sdplus = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) + (CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END *2),
> nSdi = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0
> ELSE
> (a.nResult - (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ))
> / (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot )
> END
>
>from s2p2_customerData a join s2p2_participants b
> on a.cSerialNo = b.cInstSerialNo
>where a.iBatchID = 4
>and b.iInstrumentType = 1
>and a.lIsOutlier = 0
>group by a.cCustNo, a.cSerialNo, a.iAssayId, a.iSampleId, a.cReagentLot, a.nResult
>order by a.iAssayId, a.iSampleId, a.cCustNo, a.cSerialNo
>
>
>The problem is with the nSdi calculations. The formula for this field is (a.nResult - nMean) / nSd. When try the execute the query I am getting the Divide by zero error encountered error mesage.
>
>Thank you,
>Daniel
(a.nResult - nMean) / NULLIF(nSd,0)
