SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table admissions
(
HIC char(1),
DateFrom date,
DateTo date 
);
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');

11 rows affected

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  


HIC
DateFrom
AdmissionCount


A
2018-01-19
1

A
2018-01-19
0

A
2018-05-09
1

A
2018-07-13
1

A
2018-09-25
1

A
2018-10-05
1

A
2018-10-28
1

B
2018-10-05
1

B
2018-10-05
0

C
2018-01-19
1

D
2018-01-19
1

HIC	DateFrom	AdmissionCount
A	2018-01-19	1
A	2018-01-19	0
A	2018-05-09	1
A	2018-07-13	1
A	2018-09-25	1
A	2018-10-05	1
A	2018-10-28	1
B	2018-10-05	1
B	2018-10-05	0
C	2018-01-19	1
D	2018-01-19	1