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

You should use PHP to get count for every month in the range, SQL alone will not return the data you need (easily).

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

Assuming your query range begins 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 seats were taken during each month of your range.

Basically loop through each month in the range, and count all the students that were enrolled on or prior to that month AND whose enrollment hadn't ended prior to that month.

Below, I'm populating an array called $enrollmentByMonth that will hold the count of the number of students stored by [year][month] and then just subtracting them from the capacity of the room and storing 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 loop, printing printing the desired info is a trivial matter:

// 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 test it, so there could be a typo or something I overlooked, but hopefully this points you in a good direction..!

Answer 2

For this question a small demo was set up.

Based on your database screenshots

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 get you the remaining seats available 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 example results in SQL Fiddle

Answer 3

Looks like there's a few people here with solutions that will give you results for months that aren't entirely free (if they are entirely free, no row is returned).

A good solution would be to return the numbers occupied for each month, then in your code build a list of months in the range you care about, and subtract the results of your query from the relevant month's default "fully free" value.

But, if it's really important that you do this purely in MySQL for some reason, there's no general way to say "give me a list of values within a given range", but if you're not averse to building a list of months you care about before executing the query, you can probably do it with a large union statement and a left join, eg:

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 will need to be heavily modified to correct ambiguous column names etc and add in the logic so any student appointments straddling a given year and month are included in the counts and it accounts for stuff correctly... the other answers already do that, I won't bother redoing it :)

Just a thought on a possible approach, the above is not meant to be taken directly as an answer.

Answer 4

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

Answer 5

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