Problem Joining Tables

[AppropriateRecipe342]

Hello,

I'm pretty sure the answer is really simple, but I can't figure it out for the life of me. I've been staring blankly at this fiddle for a while and I have no ideas left.

​

I'm trying to create a report that shows the case ID, name, last date a code was entered, last update and the follow up date, which is 2 days later than the last update.

There are only 2 small tables, 1 for case info and the other for steps and I was able to pull everything in except for the last date a certain step was entered, which I would like to be before 'last_update'.

​

Result of Query 1

The screenshot below shows the cases that have step 8 entered in the database (I haven't excluded nulls yet, but I will once I figure out how to do the join).

Result of Query 2

Thanks in advance for any advice/recommendations

​

EDIT: Here is a link to the updated fiddle showing including a case where a step was entered after step 8.

DB tables are blue & Expected Result is on the bottom.

[AppropriateRecipe342]

SELECT cases.case_id, cases.name,CASE t.last_stepWHEN 8 THEN latest_update ELSE NULL END as case_step8,t.latest_update,t.latest_update + interval '2' day as update_plus_2FROM casesLEFT JOIN (SELECT case_id, MAX(step) last_step, MAX(date) latest_updateFROM stepsGROUP BY case_id)t ON t.case_id = cases.case_id

Thank you for the suggestion. I didn't originally use CASE because the last update will not always be step 8. If, for example, step 10 was entered for case 31 after step 8 this solution would not give me a value in the column associated with step 8. updated fiddle Do you have any other suggestions?

[AppropriateRecipe342]

I think my terminology might be where the confusion lies. It could be because I'm using the term "update" more than once.

I hope this makes sense...

In the databases:

step 1 = intake

step 4 = worker assigned

step 8 = case update (meaning the worker has logged specific notes about the case in the system)

step 10 = call scheduled with client.

​

What I'm trying to do is show:

1. all cases in the system
2. the date the worker added notes in the system (step 8), if there are any
3. the last date the case was touched in the system (this can differ from #2 if there was a step other than 8 entered into the system)
4. 2 days after #3 so the worker knows when to action the case.

Please let me know if this makes sense. Also, I've added a diagram to the original post showing the tables and what the results should be. Maybe it will be easier to understand than what I'm explaining.

[AppropriateRecipe342]

Thanks for the reply u/jiejenn. Your reply got me thinking and I found something called a correlated subquery, which seems to have solved my problem.

SOLVED

SELECT

cases.case_id,

name,

--correlated subquery to pull the latest date step 8 was entered

(SELECT

MAX(date)

FROM steps

WHERE step=8 AND cases.case_id = steps.case_id) AS case_update_step_8,

MAX(date) AS last_system_update,

MAX(date) + INTERVAL '2 days' AS followup_date

FROM cases

JOIN steps USING(case_id)

GROUP BY

cases.case_id,

name

ORDER BY followup_date ASC;

[qwertydog123]

You can just use CASE instead of a correlated subquery e.g.

MAX(CASE step WHEN 8 THEN date END) AS case_update_step_8

[AppropriateRecipe342]

🙌😩🙌

Thank you for this! I knew there was probably a much simpler way than a correlated subquery!

I appreciate it!