finding max date from a file grouping by another column

[Sgiri1]

hi,

i have a sequential file stage with input columns :

status varchar
completed_date timestamp

the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.

i used the logic of filtering the columns with status='C' in the 'where' clause and then agreegating. but this did not work since agrregation will not work on time stamp. the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'

need some help on this

[ray.wurlod]

Your timestamp string is in a collatable format. Declare it as Char (or VarChar) and get the max of that.

[Pagadrai]

status varchar
completed_date timestamp

the column status will have value as 'S' and 'C'. i want to pick the maximum date from the completed_date column with status as 'C' and load it in the target file.
the time is displayed as 'yyyy-mm-dd hh:mm:ss.000'

Hi,
You can try this:-
Before passing to aggregator, add one more column for seconds. This will be the difference between your input timestamp and a 'Constant Time stamp'.
* You can use it something like 2009-01-01 00:00:00.000
Then you can use the aggregator to know the min or max timestamp.

the function 'SecondsSinceFromTimestamp'- returns the number of
seconds between two timestamps.

Hi Experts,
If the logic is incorrect, please let me know.

[chulett]

Well, then you'd have to "decode" it back. As noted, declare the timestamp as a string, the format it is coming in is directly collatable so will aggregate just fine.