CODEWITHSUNDEEP
mysql
T.Murakami
T.Murakami

Reputation: 15

Mysql query period data to each month

I'm trying create an SQL query to resolve my problem.
I use mysqk5.7.

My Table:
|project_id|start |end |cost(per month)| |1 |2018-05-01|2018-06-30|1000 | |2 |2018-06-01|2018-07-31|2000 |
I want to generate date-columns by start and end columns. like this:
|date |project_id|cost| |2018-05|1 |1000| |2018-06|1 |1000| |2018-06|2 |2000| |2018-07|2 |2000|

Upvotes: 1

Views: 971

Answers (2)

zedfoxus
zedfoxus

Reputation: 37129

Create a table and populate it with first day of each month. You can programmatically do that or even use Excel to generate data and port it to MySQL.

create table dates (
    start_date date
);

insert into dates values 
('2018-04-01'),
('2018-05-01'),
('2018-06-01'),
('2018-07-01'),
('2018-8-01');

Then, you can run a query like so:

Query

select 
    date_format(start_date, '%Y-%m') as `Date`,
    a.project_id,
    a.cost
from projects a
inner join dates b on b.start_date between a.start and a.end;

Result

Date    project_id cost
2018-05    1       1000
2018-06    1       1000
2018-06    2       2000
2018-07    2       2000

Example

http://rextester.com/JRIUZ98116

Alternative

The other alternative is to create a stored procedure that creates a temporary table containing dates so that you don't have to generate a table. Minimum start date and maximum end date from the table can be extracted to create the temporary table of dates.

Then, the stored procedure can do the same join as above to generate a resultset.

Upvotes: 1

TomC
TomC

Reputation: 2814

This is one of those places where a separate date table will make life much easier. If you have a table with something like this:

create table DateTable(ThisDate date, Month varchar(7))

adding whatever other columns you might need (isWeekday etc) and populate it in a loop then you will be able to re-use this for all sorts of things, including this query. For example you can create a view on it to get month, startdate, enddate, and then join from that back into your table looking for dates that are between the start and end date.

This and lots of other queries will become simple.

create table DateTable(ThisDate date, Month varchar(7)) 

--- populate the table just once, re-use for all future queries

create view MonthView as 
select Month, 
   min(ThisDate) as StartOfMonth, 
   max(ThisDate) as EndOfMonth 
from DateTable

select Month, ProjectID, Cost 
from MonthView
join MyTab on myTab.Start<=EndOfmonth and myTab.End>=StartofMonth

Upvotes: 0

Related Questions