Identifying claims that have Pr codes with different service dates

asked 2021-04-21T11:19:51.563+00:00
Hellothere8028 721 Reputation points

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,455 questions
{count} votes

Accepted answer
  1. answered 2021-04-21T13:01:24.647+00:00
    Viorel 82,286 Reputation points

    Check one of solutions:

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

1 additional answer

Sort by: Most helpful
  1. answered 2021-04-22T02:07:04.993+00:00
    EchoLiu-MSFT 14,416 Reputation points

    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.