-1

I'm new in SQL Server learner and I'm stuck in simple problem to count the admission using SQL Server in healthcare data set.

Sample table:

create table admissions
(
HIC char(1),
DateFrom date,
DateTo date 
);

Test data:

insert into admissions values('A','2018-01-19','2018-01-19');
insert into admissions values('A','2018-01-19','2018-01-19');
insert into admissions values('A','2018-07-13','2018-07-13');
insert into admissions values('A','2018-05-09','2018-05-09');
insert into admissions values('A','2018-09-25','2018-09-25');
insert into admissions values('A','2018-10-05','2018-10-05');
insert into admissions values('A','2018-10-28','2018-10-28');
insert into admissions values('B','2018-10-05','2018-10-05');
insert into admissions values('B','2018-10-05','2018-10-05');
insert into admissions values('C','2018-01-19','2018-01-19');
insert into admissions values('D','2018-01-19','2018-01-19');

enter image description here

So for Patient A, admitted twice in on same day, so we will count 1 admissions on 1/19/2019 and rest other will be calculated as unique count.

  • Is column E supposed to be the number of admissions so far in that day? So 1,2,1,1,1,1,1,1,2,1,1? Or something else? You're not clear – Philᵀᴹ Mar 28 at 15:41
  • Yes, I need to calculate the Admission count for millions of record – Avinash Kumar Mar 28 at 15:43
  • Oh, sorry, I understand now – Philᵀᴹ Mar 28 at 15:49
  • Admission count per patient, per day, or total? Please provide sample output. – Tony Hinkle Mar 28 at 15:50
  • Admission count per patient per day. So, In the column D, i have manually calculated Admission count. For Pt. A, admitted twice on same day (01/19/2019) but i will count 1 and for rest of day i will count as individual count since the date of admission is different from consecutive rows. For Pt B, Admitted twice on same day, I will count it as 1 admit and other row will be 0 I hope it clear my objective – Avinash Kumar Mar 28 at 15:55
0

This produces the exact output you wanted:

with data as ( select HIC, DateFrom, DateTo, 
row_number() over (partition by HIC, DateFrom order by HIC) as AdmissionRunningTotal,
SUM(1) over (partition by HIC, DateFrom order by HIC) as AdmissionSum
from admissions
)
select HIC, DateFrom, case when AdmissionSum = AdmissionRunningTotal 
                           then 1 else 0 
                      end as AdmissionCount  
from data 
order by HIC, DateFrom, 3 desc;

There are other ways of doing this, this was just easiest for me.

Obviously a real application would want to check overlapping data ranges etc.

DB Fiddle Link

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.