Выберите максимальное значение в диапазоне дат

Задача:

• Добавьте/отредактируйте текущий рабочий код ниже, чтобы возвратить только одну строку для каждого пациента, максимальное значение d1_10.xtransfer (тип данных int) с ограничением d1_10.dstartdate <= glob_End_Date этой строки.

Предупреждения:

• Подобные вопросы есть на StackOverflow и родственных сайтах. Ни один из тех, что я нашел, не помог с решением этой проблемы.

• Это медицинская база данных EHR, я могу поделиться кодом, но любое обсуждение результатов должно быть общим и исключать информацию о пациентах.

• Я заменяю SQL-запрос в уже существующей электронной таблице Excel, чтобы сделать что-то другое. Excel извлекает информацию из нашей базы данных через соединение ODBC. Наша база данных использует Ingres SQL, который принимает большинство, но не все типичные варианты кода SQL. Вполне возможно, что фрагмент кода будет работать в других вариантах SQL, но не с комбинацией Ingres и Excel. У меня есть электронная таблица, работающая и возвращающая результаты, теперь нужно внести некоторые исправления, написав код SQL, который работает в этом программном обеспечении.

На данный момент:

С приведенным ниже рабочим кодом (без максимальных ограничений d1_10.xtransfer) мы возвращаем все строки с d1_10.dstartdate в выбранном пользователем диапазоне дат и с выбранным пользователем d1_10.xinstitute. Мы хотим только самую последнюю. То есть строка пациента либо с максимальным d1_10.dstartdate в пределах диапазона дат, либо с максимальным d1_10.xtransfer (индекс, который увеличивается по мере их добавления) в пределах диапазона дат.

В настоящее время рабочий код:

    "SELECT " & _
    "d1.xpid ""XPID"", " & _
    "d0_v1.name_family ""NAME_FAMILY"", " & _
    "d0_v1.name_given1 ""NAME_GIVEN1"", " & _
    "d0_v1.name_given2 ""NAME_GIVEN2"", " & _
    "d1.sex ""SEX"", " & _
    "d1.birthdate ""DOB"", " & _
    "d0_v1.hsp_pid, " & _
    "c58.brief_name, " & _
    "c73.cname, " & _
    "date_trunc('day',d1_10.dstartdate) ""DSTARTDATE"", " & _
    "date_trunc('day',d1_17.ddeath) ""DDEATH"" " & _
    "FROM d1 " & _
    "JOIN d0_v1 ON d1.xpid = d0_v1.xpid " & _
    "JOIN d1_2 ON d1.xpid = d1_2.xpid  " & _
    "JOIN c58 ON d1_2.xmodality = c58.xcmodality " & _
    "JOIN d1_10 ON d1.xpid = d1_10.xpid " & _
    "JOIN c73 ON d1_10.xinstitute = c73.xcsite " & _
    "JOIN d1_17 ON d1.xpid = d1_17.xpid " & _
    "WHERE " & _
    "d1_10.xinstitute = " & institute_index & " AND " & _
    "d1_10.dstartdate >= '" & glob_Start_Date & " 00:00:00' and " & _
    "d1_10.dstartdate <= '" & glob_End_Date & " 23:59:59' "

Самое близкое, что я получил с кодом, который запускается из электронной таблицы Excel, это с этой дополнительной строкой в ​​предложении WHERE:

d1_10.xtransfer = (SELECT MAX(d1_10.xtransfer) FROM d1_10 GROUP BY xpid)

С помощью этой дополнительной строки мы теперь возвращаем только одну строку от каждого пациента, у которого есть d1_10.xtransfer в диапазоне дат. Но если у них есть строка, где d1_10.xtransfer является более поздним, чем диапазон дат, то они вообще не отображаются в результатах.

В этой строке код принимает MAX(d1_10.xtransfer) для каждого xpid, прежде чем применяет ограничение даты. По моей логике, мы хотим, чтобы это было сделано после, но я не смог придумать код, который работает, чтобы приблизиться к этому.

Заранее спасибо. Я буду обновлять этот вопрос, добавляя дополнительную информацию ниже этого разрыва страницы.

Дополнительная информация:

• По Полу М:

Да, xpid — это идентификационный номер пациента, уникальный для каждого пациента.

Добавлена/изменена строка в предложении WHERE на: "d1_10.xtransfer = (SELECT MAX(xtransfer) FROM d1_10 d1_10_b WHERE d1_10.xpid = d1_10_b.xpid AND d1_10_b.dstartdate <= '" & glob_End_Date & " 23:59:59') "

У пациента Боба есть переводы 14 и 17 июня, которые соответствуют остальным критериям.

При вводе диапазона дат с датой окончания 17 июня и старше электронная таблица правильно возвращает строку для Боба с его переводом 17 июня.

При вводе диапазона дат с конечной датой 14, 15 или 16 июня электронная таблица неправильно не возвращает строку для Боба.

Кажется, что он по-прежнему требует максимального xtransfer перед ограничением по дате.

• По комментарию PaulM:

Я выполнил подвыборку для конкретного пациента следующим образом:

Вход:

SELECT MAX(xtransfer) FROM d1_10 d1_10_b WHERE d1_10_b.xpid = '2258' AND d1_10_b.dstartdate <= '20-apr-2016 23:59:59'

Он вывел значение MAX(xtransfer) = '48233'. Это правильно.

Таким образом, при запуске в Visual SQL в качестве собственного оператора, устанавливая d1_10_b.xpid равным конкретному пациенту, он правильно извлекает максимальный xtransfer из диапазона дат. (Было более свежее xtransfer за пределами диапазона дат, и оно по-прежнему правильно отображало максимальное xtransfer в пределах диапазона дат.)

Затем я попытался запустить этот же самый подвыбор в том месте, где закрывается электронная таблица. То есть я вручную выбрал тот же диапазон дат (который корректно и успешно передается как переменная), но заменил d1_10.xpid = d1_10_b.xpid на d1_10_b.xpid = '2258'. Это не сработало. Электронная таблица не показала строку для этого пациента, по-видимому, потому, что она по-прежнему применяет функцию MAX() до ограничения диапазоном дат в подзапросе. И все же подзапрос работает, когда он запускается сам по себе.

Большое спасибо за любые дальнейшие предложения.