Showing Max Date only

[MiagiSan]

Hello,

I am using CR10 and I am trying to create a report that shows Case ID with the Solution of the case.
{solution_table.CASE_ID}
{solution_table.SOLUTION_DESC}
(solution_table.ROW_LASTMANT_DTTM}


The problem is that one case can have more than one Solution and I am getting multiple returns for the same case.

Here is what I am getting:

Case ID Resolution Date
111 res1 2006/10/22
123 res2 2006/10/23
123 res3 2006/10/24
126 res4 2006/10/29


On Case ID "123" there are 2 solutions however I am only interested in the latest one (Oct 24th)

Here is what my output should look like

Case ID Resolution Date
111 res1 2006/10/22
123 res3 2006/10/24
126 res4 2006/10/29

I tried adding the following SQL expression (%Maxdate)

(select max(solution_table.ROW_LASTMANT_DTTM) from soluton_table
where
solution_table.CASE_ID = solution_table.CASE_ID)

and I added this to the record selection area

{solution_table.ROW_LASTMANT_DTTM}=(%Maxdate)

This actually removed everything from my report so I am not sure what my next step should be.

I have not performed any grouping in this report

Any help would be appreciated.

Thank you

R

 

[lbass]

Change your SQL expression to:

(select max(A.ROW_LASTMANT_DTTM) from solution_table A
where
A.CASE_ID = solution_table.CASE_ID)

-LB

 

[MiagiSan]

Thank you for the prompt reply LB

Unfortunately I am receiving an error message saying. "The column prefix solution_table does not match with a table name or alias name used in the query"

would that have anything to do with CASE_ID and ROW_LASTMANT_DTTM referencing the same table?

CASE_ID is also located here: {PS_RC_CASE.CASE_ID} Should I try to reference that table for the CASE_ID?

Thank you for the help!

R

 

[synapsevampire]

Post exactly what you tried rather than trying to explain what is in it...

Also in your original example of what you used there is a typo, so it couldn't have worked, note that you have soluton not solution.

Posting exactly what was tried is key, and you're looking for a maximum for each caseid, so you'll need a group there.

The quick fix is to create a group by cae_id, then sort by the date ascending, suppress the details, and use th group footer for display

-k

 

[MiagiSan]

My Apologies,

I went with the quick fix and it appears to be working just fine.

Thank you once again for your help!

R