Identifying claims that have Pr codes with different service dates

Question

Hi Team,

Hope you are doing well!..I am trying to identify the claims that have prcode's that have multiple from and to dates (meaning the dates of service)..Ideally a claim would have single /multiple pr codes and would have the same from and to dates for all them..

So I am trying to identifying claims for which 1) Multiple pr codes have different from and to date for the same claim 2) Single pr code have multiple from and to date for the same claim..

Please find below the DDL for the input and the output tables:

Input table

create table ##input
(ctextid int,
claimid int,
prcode varchar(100),
fromdate date,
todate date)

insert into ##input values
('11211',I2'123','ui90','01/28/2020','01/28/2020'),
('11211','123','op89','01/28/2020','01/28/2020'),
('11211','123','io12','01/28/2020','01/28/2020'),
('11211','567','jk89','04/28/2020','04/28/2020'),
('11211','567','hj32','04/25/2020','04/26/2020'),
('11211','567','jk12','04/28/2020','04/29/2020'),
('89121','612','hb34','01/14/2020','01/15/2020'),
('89121','612','hb34','01/15/2020','01/17/2020'),
('56712','344','cv12','01/12/2020','01/13/2020'),
('56712','344','ghj23','01/12/2020','01/13/2020'),
('56712','344','vb167','01/13/2020','01/16/2020')

output table

create table ##output
(ctextid int,
claimid int)

insert into ##output values
('11211','567'),
('89121','612'),
('56712','344')

Thanks,
Arun

Answer 1

Check one of solutions:

select ctextid, claimid
from (select distinct ctextid, claimid, fromdate, todate from ##input) t
group by ctextid, claimid
having count(*) > 1

Answer 2

Hi @Hellothere8028 ,

Please also check:

create table ##input  
(ctextid int,  
claimid int,  
prcode varchar(100),  
fromdate date,  
todate date)  
insert into ##input values  
(11211,123,'ui90','01/28/2020','01/28/2020'),  
(11211,123,'op89','01/28/2020','01/28/2020'),  
(11211,123,'io12','01/28/2020','01/28/2020'),  
(11211,567,'jk89','04/28/2020','04/28/2020'),  
(11211,567,'hj32','04/25/2020','04/26/2020'),  
(11211,567,'jk12','04/28/2020','04/29/2020'),  
(89121,612,'hb34','01/14/2020','01/15/2020'),  
(89121,612,'hb34','01/15/2020','01/17/2020'),  
(56712,344,'cv12','01/12/2020','01/13/2020'),  
(56712,344,'ghj23','01/12/2020','01/13/2020'),  
(56712,344,'vb167','01/13/2020','01/16/2020')  


select distinct ctextid, claimid  
from(select distinct ctextid, claimid, fromdate, todate from ##input) t  
where claimid in   
(case when nullif(fromdate,todate) is null then null else claimid end)  

Or:

select ctextid,claimid  
from(select *,rank() over(partition by ctextid,claimid order by fromdate, todate)rr  
     from ##input) t  
where claimid in   
(case when nullif(fromdate,todate) is null then null else claimid end)  
and rr>1    

Output:

    ctextid, claimid  
    56712 344  
    11211 567  
    89121 612  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.