get the Latest Date`

[abhi900]

Hello all,

how do I get the latest date from 2 dates linked to the same ID.

So basically the ID – 001has 2 execution dates
ID001 – 09/07/2010
ID001 - 26/06/2010
>> want to base my details section or count the ID's in Group Header > and show records only with the latest date (in detail section) out of the 2 ID's.
SO the result from above is ID001 - 09/07/2010.

Regards
Abhi.

 

[Madawc]

Try a summary total, maximum date. This could be used to suppress unwanted detail lines with a date less than the maximum.

Or group and sort and put the latest details in the group header or footer, suppressing all detail lines.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[abhi900]

Hi MAdawc,

the logic you suggested didnt work. I was trying to think how do i extract just the ID with the latest date assigned to it.
As some ID do have multiple dates assigned to it.
By implementing your logic,
the details was surpressed for most of the other groups and only few IDs with the latest date was shown. This was totally incorrect.
any other way of doing this ?
regards

 

[Madawc]

Now that is tricky. You might manage it by adding the table twice, meaning the second one will be an alias. And linking them by ID. Grouping within ID for date on the first occurence. Suppress level 2 groups on the basis of not having the maximum date on the first record.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

For detail suppression, you would have to use:

{table.date} < maximum({table.date},{table.ID})

...after grouping on {table.ID}.

Or you could alternatively use group selection (report->selection formula->records:

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

This would display only the most recent date in the details, but you would have to use running totals for calculations across groups, since non-group selected records would contribute to the more usual inserted summaries.

Or better yet, you could create a SQL expression {%maxdt}:

(
select max(`date`)
from table A
where A.`ID` = table.`ID`
)

Then in the record selection formula, use:

{table.date} = {%maxdt}

The SQL expression might have to include other criteria depending upon your record selection formula, since it draws directly from the database.

-LB

 

[abhi900]

Hi,
I tried using the SQL query thinking this would be the simplest to do so but when i implemented the following query the report took ages to run.

Select
Max(td.RUN.RN_EXECUTION_DATE) As MAXDATE,
td.TEST.TS_TEST_ID
From
td.RUN Right Join
td.TEST On td.RUN.RN_TEST_ID = td.TEST.TS_TEST_ID
Group By
td.TEST.TS_TEST_ID

I am using lot of other tables as well and since the joins are made in the query i still had to join the TEST ID with the TESTID of TEST table in LINK Section of Database Expert.

SO yea then as suggested i did put the condition in Record selection as well and the report took ages to run. it even crashed on couple of occassions.
So i knw i am going wrng somewhere... ? but where in joins...

regards
abhi

 

[lbass]

Did you set this up as a command? I meant for you to use a SQL expression (in the field explorer). It should look like the following. Assuming you are using some CR version from 9.0 to XI, you should not reference the table within the summary:

(
Select Max(RN_EXECUTION_DATE)
From td.RUN A Right Join
td.TEST B On
A.RN_TEST_ID = B.TS_TEST_ID
Where B.TS_TEST_ID = td.TEST.TS_TEST_ID
)

Then in the record selection formula, use:

{table.date} = {%maxdt}

-LB

 

[abhi900]

hi Lbass,

I am getting the follwing error when using SQL expression :-

The multi part identifier "td.test.TS_test_ID" could not be bound

????

 

[abhi900]

hi lbass...
well i tried to work around that error..
and i have put it like this :-

(
Select Max(RN_EXECUTION_DATE)
From td.RUN A Right Join
td.TEST B On
A."RN_TEST_ID" = B."TS_TEST_ID"
Where B."TS_TEST_ID" IN (Select TS_TEST_ID from TD.TEST)
)

 

[abhi900]

hi LBass,

SOrry i couldnt get this to work. Though the formula and all doesnt show any error but it fails to show the data when the report is run.

 

[lbass]

Please verify your CR version, identify the database, and show the exact SQL expression you used where you go the error message.

-LB

 

[abhi900]

hi Lbass,

i am using Crystal 2008, the database is SQL Server 2000 and SQL expression is :-

(
Select
Max(A.RN_EXECUTION_DATE)
From
td.RUN A Right Join
td.TEST B On A.RN_TEST_ID = B.TS_TEST_ID
Where
B.TS_TEST_ID In (Select
td.TEST.TS_TEST_ID
From
td.TEST)
)

and in record selection it is :-

{RUN.RN_EXECUTION_DATE} = {%maxDT}

 

[lbass]

I meant your version of this--as this was my suggestion:

(
Select Max(RN_EXECUTION_DATE)
From td.RUN A Right Join
td.TEST B On
A.RN_TEST_ID = B.TS_TEST_ID
Where B.TS_TEST_ID = td.TEST.TS_TEST_ID
)

-LB