CODEWITHSUNDEEP
mysql
Ragnar921
Ragnar921

Reputation: 967

mySQL query to get results in between a range of months

The table structure:

+------+-------+--------+--------------------+
| year | month | profit | inventory_turnover |
+------+-------+--------+--------------------+
| 2017 | 7     | 10000  | 2.47               |
| 2017 | 8     | 5000   | 2.47               |
| 2017 | 9     | 10000  | 4.5                |
| 2017 | 10    | 30000  | 6.7                |
| 2017 | 11    | 20000  | 1.7                |
| 2017 | 12    | 15000  | 2.8                |
| 2018 | 01    | 15000  | 1.8                |
+------+-------+--------+--------------------+

I want to get the results which are in between 08th month of 2017 and 01st month of 2018.

Current query is :

select * from monthly_records where monthly_records.month between 10 and 01 and monthly_records.year between 2017 and 2018 ORDER BY ID;

But it returns an empty set.

Upvotes: 0

Views: 35

Answers (3)

Udara Seneviratne
Udara Seneviratne

Reputation: 2483

You can compose a temporary date field called tmp_date using the month and year fields. Then compare that temporary date field with the date range using the between operator. Group By and having clauses needed to be used.

select id, STR_TO_DATE(CONCAT('01',',',month,',',year),'%d,%m,%Y') as tmp_date from monthly_records group by id having tmp_date between '2017-12-01' and '2018-01-02';

Upvotes: 2

Gaurang Dave
Gaurang Dave

Reputation: 4046

Try this

SELECT *
FROM monthly_records
WHERE month IN (8,9,10,11,12) AND year IN (2017)
ORDER BY ID;

UNION / UNION ALL

SELECT *
FROM monthly_records
WHERE month IN (1) AND year IN (2018)
ORDER BY ID;

Select UNION/ UNION ALL as per need.

OR

without UNION/UNION ALL

SELECT * FROM monthly_records WHERE (month >= 8 AND year = 2017) or (month = 1 AND year = 2018) ORDER BY ID;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

With your current table structure:

SELECT *
FROM monthly_records
WHERE (year = 2017 and month >= 8) or (year = 2018 and month <= 1)
ORDER BY ID;

I strongly recommend not storing the year and month separately. We got away with a fairly easy query, but things can get out of hand for more complex cases. Note that when using bona fide dates, we always would have just two date points to check.

Keep it simple and just use date literals here:

SELECT *
FROM monthly_records
WHERE date >= '2017-08-01' AND date < '2018-02-01'
ORDER BY ID;

Upvotes: 3

Related Questions