Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record Selection Question

Status
Not open for further replies.

MiagiSan

IS-IT--Management
Aug 1, 2005
51
CA
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

 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top