Sorry for the God-Awful subject title, have no idea how to more concisely articulate this issue! And maybe not much better below, but I have posted the data separately after the explanation, what it looks like now (not wanted) and what i want it to look like, hopefully that will elucidate any confusion in my explanation..i also included code to make the tables..
So, i have two tables*, one with the service_date and the other with the contact_date. The contact_date should be >= to the service_date, but if there is another (2nd or 3rd etc. service_date) service_date and the contact_date is >= that other service_date as well (and naturally, closer to it than the other service_date it is greater than), the contact_date should only be associated with that other service_date it is greater than and closer to, not the earlier one.
*Each data set is by pat_id, this example is for one pat_id, 'A'.
Currently because i have code written that ONLY says the contact_date >= service_date, then ALL contact_dates are associated with ALL service_dates that they are greater than.
This works like this:
Service_Dates: 6/17/2020; 9/10/2020; 2/9/2021
Contact_Dates: 6/18/2020; 7/24/2020; 9/11/2020; 2/26/2021
For the data i want to achieve, the contact dates 6/18, 7/24 should be associated with the service date 6/17, but the 9/11 contact date should not be associated with the 6/17 service_date, because there is a another service date that it is closer to, but still greater than; same with the 2/26 contact_date, for my purposes, that one should not be associated with the service_date of either 6/17 or 9/11, since there is another, latest service_date (2/9/2021) that it is closer to and greater than the contact_date of 2/26/2021
Here is how i currently have it that i do not want it:
Here is what it should look like
Here is the code i have that does not work for the obvious reason of the contact_date not being to discriminate when there is a closer service_date, but the contact_date is still greater than that closer service_date..
FROM #SERVICE_DATE AS SD
JOIN #CONTACT_DATE AS CD
ON SD.PAT_ID = CD.PAT_ID
WHERE CD.CONTACT_DATE >= SD.SERVICE_DATE
As you can see, the 9/11 contact_date should be 'taken', related to the 9/10 service_date and not the 6/17 service_date, same concept with the 2/26/2020 contact_date, that one should not be related to the 6/17 nor the 9/10 service_date since there is a closer (but less than) service_date it can and should be related to..
CREATE TABLE #SERVICE_DATE
CREATE TABLE #CONTACT_DATE
INSERT INTO #SERVICE_DATE
SELECT 'A', '20200617' UNION
SELECT 'A', '20200910' UNION
SELECT 'A', '20210209'
INSERT INTO #CONTACT_DATE
SELECT 'A', '20200618' UNION
SELECT 'A', '20200724' UNION
SELECT 'A', '20200911' UNION
SELECT 'A', '20210226'
SELECT * FROM #SERVICE_DATE
SELECT * FROM #CONTACT_DATE
Thanks for any help..!