question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ArunChandramouli-6978 commented

Identifying claims that have Pr codes with different service dates

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-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please provide any update? Thanks.

Best regards
Echo

0 Votes 0 ·

@EchoLiu-msft : Apologies for my late response!.. Really appreciate your help!..Yes I did find the answer to my question from your response and Viorel-1 response..Thank you so much!

0 Votes 0 ·
Viorel-1 avatar image
1 Vote"
Viorel-1 answered ArunChandramouli-6978 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @Viorel-1 ...Really appreciate your help!

0 Votes 0 ·
EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @EchoLiu-msft ...Appreciate your help!

0 Votes 0 ·