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
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
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
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..
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
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')
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
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
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
.
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.
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
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
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..
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.
15 people are following this question.
Year and Month aggregation in same Pivot table in SQL Server
SQL Server Query for Searching by word in a string with word breakers
How to show first row group by part id and compliance type based on priorities of Document type?
Query to list all the databases that have a specific user
T-sql query to find the biggest table in a database with a clustered index