Query for active records between date range or most recent before date range

0
votes

I need to find active records that fall between a range of date parameters from a table containing applications. First, I look for a record between the date range in a table called 'app_notes' and check if is linked to an application. If there is no app_note record in the date range, I must look at the most recent app note from before the date range. If this app note indicates a status of active, I select it.

The app_indiv table connects an individual to an application. There can be multiple app_indiv records for each individual and multiple app_notes for each app_indiv. Here is what I have so far:

SELECT DISTINCT individual.indiv_id
    FROM individual INNER JOIN 
    app_indiv ON app_indiv.indiv_id = individual.indiv_id INNER JOIN
    app_note ON app_indiv.app_indiv_id = app_note.app_indiv_id
    WHERE (app_note.when_mod BETWEEN @date_from AND @date_to)
    /* OR most recent app_note indicates active */

How can I get the most recent app_note record if there is not one in the date range? Since there are multiple app_note records possible, I don't know how to make it only retrieve the most recent.

3
sqlsql-server
That's a pretty funky requirement. I would say just take a max date, and find the most recent active record prior to that. - Andrew
Can you please post the table schema for the app_note table, and let me know if there is an app_note_id (or equivalent) that will be in order of the most recent app_note? Also, please let me know if the 'most recent' requirement means pre-date range or maximum available - AHiggins

3 Answers

0
votes

(untested) You'll need to use a CASE switch. 

SELECT DISTINCT individual.indiv_id
FROM individual INNER JOIN 
app_indiv ON app_indiv.indiv_id = individual.indiv_id INNER JOIN
app_note ON app_indiv.app_indiv_id = app_note.app_indiv_id
WHERE (CASE WHEN app_note.when_mod BETWEEN @date_from AND @date_to 
                THEN (SELECT appnote.when_mod from individual where appnote.when_mod BETWEEN @date_from AND @date_to)
            WHEN app_note.when_mod NOT BETWEEN @date_from and @date_to 
                THEN (SELECT appnote.when_mod from individual appnote.when_mod LIMIT 1))

Query might not be correct. Switch might need to be in the first SELECT part of the query.

0
votes

It seems to me that you really only care about the end date of your date range, since you want to be able to look farther back if there's nothing in that date range. I would use a CTEand the ROW_NUMBER() function. The CTE is just a cleaner way to write a sub-query (in this case, a CTE can do a lot more though). The Row_Number function will numbers the rows based on the order by statement. The partition by resets the numbering to one each time you hit a new value in that column.

with AppNoteCTE as
(select
<not sure what columns you need here>
app_indiv_id,
ROW_NUMBER() OVER (PARTITION BY APP_INDIV_ID ORDER BY WHEN_MOD DESC) RN
FROM
APP_INDIV
WHERE
WHEN_MOD <= @endDate)

SELECT DISTINCT individual.indiv_id
    FROM individual INNER JOIN 
    app_indiv ON app_indiv.indiv_id = individual.indiv_id INNER JOIN
    AppNoteCTE ON app_indiv.app_indiv_id = AppNoteCTE .app_indiv_id
    and AppNoteCTE.RN = 1
0
votes
SELECT * 
FROM individual i
INNER JOIN app_indiv ai
    ON ai.indiv_id = i.indiv_id
OUTER APPLY
(
    SELECT TOP 1 * FROM app_note an
    WHERE an.app_indiv_id = ai.app_indiv_id
        AND an.when_mod < @date_to
    ORDER BY an.when_mod DESC
) d
WHERE d.status = 'active'

Find the last note less than end date, check to see if it's active and if so show the individual record.