How To Fetch Records Based On The Dates In Mysql

I have sample data ID Name Amount cal_amt Run_amt Dates 1 Test 15000 0.00 15000 2020-06-01 1 Test 15000 0.00 30000 2020-04-01 1 Test 15000 120

Solution 1:

Use ROW_NUMBER() window function to get the row with the Run_amt of the max month and then with conditional aggregation:

SELECT t.ID, t.Name, t.Amount,
  MAX(CASEWHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END) cal_amt,
  MAX(CASEWHEN t.rn =1THEN Run_amt END) Run_amt
FROM (
  SELECT*,
    ROW_NUMBER() OVER (PARTITIONBY ID, Name, Amount ORDERBYDateDESC) rn
  FROM tablename
) t
GROUPBY t.ID, t.Name, t.Amount

Or:

SELECT t.ID, t.Name, t.Amount,
       MAX(t.cal_amt) cal_amt,
       MAX(t.Run_amt) Run_amt
FROM (
  SELECT ID, Name, Amount,
    MAX(CASEWHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END) 
      OVER (PARTITIONBY ID, Name, Amount ORDERBYDateDESC) cal_amt,
    FIRST_VALUE(Run_amt) OVER (PARTITIONBY ID, Name, Amount ORDERBYDateDESC) Run_amt
  FROM tablename
) t  
GROUPBY t.ID, t.Name, t.Amount

See the demo. Results:

> ID | Name   | Amount | cal_amt | Run_amt> -: | :----- | -----: | ------: | ------:>  1 | Test   |  15000 |   12000 |   15000>  2 | Test_1 |  18000 |   16000 |   25000