question

thejesh00-2909 avatar image
0 Votes"
thejesh00-2909 asked MelissaMa-msft commented

count claims based on age range

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

sql-server-generalsql-server-transact-sql
· 3
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.

Hi @thejesh00-2909,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with some sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards
Melissa

1 Vote 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')








assume more records with different dates .. hope this helps you..

0 Votes 0 ·

Hi @thejesh00-2909,

Could you please validate the latest answer so far and provide any update?

If it is not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
thejesh00-2909 avatar image
0 Votes"
thejesh00-2909 answered MelissaMa-msft edited

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')

· 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.

Hi @thejesh00-2909,

Thanks for your update about DDL.

Please also provide more sample data, the relationship with two tables, more details about 'count for claims based on age range for 20-40, 40-60,60-80' and the expected output based on the sample data. Thanks.

Best regards
Melissa

0 Votes 0 ·
thejesh00-2909 avatar image
0 Votes"
thejesh00-2909 answered thejesh00-2909 published

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

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.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

.

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.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ErlandSommarskog commented

Hi @thejesh00-2909,

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.

· 4
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.

hi @MelissaMa-msft i want age for claimdates itself with range of 20-40 days , 40-60 days etc.,



expected out put is


idname calimstate ageroup count
any any 20-40 days ( count of claims in between the range days for that claimstate

0 Votes 0 ·

Hi @thejesh00-2909,

Thanks for your update.

Could you please provide your expected output according to your sample data provided?

There are only '2020-08-29' and '2020-08-30' in claimsdate dates and I have no idea how to age.

Based on the min of claimsdate , one specific date or today?

Maybe more sample data with different claimsdate could be helpful for us to understand you better.

Thanks.

Best regards
Melissa

0 Votes 0 ·

u can change the date to different dates such as 2021 to 2021 and month to any . and datediff (dd,claimsdate,getdate()) based on this we have to segregate the data ... sorry if i am confusing u .. i am new to this kind of sites..

0 Votes 0 ·
Show more comments
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @thejesh00-2909,

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.

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.