SQL Server 2019

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

CREATE TABLE T (
  "as_of_date" DATE NOT NUll,
  "issue_id" INTEGER NOT NULL,
  "issue_status_name" VARCHAR(22) NOT NULL,
  "prev_issue_status_name" VARCHAR(22) NULL,
  "desired_output" DATE NULL
);

INSERT INTO T
  ("as_of_date", "issue_id", "issue_status_name", "prev_issue_status_name", "desired_output")
VALUES
  ('12/31/2019', '123456', 'Draft', NULL, NULL),
  ('1/1/2020', '123456', 'Draft', 'Draft', NULL),
  ('1/2/2020', '123456', 'Draft', 'Draft', NULL),
  ('1/3/2020', '123456', 'Pending Validation', 'Draft', '1/3/2020'),
  ('1/4/2020', '123456', 'Pending Validation', 'Pending Validation', '1/3/2020'),
  ('1/5/2020', '123456', 'Pending Validation', 'Pending Validation', '1/3/2020'),
  ('1/6/2020', '123456', 'Pending Validation', 'Pending Validation', '1/3/2020'),
  ('1/7/2020', '123456', 'Open', 'Pending Validation', NULL),
  ('1/8/2020', '123456', 'Open', 'Open', NULL),
  ('1/9/2020', '123456', 'Open', 'Open', NULL),
  ('1/10/2020', '123456', 'Open', 'Open', NULL),
  ('1/11/2020', '123456', 'Pending Sustainability', 'Open', '1/11/2020'),
  ('1/12/2020', '123456', 'Pending Sustainability', 'Pending Sustainability', '1/11/2020'),
  ('1/13/2020', '123456', 'Pending Sustainability', 'Pending Sustainability', '1/11/2020'),
  ('1/14/2020', '123456', 'Pending Sustainability', 'Pending Sustainability', '1/11/2020'),
  ('1/15/2020', '123456', 'Pending Validation', 'Pending Sustainability', '1/11/2020'),
  ('1/16/2020', '123456', 'Pending Validation', 'Pending Validation', '1/11/2020'),
  ('1/17/2020', '123456', 'Pending Validation', 'Pending Validation', '1/11/2020'),
  ('1/18/2020', '123456', 'Pending Validation', 'Pending Validation', '1/11/2020'),
  ('1/19/2020', '123456', 'Pending Validation', 'Pending Validation', '1/11/2020'),
  ('1/20/2020', '123456', 'Pending Validation', 'Pending Validation', '1/11/2020'),
  ('1/21/2020', '123456', 'Open', 'Pending Validation', NULL),
  ('1/22/2020', '123456', 'Open', 'Open', NULL),
  ('1/23/2020', '123456', 'Open', 'Open', NULL),
  ('1/24/2020', '123456', 'Open', 'Open', NULL),
  ('1/25/2020', '123456', 'Open', 'Open', NULL),
  ('1/26/2020', '123456', 'Open', 'Open', NULL),
  ('1/27/2020', '123456', 'Closed', 'Open', '1/27/2020'),
  ('1/28/2020', '123456', 'Closed', 'Closed', '1/27/2020'),
  ('1/29/2020', '123456', 'Closed', 'Closed', '1/27/2020'),
  ('1/30/2020', '123456', 'Closed', 'Closed', '1/27/2020'),
  ('1/31/2020', '123456', 'Closed', 'Closed', '1/27/2020');

32 rows affected

issue_id	as_of_date	issue_status_name	prev_issue_status_name	desired_output	calculated_output
123456	2019-12-31	Draft	null	null	null
123456	2020-01-01	Draft	Draft	null	null
123456	2020-01-02	Draft	Draft	null	null
123456	2020-01-03	Pending Validation	Draft	2020-01-03	2020-01-03
123456	2020-01-04	Pending Validation	Pending Validation	2020-01-03	2020-01-03
123456	2020-01-05	Pending Validation	Pending Validation	2020-01-03	2020-01-03
123456	2020-01-06	Pending Validation	Pending Validation	2020-01-03	2020-01-03
123456	2020-01-07	Open	Pending Validation	null	null
123456	2020-01-08	Open	Open	null	null
123456	2020-01-09	Open	Open	null	null
123456	2020-01-10	Open	Open	null	null
123456	2020-01-11	Pending Sustainability	Open	2020-01-11	2020-01-11
123456	2020-01-12	Pending Sustainability	Pending Sustainability	2020-01-11	2020-01-11
123456	2020-01-13	Pending Sustainability	Pending Sustainability	2020-01-11	2020-01-11
123456	2020-01-14	Pending Sustainability	Pending Sustainability	2020-01-11	2020-01-11
123456	2020-01-15	Pending Validation	Pending Sustainability	2020-01-11	2020-01-11
123456	2020-01-16	Pending Validation	Pending Validation	2020-01-11	2020-01-11
123456	2020-01-17	Pending Validation	Pending Validation	2020-01-11	2020-01-11
123456	2020-01-18	Pending Validation	Pending Validation	2020-01-11	2020-01-11
123456	2020-01-19	Pending Validation	Pending Validation	2020-01-11	2020-01-11
123456	2020-01-20	Pending Validation	Pending Validation	2020-01-11	2020-01-11
123456	2020-01-21	Open	Pending Validation	null	null
123456	2020-01-22	Open	Open	null	null
123456	2020-01-23	Open	Open	null	null
123456	2020-01-24	Open	Open	null	null
123456	2020-01-25	Open	Open	null	null
123456	2020-01-26	Open	Open	null	null
123456	2020-01-27	Closed	Open	2020-01-27	2020-01-27
123456	2020-01-28	Closed	Closed	2020-01-27	2020-01-27
123456	2020-01-29	Closed	Closed	2020-01-27	2020-01-27
123456	2020-01-30	Closed	Closed	2020-01-27	2020-01-27
123456	2020-01-31	Closed	Closed	2020-01-27	2020-01-27