jd0963 jd0963 - 16 days ago 5
SQL Question

Replace blank cells with NULL?

enter image description hereplease see the attached image.

My query is looking at what percentage of "breaches" we have however when there is no breaches, my query is returning no cells, not even NULLS (please see attached photo)

Is there any way I could get this to just show NULL or "100%" instead of blank cells??

Here is my query

select *

from

(select '100' - convert(decimal(5,2),(100 * convert(float,CountBreach)/nullif(convert(float,CountNot),0))) AS '2 week target'
from

(select CancerBreach, COUNT(*) [CountBreach] from
(

SELECT

d.HospitalNumber ,


CASE WHEN SUM(DATEDIFF(DD,ReceiptOfReferralDate, FirstAppointmentDate)- FirstAppointmentWaitingTimeAdjusted) > 14
THEN 'Breach' ELSE 'Not Breach'
END AS CancerBreach


FROM [WH].[SCR].[Referral] R
LEFT OUTER JOIN WHREPORTING.LK.SpecialtyDivision S
ON CONVERT(Varchar(10),(R.SpecialtyCode)) = S.SpecialtyCode
LEFT JOIN WH.SCR.Demographic as d
on d.UniqueRecordId = R.DemographicUniqueRecordId

WHERE
R.FirstAppointmentOrganisationCode IN ('RM202', 'RM201')
AND PriorityTypeCode = '03'
AND FirstAppointmentDate BETWEEN '01 oct 2016' AND '14 oct 2016'
AND R.CancerTypeCode <> '16'
AND R.FirstAppointmentDate is not NULL

AND NewTumourSite IS NULL
and s.SpecialtyCode = '103'
GROUP BY


d.HospitalNumber)f

where CancerBreach = 'breach'

group by CancerBreach) e,


(select COUNT(*) [CountNot] from

(

SELECT

d.HospitalNumber ,


CASE WHEN SUM(DATEDIFF(DD,ReceiptOfReferralDate, FirstAppointmentDate)- FirstAppointmentWaitingTimeAdjusted) > 14
THEN 'Breach' ELSE 'Not Breach'
END AS CancerBreach


FROM [WH].[SCR].[Referral] R
LEFT OUTER JOIN WHREPORTING.LK.SpecialtyDivision S
ON CONVERT(Varchar(10),(R.SpecialtyCode)) = S.SpecialtyCode
LEFT JOIN WH.SCR.Demographic as d
on d.UniqueRecordId = R.DemographicUniqueRecordId

WHERE R.FirstAppointmentOrganisationCode IN ('RM202', 'RM201')
AND PriorityTypeCode = '03'
AND FirstAppointmentDate BETWEEN '01 oct 2016' AND '14 oct 2016'
AND R.CancerTypeCode <> '16'
AND R.FirstAppointmentDate is not NULL

AND NewTumourSite IS NULL
and s.SpecialtyCode = '103'
GROUP BY


d.HospitalNumber)f

where CancerBreach = 'Not breach'

group by CancerBreach) d)e

Answer

You could use a union with a default value and a sort with Top 1 record be returned, e.g:

SELECT TOP 1 *
FROM
(
    SELECT '100'-CONVERT( DECIMAL(5, 2), (100 * CONVERT(FLOAT, CountBreach) / NULLIF(CONVERT(FLOAT, CountNot), 0))) AS '2 week   target'
    FROM
    (
        SELECT CancerBreach,
               COUNT(*) [CountBreach]
        FROM
        (
            SELECT d.HospitalNumber,
                   CASE
                       WHEN SUM(DATEDIFF(DD, ReceiptOfReferralDate, FirstAppointmentDate) - FirstAppointmentWaitingTimeAdjusted) > 14
                       THEN 'Breach'
                       ELSE 'Not Breach'
                   END AS CancerBreach
            FROM [WH].[SCR].[Referral] R
                 LEFT OUTER JOIN WHREPORTING.LK.SpecialtyDivision S ON CONVERT( VARCHAR(10), (R.SpecialtyCode)) = S.SpecialtyCode
                 LEFT JOIN WH.SCR.Demographic AS d ON d.UniqueRecordId = R.DemographicUniqueRecordId
            WHERE R.FirstAppointmentOrganisationCode IN('RM202', 'RM201')
                 AND PriorityTypeCode = '03'
                 AND FirstAppointmentDate BETWEEN '01 oct 2016' AND '14 oct 2016'
                 AND R.CancerTypeCode <> '16'
                 AND R.FirstAppointmentDate IS NOT NULL
                 AND NewTumourSite IS NULL
                 AND s.SpecialtyCode = '103'
            GROUP BY d.HospitalNumber
        ) f
        WHERE CancerBreach = 'breach'
        GROUP BY CancerBreach
    ) e,
    (
        SELECT COUNT(*) [CountNot]
        FROM
        (
            SELECT d.HospitalNumber,
                   CASE
                       WHEN SUM(DATEDIFF(DD, ReceiptOfReferralDate, FirstAppointmentDate) - FirstAppointmentWaitingTimeAdjusted) > 14
                       THEN 'Breach'
                       ELSE 'Not Breach'
                   END AS CancerBreach
            FROM [WH].[SCR].[Referral] R
                 LEFT OUTER JOIN WHREPORTING.LK.SpecialtyDivision S ON CONVERT( VARCHAR(10), (R.SpecialtyCode)) = S.SpecialtyCode
                 LEFT JOIN WH.SCR.Demographic AS d ON d.UniqueRecordId = R.DemographicUniqueRecordId
            WHERE R.FirstAppointmentOrganisationCode IN('RM202', 'RM201')
                 AND PriorityTypeCode = '03'
                 AND FirstAppointmentDate BETWEEN '01 oct 2016' AND '14 oct 2016'
                 AND R.CancerTypeCode <> '16'
                 AND R.FirstAppointmentDate IS NOT NULL
                 AND NewTumourSite IS NULL
                 AND s.SpecialtyCode = '103'
            GROUP BY d.HospitalNumber
        ) f
        WHERE CancerBreach = 'Not breach'
        GROUP BY CancerBreach
    ) d
    UNION SELECT 100 AS '2 week   target'
) e
ORDER BY [2 week   target] ASC;
Comments