Max Date Values

[#1]

Hi Expert,

I have data with multiple date but I need the data with maximum date values.

Could you please suggest?

[chrislofthouse]

Use MAX() function.

[#3]

My script is like below

DisplayOrder:
LOAD
    D_AS_OF_TMS,
    C_ORDER,
    CLM_CORE_NONCOR,
    CLM_SEGMENT,
    CLM_SEGMENT_NEW,
    COMMENTS;
SQL
    SELECT
  D_AS_OF_TMS ,
    C_ORDER,
    CLM_CORE_NONCOR,
    CLM_SEGMENT,
    CLM_SEGMENT_NEW,
    COMMENTS;
FROM CCAR.dbo.MICCAR_Disp_Order;

where to use it now

[puttemans]

Hi Raj,

I can imagine you need the maximum date per variable x. In that case, you use the MAX() function together with a group by.

e.g.

MAP_KtM:

MAPPING LOAD
KtM,

MAX(ShareNew) as ShareNewmax
FROM xxx (qvd)
Group by KtM;

In this example, I select out of a big table the maximum of 'ShareNew' for each KtM (variable I 'group by' , and recall it to 'ShareNewmax'.

I typically use this as a mapping table, in order to bring this maximum back to all lines of the total table through applymap.

[avinashelite]

Hi ,

If you want you have on any field: then use aggr() function

AGGR(max(date),field)

if your trying to achieve this in the  script try like this:

LOAD

field1,

max(date) as date;

from tablename group by field1;

Hope this helps you .

[puttemans]

And what is your 'Date' field? You just want the maximum out of the total, or per a given other variable?

[#7 · Accepted solution]

I made it like this:

FROM CCAR.dbo.MICCAR_Disp_Order
WHERE AS_OF_TMS = (select max(AS_OF_TMS) from CCAR.dbo.MICCAR_Disp_Order where AS_OF_TMS <= getDate());

Note = AS_OF_TMS, is my date field