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.
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