By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SET DATEFORMAT MDY;
CREATE TABLE Patient (
PatientID int,
DischargeDate DATE,
RxDate DATE,
DaysSupply int
);
INSERT INTO Patient (PatientID, DischargeDate, RxDate, DaysSupply) VALUES
('1', '1/1/2025', '1/2/2025', '3'),
('2', '1/3/2025', '1/3/2025', '5'),
('3', '1/3/2025', '2/4/2025', '10');
SELECT * FROM Patient
| PatientID | DischargeDate | RxDate | DaysSupply |
|---|---|---|---|
| 1 | 2025-01-01 | 2025-01-02 | 3 |
| 2 | 2025-01-03 | 2025-01-03 | 5 |
| 3 | 2025-01-03 | 2025-02-04 | 10 |
SELECT
p.PatientID,
p.DischargeDate,
DATEADD(day, g.value, p.RxDate) AS RxDate
FROM Patient p
CROSS APPLY GENERATE_SERIES(0, p.DaysSupply - 1) g
WHERE DATEADD(day, 30, p.DischargeDate) > p.RxDate;
| PatientID | DischargeDate | RxDate |
|---|---|---|
| 1 | 2025-01-01 | 2025-01-02 |
| 1 | 2025-01-01 | 2025-01-03 |
| 1 | 2025-01-01 | 2025-01-04 |
| 2 | 2025-01-03 | 2025-01-03 |
| 2 | 2025-01-03 | 2025-01-04 |
| 2 | 2025-01-03 | 2025-01-05 |
| 2 | 2025-01-03 | 2025-01-06 |
| 2 | 2025-01-03 | 2025-01-07 |
SELECT
p.PatientID,
p.DischargeDate,
p.RxDate,
p.DaysSupply,
CASE WHEN p.DaysSupply >= 01 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day01,
CASE WHEN p.DaysSupply >= 02 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day02,
CASE WHEN p.DaysSupply >= 03 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day03,
CASE WHEN p.DaysSupply >= 04 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day04,
CASE WHEN p.DaysSupply >= 05 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day05,
CASE WHEN p.DaysSupply >= 06 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day06,
CASE WHEN p.DaysSupply >= 07 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day07,
CASE WHEN p.DaysSupply >= 08 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day08,
CASE WHEN p.DaysSupply >= 09 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day09,
CASE WHEN p.DaysSupply >= 10 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day10,
CASE WHEN p.DaysSupply >= 11 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day11,
CASE WHEN p.DaysSupply >= 12 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day12,
CASE WHEN p.DaysSupply >= 13 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day13,
CASE WHEN p.DaysSupply >= 14 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day14,
CASE WHEN p.DaysSupply >= 15 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day15,
CASE WHEN p.DaysSupply >= 16 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day16,
CASE WHEN p.DaysSupply >= 17 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day17,
CASE WHEN p.DaysSupply >= 18 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day18,
CASE WHEN p.DaysSupply >= 19 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day19,
CASE WHEN p.DaysSupply >= 20 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day20,
CASE WHEN p.DaysSupply >= 21 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day21,
CASE WHEN p.DaysSupply >= 22 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day22,
CASE WHEN p.DaysSupply >= 23 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day23,
CASE WHEN p.DaysSupply >= 24 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day24,
CASE WHEN p.DaysSupply >= 25 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day25,
CASE WHEN p.DaysSupply >= 26 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day26,
CASE WHEN p.DaysSupply >= 27 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day27,
CASE WHEN p.DaysSupply >= 28 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day28,
CASE WHEN p.DaysSupply >= 29 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day29,
CASE WHEN p.DaysSupply >= 30 THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END AS Day30
FROM Patient p
| PatientID | DischargeDate | RxDate | DaysSupply | Day01 | Day02 | Day03 | Day04 | Day05 | Day06 | Day07 | Day08 | Day09 | Day10 | Day11 | Day12 | Day13 | Day14 | Day15 | Day16 | Day17 | Day18 | Day19 | Day20 | Day21 | Day22 | Day23 | Day24 | Day25 | Day26 | Day27 | Day28 | Day29 | Day30 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2025-01-01 | 2025-01-02 | 3 | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |
| 2 | 2025-01-03 | 2025-01-03 | 5 | True | True | True | True | True | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False | False |