# SQL Admission Count using row_number

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

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

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