Need to fetch record for each month in MYSQL

Question

I need help to fetch record from the database with some condition as I explain below :-

I need to check the student availability for each month between selected startDate and end Date.

Table Structure :-

Table data :-

Example :-

Here you can take capacity of classroom as 20 students for example.

I want to check the seat availablity from 02/2016 to 04/2017.

Output will be :

02/2016 - 20
03/2016 - 19
04/2016 - 18
05/2016 - 15
06/2016 - 20
.
.
.
02/2017 - 14
03/2017 - 20
04/2017 - 18

thanks in advance for help.

Answer 1

I found your question like this if there are number of students and they can enroll any time in month this is not considered but yes if you can decide that will compare to any one date like startDate or endDate this query will definitely help you.

SELECT count(*) as cnt,CONCAT(MONTH(startDate),'/',  YEAR(startDate)) as day from notes group by day 

I considered with startDate.

Please let me know if i need more research .

khajaamin

Answer 2

You need to use sub query or self join to get the required results, see example below:

SELECT LEFT(startDate,7) startMY, LEFT(endDate,7) endMY, (
    SELECT 20 - COUNT(*) FROM `tablename` B 
    WHERE LEFT(A.startDate,7) >= LEFT(B.startDate,7) AND 
          LEFT(A.endDate,7) <= LEFT(B.endDate,7)
) balanceCapacity FROM `tablename` A
GROUP BY LEFT(A.startDate,7), LEFT(A.endDate,7)

Output (based on records shown in screenshot):

+---------+---------+-----------------+
| startMY | endMY   | balanceCapacity |
+---------+---------+-----------------+
| 2015-12 | 2017-07 |              19 |
| 2016-03 | 2017-04 |              18 |
| 2016-05 | 2017-03 |              17 |
+---------+---------+-----------------+