Need to get a record for every month in MYSQL

Question

I need help getting a record from a database with some condition, as I explain below: -

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

Table structure: -

Table data: -

Example: -

Here you can take a classroom as 20 students, for example.

I want to check the availability of the place from 02/2016 to 04/2017.

The 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 

in advance for help.

Answer 1

I found your question like this, if there is a number of students, and they can register at any time per month, this does not count, but yes, if you can decide what will compare with any date, such as 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 reviewed with startDate.

Please let me know if I need more research.

khajaamin

Answer 2

To get the required results you need to use a subquery or a standalone connection, see the 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) 

Exit (based on the entries shown in the screenshot):

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

Answer 3

It looks like there are several people here with solutions that will give you results in a few months that are not completely free (if they are completely free, not a single line is returned).

A good solution would be to return the numbers occupied for each month, and then in your code build a list of months in the range of interest to you and subtract the results of your query from the corresponding default “completely free” default value.

But if it is really important that you do this exclusively in MySQL for some reason, there is no general way to say “give me a list of values ​​in a given range,” but if you are not averse to creating a list of months that you care about before executing the query, you can probably do this with the big join operator and left join, for example:

 SELECT Year, Month, {maxfree} - COUNT(whatever) FROM ( SELECT 2016 AS Year, 1 AS Month UNION SELECT 2016 AS Year, 2 AS Month UNION ... etc ... ) AS MonthList LEFT JOIN TableName ON YEAR(TableName.whatever) = MonthList.Year AND MONTH(TableName.whatever) = MonthList.Month GROUP BY Year, Month 

etc. the above should be greatly modified to correct ambiguous column names, etc. and add logic so that any assignments of students covering a certain year and month are included in the calculations, and this takes things into account correctly ... other answers already do this, I won’t redo it :)

Just the thought of a possible approach, the foregoing does not imply that it is perceived directly as an answer.

Answer 4

A small demonstration was created for this issue.

Based on snapshots of your database

Example table schema, data, and query

 create table `test` ( `id` int(11), `schoolid` int(11), `classroomid` int(11), `studentname` varchar(40), `startmonth` int(2) unsigned zerofill, `startyear` year, `endmonth` int(2) unsigned zerofill, `endyear` year); insert into `test` ( `id`, `schoolid`, `classroomid`, `studentname`, `startmonth`, `startyear`, `endmonth`, `endyear`) values (1,1,1,'ccc',3,2016,4,2017), (2,1,2,'bbb',05,2016,3,2017), (3,1,2,'aaa',12,2016,7,2017), (4,1,2,'bbb',05,2016,3,2017), (5,1,1,'bbb',09,2016,2,2017), (6,1,2,'bbb',06,2016,4,2017), (7,1,3,'bbb',03,2016,3,2017), (8,1,3,'bbb',01,2016,1,2017), (9,1,3,'bbb',11,2016,5,2017); 

This query will provide you with the remaining seats in each class based on your month and year pairs.

 select `classroomid`,'02' as `start_month`,'2016'`start_year`,'04'`end_month`,'2017'`end_year`,(20-count(`id`)) as `seats_left_in_class` from `test` where `startmonth`>=2 and `startyear`=2016 and `endmonth`<=4 and `endyear`=2017 group by `classroomid` order by `classroomid`; 

You can check the results in SQL Fiddle

Answer 5

You should use PHP to calculate for each month in the range, only SQL will not return the data you need (easily).

Use SQL to return a result set containing all students that started before your range or ended after your range.

Assuming the query range starts with $fromDate and ends with $toDate :

 SELECT startDate, endDate FROM tableName WHERE classroomID = $classroomID AND ( (startDate BETWEEN $fromDate AND $toDate) OR (endDate BETWEEN $fromDate AND $toDate) OR (startDate < $fromDate AND endDate > $toDate) ) 

Then use PHP to determine how many places were taken during each month of your range.

Basically, the cycle every month in the range and count all students who were registered before or before this month and whose registration has not ended before this month.

Below I fill in an array called $enrollmentByMonth , which will contain a count of the number of students stored in [year][month] , and then simply subtracts them from the room’s capacity and save it in $openSeats[year][month]

Assuming $result is an array of rows returned from the database:

 $year = date('Y', $fromDate); $month = date('m', $fromDate); $endYear = date('Y', $toDate); $endMonth = date('m', $toDate); $enrollmentByMonth = Array(); $openSeats = Array(); // loop through each month in range while ($year <= $endYear){ while ($year != $endYear || $month <= $endMonth){ $loopDate = mktime(0, 0, 0, $month, 1, $year); // (hr,min,sec,mon,day,year) // loop through each db result row to tally up how // many students enrolled for every month/year combo foreach($result as $row){ $rowStartDate = strtotime($row['startDate']); $rowEndDate = strtotime($row['endDate']); // if student registered prior to loopDate AND student // is still registered as of loopDate THEN count it if($rowStartDate <= $loopDate && $rowEndDate >= $loopDate){ $enrollmentByMonth[$year][$month]++; } } $openSeats[$year][$month] = $roomCapacity - $enrollmentByMonth[$year][$month]; $month++; if($month > 12){ $month=1; $year++; } } } 

after this cycle, printing the print of the necessary information is a trivial task:

 // print out data in the desired format foreach($openSeats as $year=>$months){ foreach($months as $month=>$openSeatsThisMonth){ echo "$month/$year - $openSeatsThisMonth<br>"; } } 

Sorry, I don’t have time to check it, so there may be a typo or something that I lose sight of, but I hope this indicates that you are in a good direction ..!