Hi SQL Experts,
Need help with a logic where I need to extract only the distinct records which has greatest dates with in each month.
Example
ID Process_date
(1, 20090112)
(1, 20090130)
(2, 20090105)
(3, 20090201);
(4, 20090202);
(4, 20090308);
My output should be
(1, 20090130)
(2, 20090105)
(3, 20090201);
(4, 20090202);
(4, 20090308);
Unique check should nbe for ID and date combination. Only if the ID is same, then I need to get the maximum date with in the month. Any inputs will be helpful. I tried few things with DATEDIFF but it didnt work with HCM Cloud BI Reports. Thanks