SQL Server 2016

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

CREATE TABLE billing (
  "bill_id" INTEGER,
  "person_id" INTEGER,
  "bill_date" DATE
);

INSERT INTO billing
  ("bill_id", "person_id", "bill_date")
VALUES
  ('1', '1', '1/1/2020'),
  ('2', '1', '1/2/2020'),
  ('3', '1', '1/3/2020'),
  ('4', '1', '1/4/2020'),
  ('5', '1', '2/4/2020');

CREATE TABLE billing_detail (
  "bill_id" INTEGER,
  "service_type" VARCHAR(1)
);

INSERT INTO billing_detail
  ("bill_id", "service_type")
VALUES
  ('1', 'A'),
  ('2', 'B'),
  ('3', 'A'),
  ('4', 'B'),
  ('5', 'A');
10 rows affected

SELECT person_id, bill_date, service_type,
       ROW_NUMBER() OVER (PARTITION BY person_id, service_type ORDER BY bill_date) - 1 AS prior_services
FROM billing b
JOIN billing_detail bd ON bd.bill_id = b.bill_id


person_id
bill_date
service_type
prior_services


1
2020-01-01
A
0

1
2020-01-03
A
1

1
2020-02-04
A
2

1
2020-01-02
B
0

1
2020-01-04
B
1


SELECT person_id, bill_date, service_type,
       ROW_NUMBER() OVER (PARTITION BY person_id, service_type ORDER BY bill_date) - 1 AS prior_services
FROM billing b
JOIN billing_detail bd ON bd.bill_id = b.bill_id
WHERE service_type = 'A'


person_id
bill_date
service_type
prior_services


1
2020-01-01
A
0

1
2020-01-03
A
1

1
2020-02-04
A
2

person_id	bill_date	service_type	prior_services
1	2020-01-01	A	0
1	2020-01-03	A	1
1	2020-02-04	A	2
1	2020-01-02	B	0
1	2020-01-04	B	1