MySQL 8.0

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

select version();


version()


8.0.26


CREATE TABLE billing_start
(
  billing_start_date DATE NOT NULL,
  id                 SMALLINT NOT NULL,
  status             VARCHAR (25) NOT NULL
);
INSERT INTO billing_start VALUES
('2020-07-27', 100, 'active'),
('2018-12-27', 101, 'inactive');
CREATE TABLE period_end
(
  period_end_date DATE NOT NULL,
  id              SMALLINT UNSIGNED NOT NULL,
  code_a          TINYINT  UNSIGNED NOT NULL,
  code_b          TINYINT  UNSIGNED NOT NULL
);
INSERT INTO period_end VALUES
('2018-12-21', 	100, 	4, 	5),
('2018-12-28', 	100, 	2, 	3),
('2020-07-24', 	101, 	3, 	4),
('2020-07-31', 	101, 	2, 	3);
SELECT 
  b.billing_start_date AS bsd, b.id AS bid, b.status,
  ABS(p.period_end_date - b.billing_start_date) AS diff
FROM billing_start b
JOIN period_end    p
  ON b.id = p.id;


bsd
bid
status
diff


2020-07-27
100
active
19506

2020-07-27
100
active
19499

2018-12-27
101
inactive
19497

2018-12-27
101
inactive
19504


SELECT bsd, bid, bstat, MIN(diff) AS mdiff
FROM
(
  SELECT 
    b.billing_start_date AS bsd, b.id AS bid, b.status AS bstat,
    ABS(p.period_end_date - b.billing_start_date) AS diff
  FROM billing_start b
  JOIN period_end    p
    ON b.id = p.id  
) AS tab1
GROUP BY bsd, bid, bstat;


bsd
bid
bstat
mdiff


2020-07-27
100
active
19499

2018-12-27
101
inactive
19497


SELECT 
  t.bsd, t.bid, t.bstat, p.period_end_date, p.id, mdiff,
  p.period_end_date - t.bsd, p.code_a, p.code_b
FROM
(
  SELECT bsd, bid, bstat, MIN(diff) AS mdiff
  FROM
  (
    SELECT 
      b.billing_start_date AS bsd, b.id AS bid, b.status AS bstat,
      ABS(p.period_end_date - b.billing_start_date) AS diff
    FROM billing_start b
    JOIN period_end    p
      ON b.id = p.id  
  ) AS tab1
  GROUP BY bsd, bid, bstat
) AS t
JOIN period_end p
  ON  t.bid = p.id
  WHERE ABS(p.period_end_date - t.bsd) = ABS(mdiff)
ORDER BY t.bid;


bsd
bid
bstat
period_end_date
id
mdiff
p.period_end_date - t.bsd
code_a
code_b


2020-07-27
100
active
2018-12-28
100
19499
-19499
2
3

2018-12-27
101
inactive
2020-07-24
101
19497
19497
3
4

bsd	bid	status	diff
2020-07-27	100	active	19506
2020-07-27	100	active	19499
2018-12-27	101	inactive	19497
2018-12-27	101	inactive	19504

bsd	bid	bstat	period_end_date	id	mdiff	p.period_end_date - t.bsd	code_a	code_b
2020-07-27	100	active	2018-12-28	100	19499	-19499	2	3
2018-12-27	101	inactive	2020-07-24	101	19497	19497	3	4