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