By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tablename (
`ID` INTEGER,
`Name` VARCHAR(6),
`Amount` INTEGER,
`cal_amt` INTEGER,
`Run_amt` INTEGER,
`Date` DATETIME
);
INSERT INTO tablename
(`ID`, `Name`, `Amount`, `cal_amt`, `Run_amt`, `Date`)
VALUES
('1', 'Test', '15000', '0.00', '15000', '2020-06-01'),
('1', 'Test', '15000', '0.00', '30000', '2020-04-01'),
('1', 'Test', '15000', '12000', '30000', '2020-05-01'),
('2', 'Test_1', '18000', '0.00', '25000', '2020-06-01'),
('2', 'Test_1', '18000', '0.00', '35000', '2020-04-01'),
('2', 'Test_1', '18000', '16000', '35000', '2020-05-01');
SELECT t.ID, t.Name, t.Amount,
MAX(CASE WHEN LAST_DAY(t.Date) = LAST_DAY(CURRENT_DATE) THEN t.cal_amt END) cal_amt,
MAX(CASE WHEN t.rn = 1 THEN t.Run_amt END) Run_amt
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) rn
FROM tablename
) t
GROUP BY t.ID, t.Name, t.Amount
| ID | Name | Amount | cal_amt | Run_amt |
|---|---|---|---|---|
| 1 | Test | 15000 | 12000 | 15000 |
| 2 | Test_1 | 18000 | 16000 | 25000 |
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(CASE WHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END)
OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) cal_amt,
FIRST_VALUE(Run_amt) OVER (PARTITION BY ID, Name, Amount ORDER BY Date DESC) Run_amt
FROM tablename
) t
GROUP BY t.ID, t.Name, t.Amount
| ID | Name | Amount | cal_amt | Run_amt |
|---|---|---|---|---|
| 1 | Test | 15000 | 12000 | 15000 |
| 2 | Test_1 | 18000 | 16000 | 25000 |