Reputation: 61
I am trying to combine these two LEFT JOINS together.
Preview of tables I am working with:
For example/context:
Table Name: [GMC Claims 2019]
PatNo | RevCode | CPT | BilledCharges | DRG |
---|---|---|---|---|
5 | 141 | null | 500 | 439 |
5 | 636 | J1234 | 276000 | 101 |
Table Name: BCBS_DRG_REIMBURS
DRG Code | Description | DRG Weight | ALOS | High Trim | Effective Date | DRG Rate | Per Diem High Trim Outlier |
---|---|---|---|---|---|---|---|
42 | XXXXX | YYYYY | 30 | 54 | 10/1/2018 | $235,121.59 | $5,486.17 |
101 | XXXXX | YYYYY | 24 | 40 | 10/1/2018 | $146,736.72 | $4,279.82 |
439 | XXXX | YYYYY | 13 | 23 | 10/1/2018 | $63,124.34 | $3,399.00 |
Table Name: [BCBS DRG CARVE OUT 07012016]
DRG | SERVICE | PMT (SDA) |
---|---|---|
101 | DRG CARVE OUT | 13537 |
439 | DRG CARVE OUT | 13537 |
Desired Output:
DRG | DRG Reimbursement | Carve Out PMT |
---|---|---|
439 | $63,124.34 | 13537 |
101 | $146,736.72 | 13537 |
The logic is, given the claims table as our main table, find the DRG codes that match (101 & 439 in this case) and return the respective values (so DRG Reimbursement from the BCBS_DRG_REIMBURS table and Carve Out PMT from the [BCBS DRG CARVE OUT 07012016] table.
My code/attempt:
SELECT
[GMC Claims 2019].DRG
FROM
[GMC Claims 2019]
LEFT JOIN
[BCBS DRG CARVE OUT 07012016] ON [GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG
WHERE
[GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG
LEFT JOIN
BCBS_DRG_REIMBURS ON [GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code
WHERE
[GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code
Upvotes: 1
Views: 78
Reputation: 24633
when you join , there is condition that you join on, there is no need for where clause , unless there is a reason, however in you case you need inner join not outer join:
SELECT
*
FROM
[GMC Claims 2019]
JOIN BCBS_DRG_REIMBURS
ON [GMC Claims 2019].DRG = BCBS_DRG_REIMBURS.DRG_Code
JOIN [BCBS DRG CARVE OUT 07012016] -- < here maybe it should be left join ?
ON [GMC Claims 2019].DRG = [BCBS DRG CARVE OUT 07012016].DRG
Upvotes: 1
Reputation: 15905
I think inner join between three tables will server your purpose:
select c.drg, b.Per_Diem_High_Trim_Outlier, d.pmt
from [GMC Claims 2019] c
inner join [BCBS_DRG_REIMBURS] b on c.drg = b.drg_code
inner join [BCBS DRG CARVE OUT 07012016] d on d.drg = b.drg_code
Upvotes: 1