CODEWITHSUNDEEP
mysqlmysql-workbench
Anil Kumar Reddy
Anil Kumar Reddy

Reputation: 137

MySQL query to get data dynamically based on financial year

I want to select fields dynamically based on financial year (i.e., for the year of Apr'18 to Mar'19). I have data in a table exact as given below :

ROWNO   YEAR    MONTH
1       2016    1
2       2016    2
3       2016    6
4       2017    7
5       2017    5
6       2018    4
7       2018    5
8       2018    6
9       2018    7
10      2018    8
11      2018    9
12      2018    10
13      2018    11
14      2018    12
15      2019    1
16      2019    2
17      2019    3
18      2019    3
19      2017    4
20      2017    1
21      2017    2
22      2018    3

I want to get the result as shown below for the financial year 2018-19 are

ROWNO   YEAR    MONTH
6       2018    4
7       2018    5
8       2018    6
9       2018    7
10      2018    8
11      2018    9
12      2018    10
13      2018    11
14      2018    12
15      2019    1
16      2019    2
17      2019    3

I used Query SELECT * FROM rup_calendar WHERE YEAR BETWEEN YEAR(CURDATE()) AND YEAR(CURDATE())+1; but not able to get exact result.

Please give me the query to get the result as given above.

Upvotes: 0

Views: 180

Answers (1)

P.Salmon
P.Salmon

Reputation: 17665

Given the data you have your approach is not far away

select rowno,year,month
from     t
where year * 100 + month between 
    case when month(now()) < 4 then (year(now()) - 1) * 100 + 3
    else year(now()) * 100 + 4
    end 
    and
    case when month(now()) < 4 then (year(now())) * 100 + 3
    else (year(now()) + 1)  * 100 + 4
    end

Notice I have calulated a yearmonth field to ease comparison. Note I haven't fully tested this and you should do so by substituting an @ variable for now() to test.

Upvotes: 2

Related Questions