Stored Procedure - Case Statement help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure - Case Statement help.

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-15 : 12:02:05

This is how this field was originally written in my stored procedure:

'271.AmountPaid' = ISNULL(pv.HCFAPaymentAmount, 0.0),

I need to take what I have below and merge back in what it used to be when the @PrimaryCarrierType is not like 'medicare%' and AND @CurrentCarrier = 2.

'271.AmountPaid' =
CASE WHEN @PrimaryCarrierType LIKE 'medicare%' AND @CurrentCarrier = 2 THEN
@TotalCharge - CASE WHEN ISNULL(pv.HCFAPaymentAmount,0.0) > 0 AND @FQHCBalance > 0 THEN @FQHCBalance ELSE pva.InsBalance END,

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-15 : 12:10:28

Try this:


'271.AmountPaid' =
	CASE
		WHEN @PrimaryCarrierType LIKE 'medicare%' 
		AND @CurrentCarrier = 2 THEN
			@TotalCharge - 
			CASE 
				WHEN ISNULL(pv.HCFAPaymentAmount,0.0) > 0 
				AND @FQHCBalance > 0 THEN 
					@FQHCBalance 
				ELSE pva.InsBalance 
			END
		WHEN @PrimaryCarrierType LIKE 'medicare%' 
		AND @CurrentCarrier = 2 THEN
			ISNULL(pv.HCFAPaymentAmount, 0.0)
	END,

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-15 : 12:24:03

I think this is very close ... in my two test patients, I am getting the right value when PrimaryCarrier like medicare and CurrentCarrier = 2. When its not these, I am getting a NULL now when it should have a value there.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-15 : 12:44:07

Oops, change the second WHEN @PrimaryCarrierType LIKE 'medicare%' to WHEN @PrimaryCarrierType Not LIKE 'medicare%'

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-02-15 : 12:48:12

DOH! I should have seen that one .... that did the trick. Beautiful. Thanks for the help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources