T-SQL Query

Question

Hi members,

Could you please help me to find out no of patients served by each service provider
within the next 30 days period from service date
Thank you in advance.

CREATE TABLE Test (
PatientId VARCHAR(15) ,
ProvidersName VARCHAR(15),
ServiceDate DATETIME)

INSERT INTO TEST
VALUES
(101,'Sam','6/17/2012'),
(102,'Sam','8/15/2012'),
(103,'Sam','9/11/2012'),
(104,'Sam','10/15/2012'),
(105,'Robin','4/5/2012'),
(106,'Robin','4/6/2012'),
(107,'Robin','5/1/2012'),
(108,'Robin','5/9/2012'),
(109,'Robin','7/5/2013'),
(110,'David','1/4/2013'),
(111,'David','1/8/2013'),
(112,'David','1/25/2013'),
(113,'Brenda','2/6/2010'),
(114,'Brenda','2/5/2013'),
(115,'Brenda','12/6/2013')

SELECT * FROM TEST
ORDER BY patientid,SERVICEDate

Result I need.
ProvidersName SeviceDate ThirtyDaysFromServiceDate Total Patients
Sam 6/17/2012 7/17/2012 1
sam 8/15/2012 9/14/2012 2
Sam 10/15/2012 11/14/2012 1
Robin 4/5/2012 5/5/2012 3
Robin 5/9/2012 6/8/2012 1
Robin 7/5/2013 8/4/2013 1
David 1/4/2013 2/3/2013 3
Brenda 2/6/2010 3/7/2010 2
Brenda 12/6/2013 1/5/2014 1
15

Answer 1

Hi @David Sampat ,

Welcome to Microsoft Q&A!

After checking, there is another duplicate post with same requirenment. You could consider to delete one of them if necessary.

After checking your sample data and expected output, there were some issues when the ProvidersName is Brenda. Please help check again.

I made some modifications on the sample data and please refer below:

drop table if exists test  
  
CREATE TABLE Test (  
PatientId VARCHAR(15) ,  
ProvidersName VARCHAR(15),  
ServiceDate DATETIME)  
  
INSERT INTO TEST  
VALUES  
(101,'Sam','6/17/2012'),  
(102,'Sam','8/15/2012'),  
(103,'Sam','9/11/2012'),  
(104,'Sam','10/15/2012'),  
(105,'Robin','4/5/2012'),  
(106,'Robin','4/6/2012'),  
(107,'Robin','5/1/2012'),  
(108,'Robin','5/9/2012'),  
(109,'Robin','7/5/2013'),  
(110,'David','1/4/2013'),  
(111,'David','1/8/2013'),  
(112,'David','1/25/2013'),  
(113,'Brenda','2/6/2010'),  
(114,'Brenda','3/5/2010'),  
(115,'Brenda','12/6/2013')  
  
;with cte as (  
SELECT PatientId,ProvidersName,ServiceDate  
,DATEADD(D,30,ServiceDate) ThirtyDaysFromServiceDate  
FROM TEST  
),cte1 as (  
select *,lag(ThirtyDaysFromServiceDate) over (partition by ProvidersName order by PatientId ,SERVICEDate) Thirtyepre  
,lag(ThirtyDaysFromServiceDate,2) over (partition by ProvidersName order by PatientId ,SERVICEDate) Thirtypre2  
,lag(ThirtyDaysFromServiceDate,3) over (partition by ProvidersName order by PatientId ,SERVICEDate) Thirtypre3  
 from cte)  
 ,cte2 as (  
 select PatientId,ProvidersName,ServiceDate,ThirtyDaysFromServiceDate,  
 case when Thirtypre3 is null and ServiceDate<Thirtypre2 then Thirtypre2  
 when Thirtypre2 is null and  ServiceDate<Thirtyepre then Thirtyepre  
  when Thirtypre3 is null and ServiceDate>Thirtypre2 and  ServiceDate<Thirtyepre then Thirtyepre   
 else ThirtyDaysFromServiceDate end checkdate  
 from cte1   
 )  
 select ProvidersName,DATEADD(day,-30,checkdate) ServiceDate,checkdate, count(ServiceDate)   [Total Patients]  
 from cte2  
 group by ProvidersName,checkdate  
 ORDER BY ProvidersName  

Output:

ProvidersName ServiceDate checkdate Total Patients  
Brenda 2010-02-06 00:00:00.000 2010-03-08 00:00:00.000 2  
Brenda 2013-12-06 00:00:00.000 2014-01-05 00:00:00.000 1  
David 2013-01-04 00:00:00.000 2013-02-03 00:00:00.000 3  
Robin 2012-04-05 00:00:00.000 2012-05-05 00:00:00.000 3  
Robin 2012-05-09 00:00:00.000 2012-06-08 00:00:00.000 1  
Robin 2013-07-05 00:00:00.000 2013-08-04 00:00:00.000 1  
Sam 2012-06-17 00:00:00.000 2012-07-17 00:00:00.000 1  
Sam 2012-08-15 00:00:00.000 2012-09-14 00:00:00.000 2  
Sam 2012-10-15 00:00:00.000 2012-11-14 00:00:00.000 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 2

Check the next query:

; with E as
(
    select *, 
        (
            select count(*) 
            from Test 
            where PatientId <> t.PatientId and 
                ProvidersName = t.ProvidersName and 
                ServiceDate between t.ServiceDate and ThirtyDaysFromServiceDate
        ) cnt
    FROM TEST t
    cross apply (values (dateadd(day, 30, ServiceDate))) d(ThirtyDaysFromServiceDate)
)
select ProvidersName, ServiceDate, ThirtyDaysFromServiceDate, cnt as [Total Patients]
from E 
where cnt <> 0
order by ProvidersName, ServiceDate

However, the shown expected results do not seem clear.