SQLTeam.com | Weblogs | Forums

Check fillrate in a table

sql2008

#1

I am trying to calculate not filled rate in value column
for eg: Total NULL (In Value Column)/Total Not Null (in value column)*100

DECLARE @PHARMA TABLE
(
MRN VARCHAR (30),
department VARCHAR (30),
optical VARCHAR (30),
optical_value VARCHAR (30),
dermo VARCHAR (30),
dermo_value VARCHAR (30),
Neuro VARCHAR (30),
Neuro_value VARCHAR (30)

)

Insert @PHARMA

SELECT 'BSE23098', 'Clinic', 'Willmar','10', 'Betamethasone' ,'20', 'neurobion', NULL UNION ALL

SELECT 'ZSE23098', 'Clinic', 'AUROBRIME','10', 'adapalene' ,'20', 'Blong', NULL UNION ALL
SELECT 'ZSE23098', 'Clinic', 'VOZOLE', NULL, 'SURFRAZ', '30' ,'Evion', '63'

In Output expecting only 1 column with fill rate.

Is there a way to achieve this. kindly suggest.


#2
SELECT 
    CAST(optical_not_NULL * 100.0 / NULLIF((optical_NULL + optical_not_NULL), 0) AS decimal(5, 2)) AS optical_fill_rate,
    CAST(dermo_not_NULL * 100.0 / NULLIF((dermo_NULL + dermo_not_NULL), 0) AS decimal(5, 2)) AS dermo_fill_rate,
    CAST(neuro_not_NULL * 100.0 / NULLIF((neuro_NULL + neuro_not_NULL), 0) AS decimal(5, 2)) AS neuro_fill_rate
FROM (
    SELECT
        SUM(CASE WHEN optical_value IS NULL THEN 1 ELSE 0 END) AS optical_NULL,
        SUM(CASE WHEN optical_value IS NULL THEN 0 ELSE 1 END) AS optical_not_NULL,
        SUM(CASE WHEN dermo_value IS NULL THEN 1 ELSE 0 END) AS dermo_NULL,
        SUM(CASE WHEN dermo_value IS NULL THEN 0 ELSE 1 END) AS dermo_not_NULL,
        SUM(CASE WHEN neuro_value IS NULL THEN 1 ELSE 0 END) AS neuro_NULL,
        SUM(CASE WHEN neuro_value IS NULL THEN 0 ELSE 1 END) AS neuro_not_NULL
    FROM @PHARMA
) AS derived