January 15, 2008 at 12:52 pm
hi,
I am dealing with 9 columns here.
[PrimaryPayor] [nvarchar](255) NULL,
[CURRINS1] [nvarchar](255) NULL,
[CURRINS2] [nvarchar](255) NULL,
[CURRINS3] [nvarchar](255) NULL,
[LastBill] [nvarchar](255) NULL,
[CURRINSBAL1] [money] NULL,
[CURRINSBAL2] [money] NULL,
[CURRINSBAL3] [money] NULL,
[PatBalance] [money] NULL
I have to check if value of LastBill = 'final bill' and out of following 4 column, whose value is greatest: CURRINSBAL1, CURRINSBAL2, CURRINSBAL3, PatBlance.
if LastBill = 'final bill' AND CURRRINSBAL1 is greatest, then return CURRINS1
if LastBill = 'final bill' AND CURRRINSBAL2 is greatest, then return CURRINS2
if LastBill = 'final bill' AND CURRRINSBAL3 is greatest, then return CURRINS3
if LastBill = 'final bill' AND PatBalance is greatest, then return 'SP'
if LastBill = 'unbilled' then return PrimaryPayor
if CURRINSBAL1 or CURRINSBAL2 or CURRINSBAL3 or PatBlance have negative value, then return PrimaryPayor
I tried this just to test first line, but it didn't work:
SELECT
CASE
WHEN [Last Billed]='FINAL BILLED' AND (CURRINSBAL1 > (CURRINSBAL2) or (CURRINSBAL3) or (PATBALANCE )) THEN CURRINSBAL1
ELSE
'TEST'
END
It gives an error: " An expression of non-boolean type specified in a context where a condition is expected, near 'or'. "
Any Ideas ?
Ravi.
------------
🙂
January 15, 2008 at 1:10 pm
You can read about this subject on the link below.
MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906
select
a.[PrimaryPayor],
MAX_BAL = max(bb.xx)
from
(
select xx = a.[CURRINSBAL1] where a.[CURRINSBAL1] is not null union all
select xx = a.[CURRINSBAL2] where a.[CURRINSBAL2] is not null union all
select xx = a.[CURRINSBAL3] where a.[CURRINSBAL3] is not null union all
select xx = a.[PatBalance] where a.[PatBalance] is not null
) bb
from
MyTable a
where
a.LastBill = 'final bill'
January 15, 2008 at 2:14 pm
Michael,
Thanks. But I tried this and it worked.
CASE
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL1 > CURRINSBAL2) AND (CURRINSBAL1 > CURRINSBAL3) AND (CURRINSBAL1 > PATBALANCE)THEN CURRINS1
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL2 > CURRINSBAL1) AND (CURRINSBAL2 > CURRINSBAL3) AND (CURRINSBAL2 > PATBALANCE)THEN CURRINS2
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL3 > CURRINSBAL2) AND (CURRINSBAL3 > CURRINSBAL1) AND (CURRINSBAL3 > PATBALANCE)THEN CURRINS3
WHEN [LastBill]='FINAL BILLED' AND (PATBALANCE > CURRINSBAL1) AND (PATBALANCE > CURRINSBAL2) AND (PATBALANCE > CURRINSBAL3)THEN 'SP'
WHEN (PATBALANCE < 0) OR (CURRINSBAL1 < 0) or (CURRINSBAL2 < 0) or (CURRINSBAL3 < 0) THEN PrimaryPayor
WHEN [LastBill]='UNBILLED' THEN PrimaryPayor
ELSE
'No Value'
END
------------
🙂
January 15, 2008 at 2:40 pm
ravirobin (1/15/2008)
Michael,Thanks. But I tried this and it worked...
It is not that simple, since you did not include logic to test for NULL columns, even though your table allows nulls. You should look at how to handle this on the link I posted.
Notice the incorrect result in the example below.
select
CASE
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL1 > CURRINSBAL2) AND (CURRINSBAL1 > CURRINSBAL3) AND (CURRINSBAL1 > PATBALANCE)THEN CURRINS1
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL2 > CURRINSBAL1) AND (CURRINSBAL2 > CURRINSBAL3) AND (CURRINSBAL2 > PATBALANCE)THEN CURRINS2
WHEN [LastBill]='FINAL BILLED' AND (CURRINSBAL3 > CURRINSBAL2) AND (CURRINSBAL3 > CURRINSBAL1) AND (CURRINSBAL3 > PATBALANCE)THEN CURRINS3
WHEN [LastBill]='FINAL BILLED' AND (PATBALANCE > CURRINSBAL1) AND (PATBALANCE > CURRINSBAL2) AND (PATBALANCE > CURRINSBAL3)THEN 'SP'
WHEN (PATBALANCE < 0) OR (CURRINSBAL1 < 0) or (CURRINSBAL2 < 0) or (CURRINSBAL3 < 0) THEN PrimaryPayor
WHEN [LastBill]='UNBILLED' THEN PrimaryPayor
ELSE
'No Value'
END
from
(
select [LastBill]='FINAL BILLED',
CURRINSBAL1=$99, CURRINSBAL2=null, CURRINSBAL3=80, PATBALANCE=$70,
CURRINS1 ='CURRINS1',CURRINS2 ='CURRINS2',CURRINS3 ='CURRINS3',
PrimaryPayor = 'PrimaryPayor'
) a
Result:
------------
No Value
(1 row(s) affected)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply