count claims based on age range

Question

i have 2 table

table 1
claimstable

id claimnumber claimdate
1 12345 2021-03-25

table 2
org

id idname
1 1x234

ilke these with no. of records in each table.

i need count for claims based on age range for 20-40, 40-60,60-80

Answer 1

create table claimstable
id int ,
claimnumber varchar(10),
claimstate varchar(20),
claimsdate date
insert into cliaimtable (id ,claimnumber ,claimstate,claimsdate )
values(1,'12345','sent','2021-03-25'),
values(2,'12346','approval','2021-03-15')

create table org
id int ,
idname varchar(10)
insert into org(id,idname )
values(1,'bsbd')

Answer 2

hi @MelissaMa-MSFT ..
here it is sample data rfor both tables
insert into cliaimtable (id ,claimnumber ,claimstate,claimsdate )
values (8,' CDX20-221708' ,'Patient Statements Prepared',' 2020-08-29'),
values(8, 'CDX20-221707',' Ready for Patient Statements', '2020-08-29 '),
values(8, 'THX20-050567', 'Claim Status Requested', '2020-08-30'),
values(8, 'THX20-050566'',' Claim Status Requested', '2020-08-30'),
values(8 ,'CP0G002823', 'Exception', '2020-08-29 '),
values(8, 'CP0G002813', 'Exception ,'2020-08-29 '),
values(8, 'CP0G002814 ','Exception ,'2020-08-29 '),
values(8, 'MML20-003758', 'Accepted, '2020-08-29 '),
values(8 ,'CP0G002817', 'Exception, '2020-08-29 '),
values(8, 'CP0G002818 ','Exception ,'2020-08-29 ')
insert into org(id,idname )
values(-100,' System')
values(-1,' ALL ORGANIZATIONS')
values(1 ,'STARMARK SERVICES PVT. LTD.')
values(2,' ORDERING HOSPITAL')
values(3,' SPECIMEN LABORATORY')
values(4,' ORDERING FACILITY')
values(5,' LAB ASSOCIATES')
values(6,'CYTOMATICS INC.')
values(7,' SPECIALITY HISTOMATICS INC.')
values(8,' THERANOSTIX INC.')

id is relation between two tables

Answer 3

A tip: when you are asked to supply CREATE TABLE + INSERT statements, you should also test the script before you post it, so that the person answering your post, does not have to spend time on cleaning it up.

Since you did not post the expected results, I will have to make a guess of what you are asking for But try this:

; WITH intervals AS (
    SELECT id, datediff(DAY, claimsdate, sysdatetime()) / 20 * 20  AS intervalstart,
               (datediff(DAY, claimsdate, sysdatetime()) / 20  + 1) * 20 - 1 AS intervalend
    FROM   claimstable
)
SELECT org.idname, i.intervalstart, i.intervalend, COUNT(*)
FROM   org
JOIN   intervals i ON org.id = i.id
GROUP  BY org.idname, i.intervalstart, i.intervalend

.

Answer 4

Hi @thejesh 00 ,

Please provide your expected output to avoid guessing.

Besides, there was no column related with birthday or other day since you mentioned 'age range for 20-40, 40-60,60-80'.

Please refer below query which is count the claimnumber based on claimsdate and claimstate and hope it could be helpful to you.

select a.id,a.idname,trim(claimstate) claimstate,claimsdate,count(trim(b.claimnumber)) count  
from org a  
left join claimstable b on a.id=b.id   
group by a.id,a.idname,trim(claimstate),claimsdate  

Output:

id	idname	claimstate	claimsdate	count  
-100	 System	NULL	NULL	0  
-1	 ALL ORGANIZATIONS	NULL	NULL	0  
1	STARMARK SERVICES PVT. LTD.	NULL	NULL	0  
2	 ORDERING HOSPITAL	NULL	NULL	0  
3	 SPECIMEN LABORATORY	NULL	NULL	0  
4	 ORDERING FACILITY	NULL	NULL	0  
5	 LAB ASSOCIATES	NULL	NULL	0  
6	CYTOMATICS INC.	NULL	NULL	0  
7	 SPECIALITY HISTOMATICS INC.	NULL	NULL	0  
8	 THERANOSTIX INC.	Accepted	2020-08-29	1  
8	 THERANOSTIX INC.	Claim Status Requested	2020-08-30	2  
8	 THERANOSTIX INC.	Exception	2020-08-29	5  
8	 THERANOSTIX INC.	Patient Statements Prepared	2020-08-29	1  
8	 THERANOSTIX INC.	Ready for Patient Statements	2020-08-29	1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @thejesh 00 ,

I modified some sample data and you could refer below query and check whether it is helpful to you.

create table claimstable  
(id int ,  
claimnumber varchar(20),  
claimstate varchar(100),  
claimsdate date)  

insert into claimstable (id ,claimnumber ,claimstate,claimsdate )  
values (8,' CDX20-221708' ,'Patient Statements Prepared',' 2021-04-29'),  
(8, 'CDX20-221707',' Ready for Patient Statements', '2021-04-13 '),  
(8, 'THX20-050567', 'Claim Status Requested', '2021-02-06'),  
(8, 'THX20-050566',' Claim Status Requested', '2021-01-31'),  
(8 ,'CP0G002823', 'Exception', '2021-03-29 '),  
(8, 'CP0G002813', 'Exception' ,'2021-03-26 '),  
(8, 'CP0G002814 ','Exception' ,'2021-02-23 '),  
(8, 'MML20-003758', 'Accepted', '2021-04-29 '),  
(8 ,'CP0G002817', 'Exception', '2021-02-15 '),  
(8, 'CP0G002818 ','Exception' ,'2021-01-19 ')  

;with cte as (  
select b.idname,a.claimnumber,a.claimstate,DATEDIFF(DD,claimsdate,GETDATE()) range   
from claimstable a   
inner join org b on a.id=b.id)  
select idname,claimstate,'0-20 days' ageroup ,count(claimnumber) count  
from cte   
where range>=0 and range<20  
group by idname,claimstate  
union  
select idname,claimstate,'20-40 days' ageroup ,count(claimnumber) count  
from cte   
where range>=20 and range<40  
group by idname,claimstate  
union  
select idname,claimstate,'40-60 days' ageroup ,count(claimnumber) count  
from cte   
where range>=40 and range<60  
group by idname,claimstate  
union  
select idname,claimstate,'60-80 days' ageroup ,count(claimnumber) count  
from cte   
where range>=60 and range<80  
group by idname,claimstate  
union  
select idname,claimstate,'80-100 days' ageroup ,count(claimnumber) count  
from cte   
where range>=80 and range<100  
group by idname,claimstate  
union  
select idname,claimstate,'100-120 days' ageroup ,count(claimnumber) count  
from cte   
where range>=100 and range<120  
group by idname,claimstate  

Output:

idname	claimstate	ageroup	count  
 THERANOSTIX INC.	 Claim Status Requested	100-120 days	1  
 THERANOSTIX INC.	 Ready for Patient Statements	20-40 days	1  
 THERANOSTIX INC.	Accepted	0-20 days	1  
 THERANOSTIX INC.	Claim Status Requested	80-100 days	1  
 THERANOSTIX INC.	Exception	100-120 days	1  
 THERANOSTIX INC.	Exception	40-60 days	2  
 THERANOSTIX INC.	Exception	80-100 days	2  
 THERANOSTIX INC.	Patient Statements Prepared	0-20 days	1  

If above is not working, please provide your updated sample data and expected output.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.