SQL Server 2022

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

CREATE TABLE account 
(
    account_id	VARCHAR(512),
    accountnumber VARCHAR(512)
);

CREATE TABLE service 
(
    service_id	VARCHAR(512),
    servicelocation_id VARCHAR(512)
);

CREATE TABLE servicelocation 
(
    propertynumber	VARCHAR(512),
    propertyname	VARCHAR(512),  
    servicelocation_id VARCHAR(512)
);

CREATE TABLE accountservice 
(
    account_id VARCHAR(512),
    service_id VARCHAR(512),
    FromDate	DATE,
    ToDate	DATE,
    details	VARCHAR(512)    
);

INSERT INTO servicelocation (propertynumber,propertyname,servicelocation_id) VALUES 
  ('00125466','1. 00125466','1'),
  ('10085279','2. 10085279','2'),
  ('00005110','3. 00005110','3');

INSERT INTO service (service_id,servicelocation_id) VALUES 
  ('1','1'),
  ('2','2'),
  ('3','3'),
  ('4','3'),
  ('5','3');

INSERT INTO account (account_id,accountnumber) VALUES 
  ('1','146732'),
  ('2','180887'),  
  ('3','**267110**'),
  ('4','273341');

INSERT INTO accountservice (account_id, service_id, FromDate, ToDate, details) VALUES
	('1','1',  '2/2/2001'  , NULL        , '(no to_date since still living there)'),
	('2','2',  '6/12/1978' , '1/15/2020' , '(moved in and moved out)'),
	('3','3',  '10/15/2020', '12/14/2021', '(live there from Oct. 2020 - Dec. 2021)'),
	('4','4',  '12/14/2021', '12/09/2022', '(live there from Dec. 2021 - Dec. 2022)'),
	('3','5',  '12/09/2022', NULL        , '(moved in Dec. 2022 and still there now)');

SELECT * FROM accountservice

SELECT DISTINCT TOP (100) PERCENT propertynumber, dbo.account.accountnumber, dbo.accountservice.fromdate,dbo.accountservice.todate, 
 ROW_NUMBER() OVER(PARTITION BY dbo.servicelocation.propertynumber+dbo.account.accountnumber ORDER BY FromDate DESC) as OccupationSequence
FROM   dbo.account INNER JOIN
       dbo.accountservice ON dbo.account.account_id = dbo.accountservice.account_id INNER JOIN
       dbo.service ON dbo.accountservice.service_id = dbo.service.service_id INNER JOIN
       dbo.servicelocation ON dbo.service.servicelocation_id = dbo.servicelocation.servicelocation_id

SELECT DISTINCT TOP (100) PERCENT propertynumber, accountnumber, min(fromdate) as MinFrom, max(todate) as MaxTo
FROM 
(
  SELECT DISTINCT TOP (100) PERCENT propertynumber, dbo.account.accountnumber, dbo.accountservice.fromdate,dbo.accountservice.todate,

ROW_NUMBER() OVER(PARTITION BY dbo.servicelocation.propertynumber+dbo.account.accountnumber ORDER BY FromDate DESC) as OccupationSequence
FROM   dbo.account INNER JOIN
       dbo.accountservice ON dbo.account.account_id = dbo.accountservice.account_id INNER JOIN
       dbo.service ON dbo.accountservice.service_id = dbo.service.service_id INNER JOIN
       dbo.servicelocation ON dbo.service.servicelocation_id = dbo.servicelocation.servicelocation_id
) t  
WHERE 
  (propertynumber = '00005110') AND (todate > CONVERT(DATETIME, '2021-01-01 00:00:00', 102)) OR
  (propertynumber = '00005110') AND (todate IS NULL)
GROUP BY  propertynumber,accountnumber,OccupationSequence
ORDER BY min(fromdate) DESC

account_id	service_id	FromDate	ToDate	details
1	1	2001-02-02	null	(no to_date since still living there)
2	2	1978-06-12	2020-01-15	(moved in and moved out)
3	3	2020-10-15	2021-12-14	(live there from Oct. 2020 - Dec. 2021)
4	4	2021-12-14	2022-12-09	(live there from Dec. 2021 - Dec. 2022)
3	5	2022-12-09	null	(moved in Dec. 2022 and still there now)

propertynumber	accountnumber	fromdate	todate	OccupationSequence
00005110	267110	2020-10-15	2021-12-14	2
00005110	267110	2022-12-09	null	1
00005110	273341	2021-12-14	2022-12-09	1
00125466	146732	2001-02-02	null	1
10085279	180887	1978-06-12	2020-01-15	1

propertynumber	accountnumber	MinFrom	MaxTo
00005110	267110	2022-12-09	null
00005110	273341	2021-12-14	2022-12-09
00005110	267110	2020-10-15	2021-12-14

Warning: Null value is eliminated by an aggregate or other SET operation.