on 2024 Dec 30 11:41 PM
I have two tables, one just a customer information and the other is all the casenotes tie to that customer. I just want to see the latest casenote ONLY for my customer. I create this query but it giving all the casenotes. Please help.
SELECT
c.userID,
c.FirstName,
c.LastName,
MAX(cn.noteDate) as "Last CN Date"
FROM UsercustomerDetails c
LEFT JOIN Casenotes cn
ON c.UserID=cn.UserID
Where c.userid='309277851'
GROUP BY c.UserID, c.FirstName, c.LastName,cn.noteDate,cn.title,cn.note
Result:
I need to make it display the last record only.
Request clarification before answering.
Hi @kangdinh,
I have reviewed your scenario and created two tables to replicate it. The first table customer contains customer information, and the second table note contains case notes associated with each customer. I populated these tables with sample data. Below in the image, I’ve highlighted the specific userId and their latest case note.
Query:
SELECT c~userID,
c~FirstName,
c~LastName,
cn~noteDate AS LastNoteDate,
cn~title,
cn~note
FROM customer AS c
LEFT JOIN
note AS cn ON c~userID = cn~userID
WHERE c~userID = '309277851'
AND cn~noteDate = ( SELECT MAX( noteDate ) FROM note
WHERE userID = c~userID )
INTO TABLE @DATA(lt_result).
For validation, I created a class that outputs the result in the console. Below, you'll see the result in the console as a screenshot.
This approach ensures that only the most recent note for the customer is retrieved correctly.
BR,
Andrei
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
62 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.