Of Two Dates in Separate Tables (A&B), When Table A Date Changes, Associate the Date In Table B With That Change

Transact-SQL
1

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:

PAT_ID SERVICE_DATE CONTACT_DATE Valid?
A 06/17/2020 06/18/2020 Y
A 06/17/2020 07/24/2020 Y
A 06/17/2020 09/11/2020 N
A 06/17/2020 02/26/2021 N
A 09/10/2020 09/11/2020 Y
A 09/10/2020 02/26/2021 N
A 02/09/2021 02/26/2021 Y

Here is what it should look like

PAT_ID SERVICE_DATE CONTACT_DATE Valid?
A 06/17/2020 06/18/2020 Y
A 06/17/2020 07/24/2020 Y
A 09/10/2020 09/11/2020 Y
A 02/09/2021 02/26/2021 Y

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..

SELECT SD.PAT_ID
,SERVICE_DATE
,CONTACT_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..

Data creation:

CREATE TABLE #SERVICE_DATE
(
PAT_ID VARCHAR(1)
,SERVICE_DATE DATETIME
)

CREATE TABLE #CONTACT_DATE
(
PAT_ID VARCHAR(1)
,CONTACT_DATE DATETIME
)

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..!

1 Like
2

ok, you did provide sample :upside_down_face:

3

Query:

SELECT CD.PAT_ID, SD.SERVICE_DATE, CD.CONTACT_DATE
FROM #CONTACT_DATE CD
OUTER APPLY (
    SELECT TOP (1) SD.*
    FROM #SERVICE_DATE SD
    WHERE SD.PAT_ID = CD.PAT_ID AND
        SD.SERVICE_DATE <= CD.CONTACT_DATE
    ORDER BY SD.SERVICE_DATE DESC
) AS SD
1 Like
4

Hello, this looks like it works, thanks so much!

5

You're welcome!

You'll very likely want to have an index on the #SERVICE_DATE table on (PAT_ID, SERVICE_DATE DESC) to speed up the lookup.

1 Like
6

Cool, thanks for the extra index tip!