Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Expert,
I have data with multiple date but I need the data with maximum date values.
Could you please suggest?
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
Use MAX() function.
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
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.
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 .
And what is your 'Date' field? You just want the maximum out of the total, or per a given other variable?
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