having max of date & Time in group selection

[maas]

Hello All,

I am facing difficulties while creating this report:

The data is:

John, 23-11-2009,AP
John, 25-11-2009,AP
John, 25-9-2009,NA
James, 5-9-2010, AP
Jim, 26-4-2010,RE

The output which I am expecting:
John, 25-11-2009,AP
Jim, 26-4-2010,RE
James, 5-9-2010, AP

I am trying to take the max of date in the group selection to show the latest date for each name and I used a group by (name).
where in the group selection I did:
{names.MODIFIED_DATETIME}=maximum({names.MODIFIED_DATETIME},{names.name})

and it showed me the correct values.
But, a new requirement was raised which is to insert a new group and make it above the group(1)"names" which is called status.

So, now the report is taking the max date for each name, as well as, the status too.So, the name with the max date should appear under the appropriate status and ignore the others with the oldest dates.

Best Regards

 

[Madawc]

If you add date to the report sort and show the account details in the group header or footer, suppressing details, that should give you the highest date. Does that help?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Create a SQL expression {%maxdate} like this:

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


In the record selection formula, use:

{table.date} = {%maxdate}

This will return only one date per name to the report so that a higher order group will not create multiple references to the same name.

-LB

 

[maas]

Thanks guys for the help

But, lbass you mean that I have to refer to the same name in the where clause in the SQL Expression?
I am a little bit confused in using the SQL Expression.

So, in my case it will be:

select max(`MODIFIED_DATETIME`) from names A where A.`name` = table.`name`
Is this is a correct SQL Expression?

 

[lbass]

You should replace "table" with your table name, and "name" with your field name. Leave the the "A" as is. If you identify your table(s) and field names, I'll help you with this. Are the date and name field from the same table? If not, how are the tables linked?

-LB

 

[maas]

mmm, no

Status table has
ID, status, modified_date
name table
ID, name, category ID, sub category ID
Category table
Category ID, Category Name
SubCategory table
SubCategory ID, SubCategory Name

First they would like the sequence of the groups as below:
(1)Status
(2)Category
(3)SubCategory

and then the names and the maximum of the date

 

[lbass]

Not really what I was looking for. Please go to database->show SQL query and copy that into the thread. I think you should also paste in a copy of the record selection formula from report->selection formula->record.

-LB

 

[maas]

lbass,

let us develop the report from scratch. So, what do you recommend and your tips regarding the building.

I have added only the 4 tables and linked them together. So what is the next step?

 

[lbass]

Sorry, I'm not willing to do that. However, if you follow through with my request from my last post, I will help from there.

-LB

 

[pepmarti]

Try to acumulate the date in a shared variable

In the detaills section compare de actual value in the shared with the recordset. if the value is > then store it in the shared value.

In The header group store the value in the shared value for ini the variable.

Then you have a variable lastdate with the maximum value.

something like this:

Formula Checkvalue:

shared lastdate as datetime
if lastdate < {table.fecha} then
lastdate = {table.fecha}

Formula IniValue
shared lastdate as datetime
lastdate = {table.fecha}



sorry for my english.

 

[maas]

lbass, sorry you understood me wrong. I am looking only for guidance in the previous post.
However, I will keep trying on how to write a SQL EXPRESSION and do whatever you mentioned and come back again.

Thanks pepmarti, I will try this also.

 

[maas]

Lbass, I have tried this as a SQL Expression

(select max(status.modified_datetime)
from status, name, category, subcategory
where status.id = name.id
and
name.category_ID= category.ID
and
name.SUBCATEGORY_ID=subcategory.subcategory_id)

and it showed me only one result

I tried also what you have said in the record selection to put :

{status.MODIFIED_DATETIME}={%Max_Date}
But the results are the same
Here is theSQL As you wished:
SELECT "INSTITUTION_STATUS_HST"."INSTITUTION_ID",
"INSTITUTION"."INSTITUTION_CODE", "LICENSE_CATEGORY_LKUP"."CATEGORY_NAME",
"LICENSE_SUBCATEGORY_LKUP"."SUBCATEGORY_NAME",
"INSTITUTION_STATUS_HST"."INSTITUTION_STATUS_CODE",
"INSTITUTION"."INSTITUTION_NAME",
"INSTITUTION_STATUS_HST"."MODIFIED_DATETIME", (select
max(hst.modified_datetime)
from institution_status_hst hst, institution ins, license_category_lkup
catlk, license_subcategory_lkup sublk
where hst.institution_id = ins.institution_id
and
ins.category_ID= catlk.category_ID
and
INS.SUBCATEGORY_ID=sublk.subcategory_id)
FROM (("IIS_ADMIN"."INSTITUTION_STATUS_HST" "INSTITUTION_STATUS_HST"
INNER JOIN "IIS_ADMIN"."INSTITUTION" "INSTITUTION" ON
"INSTITUTION_STATUS_HST"."INSTITUTION_ID"="INSTITUTION"."INSTITUTION_ID")
INNER JOIN "IIS_ADMIN"."LICENSE_SUBCATEGORY_LKUP"
"LICENSE_SUBCATEGORY_LKUP" ON
"INSTITUTION"."SUBCATEGORY_ID"="LICENSE_SUBCATEGORY_LKUP"."SUBCATEGORY_ID")
INNER JOIN "IIS_ADMIN"."LICENSE_CATEGORY_LKUP" "LICENSE_CATEGORY_LKUP" ON
("INSTITUTION"."CATEGORY_ID"="LICENSE_CATEGORY_LKUP"."CATEGORY_ID") AND
("LICENSE_SUBCATEGORY_LKUP"."CATEGORY_ID"="LICENSE_CATEGORY_LKUP"."CATEGORY
_ID")
WHERE "INSTITUTION_STATUS_HST"."MODIFIED_DATETIME"=(select
max(hst.modified_datetime)
from institution_status_hst hst, institution ins, license_category_lkup
catlk, license_subcategory_lkup sublk
where hst.institution_id = ins.institution_id
and
ins.category_ID= catlk.category_ID
and
INS.SUBCATEGORY_ID=sublk.subcategory_id)
ORDER BY "INSTITUTION_STATUS_HST"."INSTITUTION_STATUS_CODE",
"LICENSE_CATEGORY_LKUP"."CATEGORY_NAME",
"LICENSE_SUBCATEGORY_LKUP"."SUBCATEGORY_NAME",
"INSTITUTION_STATUS_HST"."INSTITUTION_ID

 

[lbass]

I can't tell from this which "Name" field you are trying to limit to only one row, since there are three different name fields. If you identify the table and field, we can move forward.

-LB

 

[maas]

lbas,

I am lonking 4 tables:INSTITUTION_STATUS_HST,INSTITUTION,LICENSE_SUBCATEGORY_LKUP,LICENSE_CATEGORY_LKUP

INSTITUTION_STATUS_HST has ID, Status(Approved-Rejected..etc), modified_datetime

INSTITUTION has ID, Name, Category ID, Sub Category ID

LICENSE_CATEGORY_LKUP has Category_ID, Category Name

LICENSE_SUBCATEGORY_LKUP has SUBCategory_ID, SubCategory Name

This is my table structure

 

[lbass]

You didn't answer my question--Is it the Institution Name you want to occur only once?

-LB

 

[maas]

Yes

 

[lbass]

Please create a SQL expression exactly like this:

(
select max("MODIFIED_DATETIME")
from "INSTITUTION_STATUS_HST" A, "INSTITUTION" B where
A."INSTITUTION_ID" = B."INSTITUTION_ID" AND
B."INSTITUTION_NAME" = "INSTSITUTION"."INSTITUTION_NAME"
)

You should NOT add any other "groups" to the expression, as the point is to determine the most recent date per Name, not per name per group. Also do not add a table name within the summary, as it doesn't work in XI and some earlier versions.

Then follow the earlier instructions.

-LB

 

[maas]

Thanks lbass