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.

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')