Union with a CASE statement - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Union with a CASE statement

Author

Topic

grembos
Starting Member

7 Posts

Posted - 2011-12-06 : 09:50:39

First off thanks to all that reply with help

BA Hat:
I have a health insurance SQL question
If you are the person who paid for the insurance you are a Subscriber
A person who received treatment and is not the Subscriber will have a Patient record

In the case where the Subscriber is the one receiving treatment there is no Patient record ( ie. use the Subscriber info)
So NO 2010CA (patient )records and NO TBL_2000C (patient hdr record)

I currently make two passes to capture this two scenarios.

Can I do it in one using a CASE statement.

See SQL below .... Thanks

DECLARE @ClmHdr TABLE(
ID_Num int IDENTITY(1,1),
UniqueID char(17),
DocCtrlNum char(14),
RequestID char(20),
ControlGroupNum char(5),
TransactionNum char(10),
ParentRepeatNum int,
RepeatNum int,
SubSSN char(9),
[Group] char(4),
SubLoc char(4),
Pat_DOB DATETIME,
R_C char(2),
Pat_Fname char(8),
Pat_Lname char(24),
Sub_Fname char(8),
Sub_Lname char(24),
License char(12),
PrvBusID char(9),
ProvFname char(8),
ProvLname char(16),
ProvAdd1 char(29),
ProvAdd2 char(24),
ProvCity char(14),
ProvState char(2),
ProvZip char(5),
AOB char(1),
PAY_TO char(1),
TOTSUB NUMERIC(8,2),
ENTDATE DATETIME,
OPER char(8),
NUMVAR NUMERIC(3,0),
PLAN_CODE char(2),
RECPT_DATE DATETIME,
IDENTERED char(34),
NPI char(10),
ClaimNum char(10)
);

INSERT INTO
@ClmHdr
(REQUESTID,ControlGroupNum,TransactionNum,PARENTREPEATNUM, REPEATNUM
,[GROUP],SUBLOC,PAT_DOB ,PAT_FNAME,PAT_LNAME,SUB_FNAME,SUB_LNAME
,PrvBusID,PROVFNAME,PROVLNAME,PROVADD1,PROVADD2,PROVCITY,PROVSTATE,PROVZIP
,AOB,PAY_TO,CLAIMNUM,TOTSUB,ENTDATE,OPER,PLAN_CODE,RECPT_DATE,IDENTERED)
SELECT
I.REQUESTID,I.ControlGroupNum,I.TransactionNum,I.PARENTREPEATNUM, I.REPEATNUM,
ISNULL(LEFT(TBL_2000B.SBR03,4),'') AS [Group],
ISNULL(RIGHT(TBL_2000B.SBR03,4),'') AS SubLoc,
NULL AS Pat_DOB,
SPACE(8) AS Pat_Fname,
SPACE(24) AS Pat_Lname,
LEFT(TBL_2010BA.NM104,8) AS Sub_Fname,
LEFT(TBL_2010BA.NM103,24) AS Sub_Lname,
CASE WHEN TBL_2010AA.NM108='24' THEN ISNULL(LEFT(TBL_2010AB.NM109,9),'') ELSE '' END AS PrvBusID,
LEFT(TBL_2310B.NM104,8) AS ProvFname,
LEFT(TBL_2310B.NM103,16) AS ProvLname,
LEFT(TBL_2010AA.N301,29) AS ProvAdd1,
LTRIM(LEFT(ISNULL(TBL_2010AA.N302,''),24)) AS ProvAdd2 ,
LEFT(TBL_2010AA.N401,14) AS ProvCity,
LEFT(TBL_2010AA.N402,2) AS ProvState,
LEFT(TBL_2010AA.N403,5) AS ProvZip,
LEFT(I.CLM08,10) AS AOB,
CASE WHEN I.CLM08='Y' THEN 'P' END AS PAY_TO,
I.CLM01 AS CLAIMNUM,
I.CLM02 AS TOTSUB,
GETDATE() AS ENTDATE,
'ECS' AS OPER,
'RI' AS PLAN_CODE,
GETDATE() AS RECPT_DATE,
LEFT(TBL_2010BA.NM109,34) AS IDENTERED
FROM
TBL_2000A A
INNER JOIN TBL_2010AA
ON A.RepeatNum = TBL_2010AA.ParentRepeatNum
INNER JOIN TBL_2010AB
ON TBL_2010AA.ParentRepeatNum = TBL_2010AB.ParentRepeatNum
INNER JOIN TBL_2000B
ON TBL_2010AB.ParentRepeatNum = TBL_2000B.ParentRepeatNum
INNER JOIN TBL_2010BA
ON TBL_2000B.RepeatNum = TBL_2010BA.ParentRepeatNum
INNER JOIN TBL_2010BB F
ON TBL_2010BA.ParentRepeatNum = F.ParentRepeatNum
INNER JOIN TBL_2300 I
ON F.ParentRepeatNum = I.ParentRepeatNum
INNER JOIN TBL_2310B
ON I.RepeatNum = TBL_2310B.ParentRepeatNum

INSERT INTO
@ClmHdr
(REQUESTID,ControlGroupNum,TransactionNum,PARENTREPEATNUM, REPEATNUM
,[GROUP],SUBLOC,PAT_DOB ,PAT_FNAME,PAT_LNAME,SUB_FNAME,SUB_LNAME
,PrvBusID,PROVFNAME,PROVLNAME,PROVADD1,PROVADD2,PROVCITY,PROVSTATE,PROVZIP
,AOB,PAY_TO,CLAIMNUM,TOTSUB,ENTDATE,OPER,PLAN_CODE,RECPT_DATE,IDENTERED)
SELECT
I.REQUESTID,I.ControlGroupNum,I.TransactionNum,I.PARENTREPEATNUM, I.REPEATNUM,
ISNULL(LEFT(TBL_2000B.SBR03,4),'') AS [Group],
ISNULL(RIGHT(TBL_2000B.SBR03,4),'') AS SubLoc,
SubstrING(TBL_2010CA.DMG02,5,2) + '/' +
SubstrING(TBL_2010CA.DMG02,7,2) + '/' +
SubstrING(TBL_2010CA.DMG02,1,4) AS PAT_DOB,
LEFT(TBL_2010CA.NM104,8) AS Pat_Fname,
LEFT(TBL_2010CA.NM103,24) AS Pat_Lname,
LEFT(TBL_2010BA.NM104,8) AS Sub_Fname,
LEFT(TBL_2010BA.NM103,24) AS Sub_Lname,
CASE WHEN TBL_2010AA.NM108='24' THEN ISNULL(LEFT(TBL_2010AB.NM109,9),'') ELSE '' END AS PrvBusID,
LEFT(TBL_2310B.NM104,8) AS ProvFname,
LEFT(TBL_2310B.NM103,16) AS ProvLname,
LEFT(TBL_2010AA.N301,29) AS ProvAdd1,
LTRIM(LEFT(ISNULL(TBL_2010AA.N302,''),24)) AS ProvAdd2 ,
LEFT(TBL_2010AA.N401,14) AS ProvCity,
LEFT(TBL_2010AA.N402,2) AS ProvState,
LEFT(TBL_2010AA.N403,5) AS ProvZip,
LEFT(I.CLM08,10) AS AOB,
CASE WHEN I.CLM08='Y' THEN 'P' END AS PAY_TO,
I.CLM01 AS CLAIMNUM,
I.CLM02 AS TOTSUB,
GETDATE() AS ENTDATE,
'ECS' AS OPER,
'RI' AS PLAN_CODE,
GETDATE() AS RECPT_DATE,
LEFT(TBL_2010BA.NM109,34) AS IDENTERED
FROM
TBL_2000A A
INNER JOIN TBL_2010AA
ON A.RepeatNum = TBL_2010AA.ParentRepeatNum
INNER JOIN TBL_2010AB
ON TBL_2010AA.ParentRepeatNum = TBL_2010AB.ParentRepeatNum
INNER JOIN TBL_2000B
ON TBL_2010AB.ParentRepeatNum = TBL_2000B.ParentRepeatNum
INNER JOIN TBL_2010BA
ON TBL_2000B.RepeatNum = TBL_2010BA.ParentRepeatNum
INNER JOIN TBL_2010BB F
ON TBL_2010BA.ParentRepeatNum = F.ParentRepeatNum
INNER JOIN TBL_2000C G
ON F.ParentRepeatNum = G.ParentRepeatNum
INNER JOIN TBL_2010CA
ON G.RepeatNum = TBL_2010CA.ParentRepeatNum
INNER JOIN TBL_2300 I
ON TBL_2010CA.ParentRepeatNum = I.ParentRepeatNum
INNER JOIN TBL_2310B
ON I.RepeatNum = TBL_2310B.ParentRepeatNum
order by I.REQUESTID,I.ControlGroupNum,I.TransactionNum,I.PARENTREPEATNUM, I.REPEATNUM

DECLARE @ClmHdr2 TABLE(
ID_Num int IDENTITY(1,1),
UniqueID char(17),
DocCtrlNum char(14),
RequestID char(20),
ControlGroupNum char(5),
TransactionNum char(10),
ParentRepeatNum int,
RepeatNum int,
SubSSN char(9),
[Group] char(4),
SubLoc char(4),
Pat_DOB DATETIME,
R_C char(2),
Pat_Fname char(8),
Pat_Lname char(24),
Sub_Fname char(8),
Sub_Lname char(24),
License char(12),
PrvBusID char(9),
ProvFname char(8),
ProvLname char(16),
ProvAdd1 char(29),
ProvAdd2 char(24),
ProvCity char(14),
ProvState char(2),
ProvZip char(5),
AOB char(1),
PAY_TO char(1),
TOTSUB NUMERIC(8,2),
ENTDATE DATETIME,
OPER char(8),
NUMVAR NUMERIC(3,0),
PLAN_CODE char(2),
RECPT_DATE DATETIME,
IDENTERED char(34),
NPI char(10),
ClaimNum char(10)
);

INSERT INTO
@ClmHdr2
(REQUESTID,ControlGroupNum,TransactionNum,PARENTREPEATNUM, REPEATNUM
,[GROUP],SUBLOC,PAT_DOB ,PAT_FNAME,PAT_LNAME,SUB_FNAME,SUB_LNAME
,PrvBusID,PROVFNAME,PROVLNAME,PROVADD1,PROVADD2,PROVCITY,PROVSTATE,PROVZIP
,AOB,PAY_TO,CLAIMNUM,TOTSUB,ENTDATE,OPER,PLAN_CODE,RECPT_DATE,IDENTERED,NPI,R_C)
select REQUESTID,ControlGroupNum,TransactionNum,PARENTREPEATNUM,REPEATNUM
,[GROUP],SUBLOC,PAT_DOB ,PAT_FNAME,PAT_LNAME,SUB_FNAME,SUB_LNAME
,PrvBusID,PROVFNAME,PROVLNAME,PROVADD1,PROVADD2,PROVCITY,PROVSTATE,PROVZIP
,AOB,PAY_TO,CLAIMNUM,TOTSUB,ENTDATE,OPER,PLAN_CODE,RECPT_DATE,IDENTERED,NPI,R_C
from @ClmHdr
order by
REQUESTID,ControlGroupNum,TransactionNum,PARENTREPEATNUM,REPEATNUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 10:50:16

for that you need to change the inner joins to 2010CA (patient ) table and TBL_2000C (patient hdr record) tables to left join and then use check in CASE on fields from them against NULL values to determine if its a patient (columns will be NOT NULL) or its a subscriber (NULL values)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grembos
Starting Member

7 Posts

Posted - 2011-12-06 : 13:09:22

what is the sql syntax for
( a Patient Record Exists in 2010CA)

So something like this

CASE WHEN ( a Patient Record Exists in 2010CA) THEN LEFT(HL_837_2010CA.NM104,8) ELSE SPACE(0) END AS Pat_Fname,

CASE WHEN ( a Patient Record Exists in 2010CA) THEN LEFT( TBL_2010CA.NM103,24) ELSE SPACE(0) END AS Pat_Lname,

INNER JOIN TBL_2010BB F
ON TBL_2010BA.ParentRepeatNum = F.ParentRepeatNum
-- INNER JOIN TBL_2000C G
-- ON F.ParentRepeatNum = G.ParentRepeatNum
-- INNER JOIN TBL_2010CA
-- ON G.RepeatNum = TBL_2010CA.ParentRepeatNum
LEFT OUTER JOIN TBL_2000C G
ON F.ParentRepeatNum = G.ParentRepeatNum
LEFT OUTER JOIN TBL_2010CA
ON G.RepeatNum = TBL_2010CA.ParentRepeatNum
INNER JOIN TBL_2300 I
ON TBL_2010CA.ParentRepeatNum = I.ParentRepeatNum
INNER JOIN TBL_2310B
ON I.RepeatNum = TBL_2310B.ParentRepeatNum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-06 : 23:30:30

it should be

CASE WHEN ( TBL_2010CA.ParentRepeatNum is not null) THEN LEFT(HL_837_2010CA.NM104,8) ELSE SPACE(0) END AS Pat_Fname,
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grembos
Starting Member

7 Posts

Posted - 2011-12-07 : 19:27:04

Thanks for your input, I will try your suggestion

quote:
Originally posted by visakh16

it should be

CASE WHEN ( TBL_2010CA.ParentRepeatNum is not null) THEN LEFT(HL_837_2010CA.NM104,8) ELSE SPACE(0) END AS Pat_Fname,
..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-08 : 01:22:49

wc
let us know how you got on

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

grembos
Starting Member

7 Posts

Posted - 2011-12-08 : 19:52:25

PAT_LNAME and PAT_LNAME now show ... Thanks for that
however,
P_rovLname , PROVFNAME, PROVLNAME are still showing as blank for
records where the is only a Subscriber(2010BA) record but
no patient record (2010CA)

thanks again

SELECT
A.REQUESTID,A.ControlGroupNum,A.TransactionNum,A.PARENTREPEATNUM, A.REPEATNUM,
ISNULL(LEFT(TBL_2000B.SBR03,4),'') AS [Group],
ISNULL(RIGHT(TBL_2000B.SBR03,4),'') AS SubLoc,
LEFT(TBL_2010BA.NM103,24) AS Sub_Lname,
LEFT(TBL_2010BA.NM104,8) AS Sub_Fname,
CASE WHEN TBL_2010CA.DMG02 IS NULL THEN
SubstrING(TBL_2010BA.DMG02,5,2) + '/' +
SubstrING(TBL_2010BA.DMG02,7,2) + '/' +
SubstrING(TBL_2010BA.DMG02,1,4)
ELSE
SubstrING(TBL_2010CA.DMG02,5,2) + '/' +
SubstrING(TBL_2010CA.DMG02,7,2) + '/' +
SubstrING(TBL_2010CA.DMG02,1,4)
END AS PAT_DOB,
CASE WHEN TBL_2010CA.NM103 IS NULL THEN LEFT(TBL_2010BA.NM103,24) ELSE LEFT(TBL_2010CA.NM103,24) END AS PAT_LNAME,
CASE WHEN TBL_2010CA.NM104 IS NULL THEN LEFT(TBL_2010BA.NM104,8) ELSE LEFT(TBL_2010CA.NM104,8) END AS PAT_FNAME,
CASE WHEN TBL_2010AA.NM108='24' THEN ISNULL(LEFT(TBL_2010AB.NM109,9),'') ELSE '' END AS PrvBusID,
CASE WHEN TBL_2310B.NM104 IS NULL THEN SPACE(0) ELSE LEFT(TBL_2310B.NM104,8) END AS PROVFNAME,
ISNULL(LEFT(TBL_2310B.NM103,16),'') AS ProvLname,
CASE WHEN TBL_2310B.NM103 IS NULL THEN 'NoProvLname' ELSE LEFT(TBL_2310B.NM103,16) END AS P_rovLname,
LEFT(TBL_2010AA.N301,29) AS ProvAdd1,
LTRIM(LEFT(ISNULL(TBL_2010AA.N302,''),24)) AS ProvAdd2 ,
LEFT(TBL_2010AA.N401,14) AS ProvCity,
LEFT(TBL_2010AA.N402,2) AS ProvState,
LEFT(TBL_2010AA.N403,5) AS ProvZip,
LEFT(I.CLM08,10) AS AOB,
CASE WHEN I.CLM08='Y' THEN 'P' END AS PAY_TO,
I.CLM01 AS CLAIMNUM,
I.CLM02 AS TOTSUB,
GETDATE() AS ENTDATE
FROM
TBL_2000A A
INNER JOIN TBL_2010AA
ON A.RepeatNum = TBL_2010AA.ParentRepeatNum
INNER JOIN TBL_2010AB
ON TBL_2010AA.ParentRepeatNum = TBL_2010AB.ParentRepeatNum
INNER JOIN TBL_2000B
ON TBL_2010AB.ParentRepeatNum = TBL_2000B.ParentRepeatNum
INNER JOIN TBL_2010BA
ON TBL_2000B.RepeatNum = TBL_2010BA.ParentRepeatNum
LEFT OUTER JOIN TBL_2010BB F
ON TBL_2010BA.ParentRepeatNum = F.ParentRepeatNum
LEFT JOIN TBL_2000C G
ON F.ParentRepeatNum = G.ParentRepeatNum
LEFT OUTER JOIN TBL_2010CA
ON G.RepeatNum = TBL_2010CA.ParentRepeatNum
LEFT OUTER JOIN TBL_2300 I
ON TBL_2010CA.ParentRepeatNum = I.ParentRepeatNum
LEFT OUTER JOIN TBL_2310B
ON I.RepeatNum = TBL_2310B.ParentRepeatNum
WHERE A.ControlGroupNum = '40' AND TBL_2010BA.PARENTREPEATNUM <> '12'
order by TBL_2010BA.repeatnum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 01:43:44

thats because of thie relationship


...
LEFT OUTER JOIN TBL_2300 I
ON TBL_2010CA.ParentRepeatNum = I.ParentRepeatNum
LEFT OUTER JOIN TBL_2310B 
ON I.RepeatNum = TBL_2310B.ParentRepeatNum ...

since you're using fields from TBL_2010CA to join onto TBL_2310B through TBL_2300 you wont get any details from them when there's no record in 2010 and you're using fields from 2300 required fields as below which is why they're missing value


CASE WHEN TBL_2310B.NM104 IS NULL THEN SPACE(0) ELSE LEFT(TBL_2310B.NM104,8) END AS PROVFNAME,
ISNULL(LEFT(TBL_2310B.NM103,16),'') AS ProvLname,

i think based on requirement you should change this relationship to some table which has required data always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-09 : 01:50:09

I really dont understand why your objects are named like this. It certainly doesnt add any clarity on what they actually store within them. This is really a bad way of naming objects IMHO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources