How to Fetch records based on the Dates in MYSQL

Question

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    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

I need to get MAX(month) of Run_Amount i.e : 2020-06-01 --> 15000

Need to fetch cal_amt of current month i.e : 2020-05-01 --> 12000 and 0.00 also relates to this month 2020-04-01

I need to get output like this :

 ID  Name  Amount  cal_amt Run_amt  
 1   Test   15000    12000  15000 
 2   Test_1 18000    16000  25000

It is sample data but have another few more columns are there I have tried with MAX() conditions and

ROW_NUMBER()over (PARTITION BY run_amt order by Date )

Can anyone suggest me the best way

Can you clarify - you want the value of Run_amt from the latest Date, and the value of cal_amt from the current month, but what about the values for ID, Name and Amount? where should they come from? — Nick
– Nick, Commented May 1, 2020 at 9:25
@Nick yes you are right . ID,Name and Amount are there in the same table I can get them by using GROUP BY .I'm looking for Run_amt and cal_amt — mohan111
– mohan111, Commented May 1, 2020 at 9:28
You don't have enough sample data in the query to be clear as to how to select the output values. Please add some more with different ID, Name and Amount values... — Nick
– Nick, Commented May 1, 2020 at 9:29

forpas · Accepted Answer · 2020-05-01 12:37:16Z

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(CASE WHEN LAST_DAY(Date) = LAST_DAY(CURRENT_DATE) THEN cal_amt END) cal_amt,
  MAX(CASE WHEN t.rn = 1 THEN 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

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(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

See the demo.
Results:

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

Collectives™ on Stack Overflow

How to Fetch records based on the Dates in MYSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related