SQL Server 2022

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

CREATE TABLE Admission (
    PatientID VARCHAR(100),
    PatientMasterID VARCHAR(100),
    Admission DATE,
    Discharge DATE,
    LevelOfCare INT,
    DischargeDate DATE
)
INSERT Admission
VALUES
    ('1234-5', 'aBcDD-Ecnad9-09as', '2022-09-01', '2022-10-01', 3, '2022-10-01'),
    ('123-6', 'aBcDD-Ecnad9-09as', '2022-10-01', '2022-10-31', 2, '2022-10-31'),
    ('123-7', 'aBcDD-Ecnad9-09as', '2022-12-01', '2022-12-02', 2, '2022-12-02'),
    ('123-8', 'aBcDD-Ecnad9-09as', '2022-12-03', '2022-12-04', 3, '2022-12-04'),
    ('987-1', 'xxxxx-yyyyyy-zzzz', '2022-09-01', '2022-10-01', 1, '2022-10-01'),
    ('987-2', 'xxxxx-yyyyyy-zzzz', '2022-10-01', NULL,        1, NULL)

SELECT
    PatientID, PatientMasterID, Admission, Discharge, LevelOfCare,
    CASE
        WHEN Discharge IS NULL THEN 'Still here?'
        WHEN NextAdmission IS NULL THEN 'True Discharge'
        WHEN DATEDIFF(day, Discharge, NextAdmission) >= 2 THEN 'Gone for now, but they''ll be back'
        WHEN NextLevelOfCare < LevelOfCare THEN 'Stepdown'
        WHEN NextLevelOfCare > LevelOfCare THEN 'Step-Up'
        WHEN NextLevelOfCare = LevelOfCare THEN 'Lateral Transfer'
        END AS DischargeType,
    AdmissionsBefore, AdmissionsAfter, NextAdmission, NextLevelOfCare
FROM (
    SELECT
        PatientID, PatientMasterID, Admission, Discharge, LevelOfCare,
        COUNT(*) OVER(
            PARTITION BY PatientMasterID
            ORDER BY Admission
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ) AS AdmissionsBefore,
        COUNT(*) OVER(
            PARTITION BY PatientMasterID
            ORDER BY Admission
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
            ) AS AdmissionsAfter,
        LEAD(Admission) OVER(PARTITION BY PatientMasterID ORDER BY Admission) AS NextAdmission,
        LEAD(LevelOfCare) OVER(PARTITION BY PatientMasterID ORDER BY Admission) AS NextLevelOfCare
    FROM Admission
) A
ORDER BY PatientMasterID, Admission

PatientID	PatientMasterID	Admission	Discharge	LevelOfCare	DischargeType	AdmissionsBefore	AdmissionsAfter	NextAdmission	NextLevelOfCare
1234-5	aBcDD-Ecnad9-09as	2022-09-01	2022-10-01	3	Stepdown	0	3	2022-10-01	2
123-6	aBcDD-Ecnad9-09as	2022-10-01	2022-10-31	2	Gone for now, but they'll be back	1	2	2022-12-01	2
123-7	aBcDD-Ecnad9-09as	2022-12-01	2022-12-02	2	Step-Up	2	1	2022-12-03	3
123-8	aBcDD-Ecnad9-09as	2022-12-03	2022-12-04	3	True Discharge	3	0	null	null
987-1	xxxxx-yyyyyy-zzzz	2022-09-01	2022-10-01	1	Lateral Transfer	0	1	2022-10-01	1
987-2	xxxxx-yyyyyy-zzzz	2022-10-01	null	1	Still here?	1	0	null	null