Query almost perfect but brings wrong record

1

Data like this. 1. Event_log_view. We have the event_name which we are manually looking for specific consents events. Key is people_id. the consents are for agency clients. We have the actual_date, expired_date, received_date. 2. we have to get one column for some of the consents that have progress_note field this is stored in progress_notes_view by event_log_id they both have. This works ok.

  1. We next have to get the program_name they were enrolled in at the time of the consent. It should be only one program at the same time. But there is overlap. So one program ended today and the other started. Basically the formula is to select a program_name when the start_program_enroll is >= to the actual_date. ANd also less than the End_program_enroll meaning that If I wrote a consent on 7-31-24 and your end_date was 7-30-24 then it is not a match. the problem is one, I never get two records in the above example because i have an OR clause so that means it's one or the other not both. The issue is when programs are not end_dated and have a null. I have a case where it's selecting the earlier enrollment for the client he had one enrollment, from 2-7-24 to 2-14-24. then he had a new one from 2-14-24 to present. it's selecting the earlier one. I think it should show the current program is most logical. This is the code:
    Select Distinct 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

from event_view ev
left join progress_note_view pnv
on ev.event_log_id = pnv.event_log_id

right 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_start_date AND 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'

I have a case where there was an enrollment in program FFC from 2-7-24 to 2-14-24 and there was also an enrollemnt that is there was a transfer to TFFC on 2-14-24 to the present with a NULL in Program_end_date. So it is logical to show the one that is current and has no end_date but it's selecting the first program the FFC. is there a way that the query can select if there are 2 records that qualify take the one with no end_date?

2

It'll be a lot more helpful to provide DDL, Sample data and expected outcome.

1 Like
3

Maybe this:


Select Distinct 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
from event_view ev
cross apply (
    Select top (1) rpt.*
    From rpt_critical_info_view rpt
    Where 
    (ev.actual_date >= rpt.program_start_date AND rpt.program_end_date is NULL) OR
    (ev.actual_date >= rpt.program_start_date AND ev.actual_date <= rpt.program_end_date)
    Order by Case when rpt.program_end_date is NULL then 1 else 2 end --<<--
) as rpt
left join programs_view pv
on rpt.program_info_id = pv.program_info_id and pv.is_carday = '1'
where ev.event_name in ('Consent for Treatment', 'Medical Consent')
4

Yeah that might be. It turned out to be another twist in the actual _date is datetime but the application records only midnight so the compares weren't being done properly. However I still want to test for any anomalies.

5

hi

hope this helps

Test Sample Data

It might have mistakes

"Please ignore if you do not like "
OR
Feel free to correct

Sample Create Tables Insert Data 
CREATE TABLE event_view (
    event_log_id INT IDENTITY PRIMARY KEY,
    people_id INT,
    event_name VARCHAR(100),
    actual_date DATE,
    expiration_date DATE,
    generic_remarks VARCHAR(255),
    full_name VARCHAR(100),
    date_entered DATETIME,
    user_entered_desc VARCHAR(100)
);

CREATE TABLE rpt_critical_info_view (
    program_info_id INT IDENTITY PRIMARY KEY,
    people_id INT,
    program_name VARCHAR(100),
    program_start_date DATE,
    program_end_date DATE NULL
);

CREATE TABLE programs_view (
    program_info_id INT PRIMARY KEY,
    is_carday CHAR(1)
);

CREATE TABLE progress_note_view (
    event_log_id INT PRIMARY KEY,
    progress_note VARCHAR(MAX)
);

INSERT INTO event_view (people_id, event_name, actual_date, expiration_date, generic_remarks, full_name, date_entered, user_entered_desc)
VALUES
(1, 'Consent for Treatment', '2024-02-14', '2025-02-14', 'Note A', 'John Doe', GETDATE(), 'User1'),
(1, 'Medical Consent', '2024-02-14', '2025-02-14', 'Note B', 'John Doe', GETDATE(), 'User2');

INSERT INTO rpt_critical_info_view (people_id, program_name, program_start_date, program_end_date)
VALUES 
(1, 'FFC', '2024-02-07', '2024-02-14'),
(1, 'TFFC', '2024-02-14', NULL);  -- current ongoing program

INSERT INTO programs_view (program_info_id, is_carday)
VALUES 
(1, '1'), 
(2, '1');


INSERT INTO progress_note_view (event_log_id, progress_note)
VALUES (1, 'Progress note example text');

CREATE TABLE rpt_critical_info_view (
    program_info_id INT IDENTITY PRIMARY KEY,
    people_id INT,
    program_name VARCHAR(100),
    program_start_date DATE,
    program_end_date DATE NULL
);

CREATE TABLE programs_view (
    program_info_id INT PRIMARY KEY,
    is_carday CHAR(1)
);

CREATE TABLE progress_note_view (
    event_log_id INT PRIMARY KEY,
    progress_note VARCHAR(MAX)
);

INSERT INTO event_view (people_id, event_name, actual_date, expiration_date, generic_remarks, full_name, date_entered, user_entered_desc)
VALUES
(1, 'Consent for Treatment', '2024-02-14', '2025-02-14', 'Note A', 'John Doe', GETDATE(), 'User1'),
(1, 'Medical Consent', '2024-02-14', '2025-02-14', 'Note B', 'John Doe', GETDATE(), 'User2');

INSERT INTO rpt_critical_info_view (people_id, program_name, program_start_date, program_end_date)
VALUES 
(1, 'FFC', '2024-02-07', '2024-02-14'),
(1, 'TFFC', '2024-02-14', NULL);  -- current ongoing program

INSERT INTO programs_view (program_info_id, is_carday)
VALUES 
(1, '1'), 
(2, '1');


INSERT INTO progress_note_view (event_log_id, progress_note)
VALUES (1, 'Progress note example text');