CODEWITHSUNDEEP
mysql
Athulya
Athulya

Reputation: 181

How can i get ongoing project count in each month using MYSQL?

I have the table like this

Title    | start_date |end_date   | 
------   | ---------- |---------  |
Test_pro1 | 2017-06-06 |2017-08-06 |
Test_pro2 | 2017-07-06 |2017-11-06 |

I need the ongoing project count in each month.

Example 1 : 2017-06-06 project count = 1
Example 2 : 2017-07-06 project count = 2
Example 3 : 2017-11-06 project count = 0

How can i write a query in MYSQL?

Upvotes: 2

Views: 353

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

I think the conceptually easiest way to handle this is to use a calendar table (i.e. a table just containing dates, representing each month), and join this to your current table. The join condition is that a given year/month falls somewhere inside the range for a current project. We can then group the result set by year/month (i.e. date), and count how many projects fall into each month.

Note that in the query below I have inlined a makeshift calendar table containing years and months for 2017 alone. Ideally, you would have a bona fide calendar table in your schema, containing however wide a timespan you would need to cover all projects.

SELECT
    t1.year,
    t1.month,
    COUNT(t2.start_date) AS project_count
FROM
(
    SELECT 2017 AS year, 1 AS month UNION ALL
    SELECT 2017, 2  UNION ALL
    SELECT 2017, 3  UNION ALL
    SELECT 2017, 4  UNION ALL
    SELECT 2017, 5  UNION ALL
    SELECT 2017, 6  UNION ALL
    SELECT 2017, 7  UNION ALL
    SELECT 2017, 8  UNION ALL
    SELECT 2017, 9  UNION ALL
    SELECT 2017, 10 UNION ALL
    SELECT 2017, 11 UNION ALL
    SELECT 2017, 12
) t1
LEFT JOIN yourTable t2
    ON (t1.year >= YEAR(t2.start_date) AND t1.month >= MONTH(t2.start_date)) AND
       (t1.year <= YEAR(t2.end_date)   AND t1.month <= MONTH(t2.end_date))
GROUP BY t1.year, t1.month;

In the output below I show all months for 2017. If you only wanted to report months having a non zero count for projects, you could either switch the join to INNER JOIN or add a HAVING clause which checks the project count.

Output:

enter image description here

Demo here:

Rextester

Upvotes: 0

etsa
etsa

Reputation: 5060

May be your are looking for something like this (change YOUR_DATE with the value you want to check)?

SELECT  COUNT(*) AS RC
FROM  YOUR_TABLE
WHERE   START_DATE <= YOUR_DATE AND END_DATE >= YOUR_DATE

Upvotes: 0

nacho
nacho

Reputation: 5397

You could do something like this:

SELECT MONTH(start_date),count(title)
from projects
group by month(start_date)

Upvotes: 1

Related Questions