CODEWITHSUNDEEP
sqlsql-serverjoin
Crow
Crow

Reputation: 43

Joining two Rows from the same tables

Using SQL Server, I would like to join the following two queries to create one row where a patient has had both of these codes (80048 and 80053) on the same date of service. If easier, I could just pull both rows (the row with 80048, and the row with 80053)

(Select PatLname + PatFname + cast(PatBdate as varchar(50)) as "ID", c.HCPCS as "HCPCS", c.SvDate
From Claim as a 
left join claimDtl as c on a.claim_id = c.claimDtl_id
Where
c.HcPcS = 80048
and c.SvDate between '10-01-2016' and '11-01-2016'
group by PatLname, PatFname, PatBdate, c.HCPCS,c.SvDate) Tbl1

Inner Join

(Select PatLname + PatFname + cast(PatBdate as varchar(50))as "ID", c.HCPCS as "HCPCS", c.SvDate
From claim as a 
left join claimDtl as c on a.claim_id = c.claimDtl_id
Where
c.HcPcS = 80053
and c.SvDate between '10-01-2016' and '11-01-2016'
group by PatLname, PatFname, PatBdate, c.HCPCS, c.SvDate) Tbl2

on Tbl1.ID = Tbl2.ID
where Tbl1.Svdate = Tbl2.SvDate

Upvotes: 0

Views: 111

Answers (4)

Esperento57
Esperento57

Reputation: 17462

  1. if you test a value into table of left join into your where you can replace left outer join by inner join

  2. I suppose your group by is for get distinct row

  3. if you concat value, be carefull if null values, use isnull for this
  4. Use CTE for create autojoin, its better in perfomance in your case

try something like this :

with tmp as (
Select distinct PatLname + PatFname + cast(PatBdate as varchar(50)) as ID, c.HCPCS, c.SvDate
From Claim as a inner join claimDtl as c on a.claim_id = c.claimDtl_id
Where c.HcPcS in ( 80048, 80053)
and c.SvDate between '10-01-2016' and '11-01-2016'
) 
select * from tmp t1 inner join t2 on t1.ID=t2.ID and t1.Svdate=t2.Svdate
where t1.HCPCS=80048 and t2.HCPCS=80053

Upvotes: 0

DRapp
DRapp

Reputation: 48139

This should work by doing a secondary join once the first entry was qualified. Prevents need of aggregate and group by. I assume the patient name/birthdate are in the main claims table with alias "a".

So the JOIN from the claim to the claim detail is on the primary code you are looking for AND it is within the date range.

Once you have that specific finding AND specific date, join one more time to the claim detail for the same claim and date of service, but also on the SECOND code you are looking for.

Select 
      PatLname + PatFname + cast(PatBdate as varchar(50)) as "ID", 
      c.HCPCS as "HCPCS", 
      c2.HcPcS as "HCPCS2",
      c.SvDate
   From 
      Claim as a
         join claimDtl as c 
            on a.claim_id = c.claimDtl_id
            AND c.HcPcS = 80048
            and c.SvDate between '10-01-2016' and '11-01-2016'
            join claimDtl as c2 
               on a.claim_id = c2.claimDtl_id
               AND c2.HcPcS = 80053
               and c.SvDate = c2.SvDate

If someone does not even qualify for the claim 80048, no need to even try for an 80053 code

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Be careful using between with date and datetime values. It is usually better to explicitly break out the between statements into >= and <.

Check out Aaron Bertrand's article Bad habits to kick : mis-handling date / range queries.

common table expression with row_number() version:

with cte as (
  select distinct
      ID = PatLname + PatFname + cast(PatBdate as varchar(50))
    , c.HCPCS
    , c.SvDate
    , rn = row_number() over (partition by PatLname,PatFname,PatBdate,c.SvDate order by c.HCPCS)
    from Claim as a
      left join claimDtl as c on a.claim_id = c.claimDtl_id
    where c.HCPCS in (80048,80053)
      and c.SvDate >= '10-01-2016' 
      and c.SvDate <  '11-01-2016'
    )
select 
    Id
  , Hcpcs
  , SvDate
  from cte
  where rn = 2;

If you don't need to return HCPCS (and I don't know why you would), this could work as well:

select 
    PatLname + PatFname + cast(PatBdate as varchar(50)) as "ID"
  --, c.HCPCS as "HCPCS"
  , UniqueHcpcs = count(Distinct c.HCPCS)
  , c.SvDate
  from Claim as a
    left join claimDtl as c on a.claim_id = c.claimDtl_id
  where c.HCPCS in (80048,80053)
    and c.SvDate >= '10-01-2016' 
    and c.SvDate <  '11-01-2016'
  group by PatLname, PatFname, PatBdate, /*c.HCPCS,*/ c.SvDate
  having UniqueHcpcs >1;

Upvotes: 0

David T. Macknet
David T. Macknet

Reputation: 3162

I think there may be several problems with the approach you've put out there:

  1. You will only return rows where both codes are present in the data set;
  2. You will need to somehow join on the date, I would assume, because what happens if they've been in multiple times with the same codes; unless the date has been sanitized, the dates presented may include a time stamp which would mess up the join.
  3. What happens if they have multiple rows matching your codes?

That said, to answer what you asked (I think):

select distinct Tbl1.ID ,
        Tbl1.SvDate
from
(select     PatLname + PatFname + cast(PatBdate as varchar(50)) as "ID",
        c.SvDate
from        Claim as a 
inner join  claimDtl as c on a.claim_id = c.claimDtl_id
Where   c.HcPcS = 80048) as Tbl1
inner join
(select     PatLname + PatFname + cast(PatBdate as varchar(50)) as "ID",
        c.SvDate
from        Claim as a 
inner join  claimDtl as c on a.claim_id = c.claimDtl_id
Where   c.HcPcS = 80053) as Tbl2
on  Tbl1.ID = Tbl2.ID
and Tbl1.SvDate = Tbl2.SvDate

Upvotes: 1

Related Questions