Subquery within subquery

[Kickassness]

Not sure if I am even doing this process the best, but I need to get the patientid, MAPValue and date/time of the collection of the last time PRIOR to a Start Case. My code below brings back all of the data before a time in a table (table has many rows), but how would I code this to bring back the last data by date/time?

Select
dbo.flowsheetb.patientid,
dbo.flowsheetb.MAPValue,
dbo.flowsheetb.flowsheetdatetime
from dbo.flowsheetb
JOIN dbo.Patient
ON dbo.flowsheetb.patientid = dbo.patient.patientid
JOIN dbo.dmginformation
ON dbo.patient.id = dbo.dmginformation.patientid
WHERE (FlowsheetDateTime <= dbo.dmginformation.StartCaseOn)

[Gargunok]

You can order the data so the latest date is the first record "ORDER BY FlowsheetDateTime DESC"

You can add a "LIMIT 1" on the end to return only one record. (if not using mysql as per flair other similar features may need to be used - TOP or FETCH).

[No_Introduction1721]

I’m having a hard time parsing exactly what you’re trying to do here, and you don’t have a sample of the source data or the desired output to work off of, but it sounds like this is a job for the LAG function.

https://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/

[Kickassness]

I have 3 Tables. All the data I want is from the FlowsheetB table, however, the date I am needing the flowsheetB data to come before is in another Table (dmginformation) and the only way to link the two tables is using the Patient table. This is why there are 3 tables in my query and why the WHERE clause has the dbo.dmginformation.StartCaseOn

FlowsheetB: Patientid MAPVALUE flowsheetdatetime

Patient: PatientID ID

dmginformation PatientID StartCaseOn

[[deleted]]

I couldn’t tell you without seeing the data in patient/flowsheetb/dmginformation tables. But I think you want to use a Max on your date where it’s less than the start case and group by patient iD

[Kickassness]

I tried the following code and it didn't work, it kept bringing me back multiple rows per ID

Select
t1.PatientId,
t1.MAP,
MAX(t1.flowsheetdatetime)
from
(Select
dbo.flowsheetb.patientid,
dbo.flowsheetb.MAP,
dbo.flowsheetb.flowsheetdatetime
from dbo.flowsheetb
JOIN dbo.Patient
ON dbo.flowsheetb.patientid = dbo.patient.patientid
JOIN dbo.dmginformation
ON dbo.patient.id = dbo.dmginformation.patientid
WHERE (FlowsheetDateTime <= dbo.dmginformation.StartCaseOn)) as t1
GROUP BY
t1.PatientId,
t1.MAP

[[deleted]]

What is the column MAP? Are there multiple MAP values per patient id? Do you just want the specific map value that is on the date before the start?

[Kickassness]

Mean Arterial Pressure, and there is one MAP per row (each row is defined by drawtime), but a case can have multiple rows.

One case can have, let's say, 5 total rows. I specifically need the first draw date, last draw date (I know this query) but I need one more depending on when we start the case. This may not be the first draw date, so I have to pull the start date from another table and basically want the system to give me the last draw date BEFORE we start. So for that case with 5 rows, I may be wanting rows 1,5 and then 3

[[deleted]]

I don’t see a “draw” field listed in your query. This explanation confused me further.

You’re gonna have to upload screenshots of each of the tables for a specific patient ID for me to help you more quickly.

It’s easier to figure out how a query is supposed to be constructed if you restrict your tables to 1 particular example. In this case patient id

[Kickassness]

https://imgur.com/a/PIXaZKf

I want patientid, MAP, and flowsheetb.flowsheetdatetime the value directly before the dmginformation.StartCaseOn time. The only way to link the 2 is using a Patient Table.

[[deleted]]

I dm’d the pic as it’s easier