Record Selection Question

[MiagiSan]

Hello,

I am trying to pull information from SQL that shows me the following:

Case Number "CASE_ID"
Case Note "CASE_DECR"
Case Note Added Date "DATEADDED_DTTM"

My report keeps coming out with duplicate case numbers because there are multiple case notes in each case. I want the report to show me only the "Newest" case note. (Only one Case number per case note)

I think I have the SQL Statement down but it doesn't work in Crystal. Am I on the right track? Thank you for the help!

SELECT TOP 1 DATEADDED_DTTM,CASE_ID FROM TABLE_NAME
WHERE CASE_ID IN (SELECT DISTINCT CASE_ID FROM TABLE_NAME)
ORDER BY ADDED_DTTM DESC

 

[synapsevampire]

Please remember to post your software version.

CR 9 and above:

Create an Add Command and paste in your SQL.

Or you can just use the tables in Crystal, group by the ID, and use the following in the Report->Eit Selection Formula->Group:

{table.date} = maximum({table.date},{table.id})

-k

 

[MiagiSan]

Sorry, CR10

Thank you very much!