hi
hope this helps
create tables
-- People table
CREATE TABLE People (
people_id INT PRIMARY KEY,
full_name VARCHAR(100)
);
-- Event log table
CREATE TABLE event_view (
event_log_id INT PRIMARY KEY,
people_id INT,
event_name VARCHAR(100),
actual_date DATE,
expiration_date DATE,
generic_remarks VARCHAR(200),
date_entered DATE,
user_entered_desc VARCHAR(100)
);
-- Progress notes table
CREATE TABLE progress_note_view (
progress_note_id INT PRIMARY KEY,
event_log_id INT,
progress_note_text VARCHAR(200)
);
-- Program enrollment table
CREATE TABLE rpt_critical_info_view (
program_info_id INT PRIMARY KEY,
people_id INT,
program_name VARCHAR(100),
program_start_date DATE,
program_end_date DATE
);
-- Programs metadata
CREATE TABLE programs_view (
program_info_id INT PRIMARY KEY,
is_carday BIT
);
insert data
-- People
INSERT INTO People VALUES
(1, 'John Doe');
-- Events (consents)
INSERT INTO event_view VALUES
(101, 1, 'Consent for Treatment', '2024-02-10', '2025-02-10', 'Initial consent', '2024-02-10', 'Admin'),
(102, 1, 'Medical Consent', '2024-07-31', '2025-07-31', 'Follow-up consent', '2024-07-31', 'Admin');
-- Progress notes
INSERT INTO progress_note_view VALUES
(201, 101, 'Consent signed with remarks'),
(202, 102, 'Medical consent signed');
-- Programs
-- Scenario 1: Program ended before consent (should not match)
INSERT INTO rpt_critical_info_view VALUES
(301, 1, 'FFC', '2024-02-07', '2024-02-14');
-- Scenario 2: Program overlaps consent (should match)
INSERT INTO rpt_critical_info_view VALUES
(302, 1, 'TFFC', '2024-02-14', NULL);
-- Scenario 3: Multiple programs overlap, one ended, one current
INSERT INTO rpt_critical_info_view VALUES
(303, 1, 'ABC', '2024-07-01', '2024-07-30'),
(304, 1, 'XYZ', '2024-07-30', NULL);
-- Programs metadata
INSERT INTO programs_view VALUES
(301, 1),
(302, 1),
(303, 1),
(304, 1);
t-sql
;WITH ProgramMatches AS (
SELECT
ev.event_log_id,
ev.event_name,
ev.full_name,
ev.actual_date AS Received_Date,
ev.expiration_date,
ev.generic_remarks AS Remarks,
rpt.program_name,
ev.date_entered,
ev.user_entered_desc,
ROW_NUMBER() OVER (
PARTITION BY ev.event_log_id
ORDER BY
CASE WHEN rpt.program_end_date IS NULL THEN 0 ELSE 1 END,
rpt.program_start_date DESC
) AS rn
FROM event_view ev
LEFT JOIN progress_note_view pnv
ON ev.event_log_id = pnv.event_log_id
INNER JOIN rpt_critical_info_view rpt
ON ev.people_id = rpt.people_id
AND ev.actual_date >= rpt.program_start_date
AND (rpt.program_end_date IS NULL OR ev.actual_date <= rpt.program_end_date)
LEFT JOIN programs_view pv
ON rpt.program_info_id = pv.program_info_id
WHERE ev.event_name IN ('Consent for Treatment', 'Medical Consent')
AND pv.is_carday = 1
)
SELECT event_name, full_name, Received_Date, program_name
FROM ProgramMatches
WHERE rn = 1;
result
