records having max date 1

[codrutza]

CRXI

Hi. Please help me with this:
I have a group on job. How do I take only the mov which have the latest date?

job mov date
1059 GCI4410 25/04/2011
ICI4443 08/05/2011

1058 GCI4410 25/04/2011

1060 GII1060727 13/06/2011
ICI4514 05/07/2011

1061 GII1060844 29/05/2011
ICI4487 14/06/2011

1065 ICI4543 25/07/2011

1064 GCI4493 19/06/2011
ICI4561 11/08/2011

1062 GCI4493 19/06/2011
ICI4561 11/08/2011

1068 GCI4507 08/07/2011
ICI4543 25/07/2011

1069 ICI4582 08/08/2011


Desired output

job mov date
1059 ICI4443 08/05/2011

1058 GCI4410 25/04/2011

1060 ICI4514 05/07/2011

1061 ICI4487 14/06/2011

1065 ICI4543 25/07/2011

1064 ICI4561 11/08/2011

1062 ICI4561 11/08/2011

1068 ICI4543 25/07/2011

1069 ICI4582 08/08/2011

 

[lbass]

Go to report->selection formula->GROUP and enter:

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

If you need to do calculations across jobs, be sure to use running totals, since non-group-selected records are still "in" the report, though not displayed, and would contribute to the more usual subtotals. Unlike suppression, with group selection, you don't have to explicitly exclude non-group-selected records.

-LB

 

[codrutza]

Thank you, LB. It's working.

 

[codrutza]

Now it prints several times the mov, each mov having more jobs. Any ideea?

1059 ICI4443 08/05/2011

1058 GCI4410 25/04/2011

1060 ICI4514 05/07/2011

1050 ICI4443 08/05/2011

1061 ICI4487 14/06/2011

1065 ICI4543 25/07/2011

1064 ICI4561 11/08/2011

1062 ICI4561 11/08/2011

1068 ICI4543 25/07/2011

1051 ICI4443 08/05/2011

1069 ICI4582 08/08/2011

 

[lbass]

If you only want one record per mov in the report, then try this:

I think you should create a SQL expression {%maxdt} like this:

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

Then use a record selection formula like this:

{table.date} = {%maxdt}

Then remove the group selection formula I suggested ealier. This would mean there would be only one record per mov in the report.

-LB

 

[codrutza]

I'm sorry, I can't understand; which is table A?

 

[lbass]

Replace "table" with your actual table name, and leave "A" as is. Similarly replace "date" and "mov" with your actual field names.

-LB

 

[jklewis]

A" is just an alias name for "table"

If you see
FROM table A
or you may see
FROM table AS A

Now you can use A to reference table instead of table....
This is extremely handy for long table names and if you join the table on itself.

You can also do this with column names.
table.date AS date_row
etc etc..

Hope this helps,

 

[codrutza]

Thanks, Jklewis. LB: I don't know how to write this SQL Expression. I tryed to write it like this:
(
select max("mytable"."DepartureDate")
from table A
where A."Mov"="mytable"."Mov"
)

and like that
(
select max('mytable'.'DepartureDate')
from table A
where A.'Mov'='mytable'.'Mov'
)

and it gives me errors

 

[lbass]

You cannot use the table name within the summary, so do it as I suggested. What type of database are you using? Try adding a field to the SQL expression by double-clicking on it in the field list within the SQL expression area. This will indicate what punctuation you should be using. It will either be:

(
select max("DepartureDate")
from "mytable" A
where A."Mov"="mytable"."Mov"
)

or:
(
select max(`DepartureDate`)
from mytable A
where A.`Mov`=mytable.`Mov`
)

Note that the character in the last expression is NOT a single quote--not sure what it's called. It's to the left of the "1" on the top row of your keyboard.

-LB

 

[codrutza]

SQL Server. I use before the SQL Exp and the punctuation is like this: "mytable"."Mov"
It still gives error

 

[lbass]

Please show the actual SQL expression as you entered it so we can troubleshoot this.

-LB

 

[codrutza]

(
select max("DepartureDate")
from "FreightMovements" A
where A."Number"="FreightMovements"."Number"
)

Error: The multi-part identifier "FreightMovements"."Number"
could not be bound


(
select max(`DepartureDate`)
from `FreightMovements` A
where A.`Number`=`FreightMovements`.`Number`
)

Error: Incorrect syntax near ‘`’

 

[lbass]

The first syntax would be correct.

So you have a table named FreightMovements containing a field Number? You must add the table to the main report and place a field somewhere in it (e.g., suppressed in the report header).

-LB

 

[pmax9999]

Hi codrutza

I have had this happen many times, for no obvious reason. Try amending the SQL expression to:
(
select max("DepartureDate")
from "FreightMovements" A
where "Number"="FreightMovements"."Number"
)
ie, remove the table alias in the where clause.

Hope this helps.

Cheers
Pete

 

[codrutza]

Thank you, Pete, it's the right syntax now.
LB:
I created SOL Expression {%maxdt}
(
select max("DepartureDate")
from "FreightMovements" A
where "Number"="FreightMovements"."Number"
)
and I have put in record selection
{FreightMovements.DepartureDate}={%maxdt}
and I've deleted from group selection
{FreightMovements.DepartureDate}=maximum({FreightMovements.DepartureDate},{FreightJobs.Number})
I miss something I'm afraid, because it still prints several times the movement.

"So you have a table named FreightMovements containing a field Number? You must add the table to the main report and place a field somewhere in it (e.g., suppressed in the report header)."

I don't understand, I have to add FreightMovements again?
And I have some fields from FreightMovements in the report allready.