asked on

SQL - CASE Statement help needed

I am getting a Cout of Visits = 0 against a clients database when it should equal 3. When I run my sub select query in the Count of Visits by itself, I get a count of 3, however when its run the CASE statement, its giving me 0. I am confused were I messed up the SQL. Any insight is appreciated.

SELECT
        CASE 
                WHEN SUM(pva.PatBalance) + SUM(pva.InsBalance) > 1 THEN 
		(
		SELECT 
			COUNT(pv.PatientVisitId) 
		FROM
			PatientVisit pv
			JOIN PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
			JOIN Medlists bs ON pv.BillStatus = bs.JoinId
		WHERE
			bs.Code = 2 AND
			bs.TableName = 'BillStatus'
			and pva.PatBalance + pva.InsBalance > 1
		)
                ELSE 0
        END AS [Count of Visits],
        ISNULL(SUM(CASE WHEN (pva.InsBalance + pva.PatBalance > 1) THEN pva.InsBalance
                        ELSE 0
                        END) , 0)AS [Insurance Balance] ,
        ISNULL(SUM(CASE WHEN (pva.PatBalance + pva.InsBalance > 1) THEN pva.PatBalance
                        ELSE 0
                        END) , 0)AS [Patient Balance] ,
        bs.Code,
        bs.Description
FROM
        dbo.MedLists bs 
        LEFT JOIN dbo.PatientVisit pv ON pv.BillStatus = bs.JoinId 
        LEFT JOIN dbo.PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
        bs.Code IN ( 2 ) AND
        bs.TableName = 'BillStatus'  -- Limit to BillStatus only
GROUP BY
        bs.code,
        bs.Description

Open in new window

ullfindsmit

Try using a simple '1' instead of the subquery to see if it actually is being called
It simply might be the '0' in the ELSE case

Please verify
Smit.

chapmandew

declare @count int
set @count = (
SELECT
COUNT(pv.PatientVisitId)
FROM
PatientVisit pv
JOIN PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
JOIN Medlists bs ON pv.BillStatus = bs.JoinId
WHERE
bs.Code = 2 AND
bs.TableName = 'BillStatus'
and pva.PatBalance + pva.InsBalance > 1
)
SELECT
CASE
WHEN SUM(pva.PatBalance) + SUM(pva.InsBalance) > 1 THEN
@count
ELSE 0
END AS [Count of Visits],
ISNULL(SUM(CASE WHEN (pva.InsBalance + pva.PatBalance > 1) THEN pva.InsBalance
ELSE 0
END) , 0)AS [Insurance Balance] ,
ISNULL(SUM(CASE WHEN (pva.PatBalance + pva.InsBalance > 1) THEN pva.PatBalance
ELSE 0
END) , 0)AS [Patient Balance] ,
bs.Code,
bs.Description
FROM
dbo.MedLists bs
LEFT JOIN dbo.PatientVisit pv ON pv.BillStatus = bs.JoinId
LEFT JOIN dbo.PatientVisitAgg pva ON pva.PatientVisitId = pv.PatientVisitId
WHERE
bs.Code IN ( 2 ) AND
bs.TableName = 'BillStatus' -- Limit to BillStatus only
GROUP BY
bs.code,
bs.Description

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeff S

ASKER

Thanks!