• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

0
Jeff S
Asked:
Jeff S
1 Solution
 
ullfindsmitCommented:
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.
0
 
chapmandewCommented:
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
0
 
appariCommented:
try this

SELECT
      Sum ( CASE  WHEN pva.PatBalance + pva.InsBalance > 1 THEN  
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
0
 
Jeff SAuthor Commented:
Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now