SUM with a ISNULL - Need to show as 0 (Zero) versus NULL

  • Is it possible to have '0' (zeros) versus a NULL in my output?

    SQL Results

     

    DECLARE @GroupBy1 VARCHAR(100) = 'Visit Financial Class'
    DECLARE @GroupBy2 VARCHAR(100) = NULL -- 'Insurance'

    SELECT [Type]= 'Payments'
    , [Amount]= SUM(td.Amount)
    , [Action]= t.Action
    , [Date]= CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(DateOfServiceFrom)) + '/01/' + CONVERT(VARCHAR, YEAR(DateOfServiceFrom)))
    , [AR_Bucket]= CASE
    WHEN DATEDIFF(DAY,DateOfServiceFrom,b.Entry) <= 30 THEN '0_30'
    WHEN DATEDIFF(DAY,DateOfServiceFrom,b.Entry) BETWEEN 31 AND 60 THEN '31_60'
    WHEN DATEDIFF(DAY,DateOfServiceFrom,b.Entry) BETWEEN 61 AND 90 THEN '61_90'
    WHEN DATEDIFF(DAY,DateOfServiceFrom,b.Entry) BETWEEN 91 AND 120 THEN '91_120'
    WHEN DATEDIFF(DAY,DateOfServiceFrom,b.Entry) BETWEEN 121 AND 150 THEN '121_150'
    ELSE '151+'
    END
    , [Group1Value] = CASE @GroupBy1
    WHEN 'Company' THEN c.ListName
    WHEN 'Responsible Provider' THEN d.ListName
    WHEN 'Facility' THEN f.ListName
    WHEN 'Visit Financial Class' THEN ISNULL(fc.Description,'No Financial Class')
    WHEN 'Insurance' THEN ISNULL(ic.ListName,'Self Pay')
    WHEN 'Insurance Group' THEN ISNULL(ig.Name,'No Insurance Group')
    WHEN 'Policy Type' THEN ISNULL(pt.Description,'No Policy Type')
    ELSE 'ALL'
    END
    , [Group2Value] = CASE @GroupBy2
    WHEN 'Company' THEN c.ListName
    WHEN 'Responsible Provider' THEN d.ListName
    WHEN 'Facility' THEN f.ListName
    WHEN 'Visit Financial Class' THEN ISNULL(fc.Description,'No Financial Class')
    WHEN 'Insurance' THEN ISNULL(ic.ListName,'Self Pay')
    WHEN 'Insurance Group' THEN ISNULL(ig.Name,'No Insurance Group')
    WHEN 'Policy Type' THEN ISNULL(pt.Description,'No Policy Type')
    ELSE 'ALL'
    END

    INTO #t

    FROM VisitTransactions vt
    JOIN Transactions t ON t.VisitTransactionsId = vt.VisitTransactionsId
    JOIN TransactionDistributions td ON td.TransactionsId = t.TransactionsId
    JOIN PatientVisitProcs pvp ON td.PatientVisitProcsId = pvp.PatientVisitProcsId
    JOIN PaymentMethod pm ON vt.PaymentMethodId = pm.PaymentMethodId
    JOIN Batch b ON pm.BatchId = b.BatchId
    JOIN PatientVisit pv ON vt.PatientVisitId = pv.PatientVisitId

    -- Groupings on (Company, Responsible Provider, Visit Facility, Current Insurance, Insurance Group, Policy Type and Visit Financial Class)
    JOIN DoctorFacility c ON pv.CompanyId = c.DoctorFacilityId
    JOIN DoctorFacility d ON pv.DoctorId = d.DoctorFacilityId
    JOIN DoctorFacility f ON pv.FacilityId = f.DoctorFacilityId
    LEFT JOIN MedLists fc ON pv.FinancialClassMId = fc.MedListsId
    LEFT JOIN InsuranceCarriers ic ON vt.InsuranceCarriersId = ic.InsuranceCarriersId
    LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
    LEFT JOIN MedLists pt ON ic.PolicyTypeMId = pt.MedListsId



    WHERE b.Entry >= ISNULL(N'01/01/2013', '1/1/1900')
    AND b.Entry < DATEADD(d, 1, ISNULL(N'01/31/2013', '1/1/3000'))
    AND ISNULL(pm.InsuranceTransfer, 0) = 0
    AND t.Action = 'P'

    GROUP BY Action
    , CONVERT(DATETIME, CONVERT(VARCHAR, MONTH(DateOfServiceFrom)) + '/01/' + CONVERT(VARCHAR, YEAR(DateOfServiceFrom)))
    , pvp.DateOfServiceFrom
    , b.Entry
    , c.ListName
    , d.ListName
    , f.ListName
    , fc.Description
    , ic.ListName
    , ig.Name
    , pt.Description

    SELECT * FROM
    (
    SELECT
    [Type]
    , [Group1Value]
    , [Group2Value]
    , [Amount] = SUM([Amount])
    , [AR_Bucket]
    FROM #t
    GROUP BY [Type], [Group1Value] , [Group2Value] , [AR_Bucket]
    ) t
    PIVOT(
    SUM([Amount])
    FOR [AR_Bucket] IN ([0_30],[31_60],[61_90],[91_120],[121_150],[151+])
    ) AS PivotTable

    ORDER BY
    [Group1Value]
    , [Group2Value]

    DROP TABLE #t
  • Have you tried wrapping SUM(Amount) w/ ISNULL? e.g.,

     , [Amount]= ISNULL(SUM(Amount),0)
  • I have and it does not change the values.

  • Does this work? I don't know if the extra level of nesting is required, or whether you can just replace the select * with a column list.

     

    SELECT p.[Type], 
    p.Group1Value,
    p.Group2Value,
    ISNULL(p.[0_30],0) AS ‘AR_0_30’,
    ISNULL(p.[31_60],0) AS ‘AR_31_60’....
    (
    SELECT PivotTable.*
    FROM
    (
    SELECT [Type]
    , [Group1Value]
    , [Group2Value]
    , [Amount] = SUM([Amount])
    , [AR_Bucket]
    FROM #t
    GROUP BY [Type], [Group1Value] , [Group2Value] , [AR_Bucket]
    ) t
    PIVOT(
    SUM([Amount])
    FOR [AR_Bucket] IN ([0_30],[31_60],[61_90],[91_120],[121_150],[151+])
    ) AS PivotTable
    ) AS p
  • the replacement of null's with zeros should be done by the presentation layer.

    try

    select [type]
    , Group1Value
    , Grup2Value
    , coalesce([0_30], 0) as [0_30]
    , coalesce([31_60], 0) as [31_60]
    , coalesce([61_90], 0) as [61_90]
    , coalesce([91_120], 0) as [91_120]
    , coalesce([121_150], 0) as [121_150]
    , coalesce([151+], 0) as [151+]
    , AR_Bucket
    from (select *
    from (select [type]
    , [Group1Value]
    , [Group2Value]
    , [amount] = sum([amount])
    , [AR_Bucket]
    from #t
    group by [type]
    , [Group1Value]
    , [Group2Value]
    , [AR_Bucket]
    ) t
    pivot (
    sum([Amount])
    for [AR_Bucket] in ([0_30], [31_60], [61_90], [91_120], [121_150], [151+])
    ) as pivottable

    ) p
    order by [Group1Value]
    , [Group2Value]

     

  • If you're willing to get rid of the PIVOT in favor of a cross-tab approach (which IMHO is preferable anyway), then try this:

    SELECT
    [Type], [Group1Value], [Group2Value],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '0_30' THEN Amount ELSE 0 END), 0) AS [0_30],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '31_60' THEN Amount ELSE 0 END), 0) AS [31_60],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '61_90' THEN Amount ELSE 0 END), 0) AS [61_90],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '91_120' THEN Amount ELSE 0 END), 0) AS [91_120],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '121_150' THEN Amount ELSE 0 END), 0) AS [121_150],
    ISNULL(SUM(CASE WHEN [AR_Bucket] = '151+' THEN Amount ELSE 0 END), 0) AS [151+]
    FROM
    (
    SELECT
    [Type]
    , [Group1Value]
    , [Group2Value]
    , [Amount] = SUM([Amount])
    , [AR_Bucket]
    FROM #t
    GROUP BY [Type], [Group1Value] , [Group2Value] , [AR_Bucket]
    ) t

    ORDER BY
    [Group1Value]

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you all for the amazing help. I can use all suggestions and deeply appreciate the assistance.

  • Can I mark all that replied as the answer as all truly were spot on?

  • Side note: take a look at DATEFROMPARTS and DATETIMEFROMPARTS instead of concatenating strings and converting to datetime.  For example:

    [Date] = DATETIMEFROMPARTS(year(DateOfServiceFrom), month(DateOfServiceFrom), 1, 0, 0, 0, 0)

    Or - you can use the something like this:

    [Date] = DATEADD(year, DATEDIFF(year, 0, DateOfServiceFrom), 0)

    With that said, the query you have shown has no need for the dates to be included since you are not using those in the final query.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • and while we at it - always specify the size of your varchars

    CONVERT(VARCHAR, MONTH(DateOfServiceFrom))

     

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply