CODEWITHSUNDEEP
mysqlsqldate
Peter
Peter

Reputation: 173

MySQL generating monthly breakdown from date ranges

good morning,

I have a nagging issue I cannot really solve.. I have a database table like this, showing resources spent (value) in a date range by person:

    id,name,startdate,enddate,value
    --------------------------------
    10,John,2012-01-14,2012-10-30,200000
    11,Jack,2012-02-01,2012-08-01,70000
    12,John,2012-05-01,2012-06-01,2000

I need a query that creates the result like this, summarizing the 'value' by month, taking partial months into account

month, name, value
------------------    
2012-01, John, 9000
2012-02, John, 18000
2012-03, John, 18000
2012-04, John, 18000
2012-05, John, 20000
2012-06, John, 18000
2012-07, John, 18000
2012-08, John, 18000
2012-01, John, 18000
2012-02, Jack, 10000
2012-03, Jack, 10000
2012-04, Jack, 10000
2012-05, Jack, 10000
2012-06, Jack, 10000
2012-07, Jack, 10000
2012-08, Jack, 0

Now I know how I'd do this procedurally (like with PHP) with a loop: get the daily amount, then check month by month how many days spent there according to the range and multiply it by the daily amount.

thanks peter

Upvotes: 0

Views: 1192

Answers (2)

user359040
user359040

Reputation:

If you don't have a calendar table and can't create one, you can simulate a virtual calendar table in your query. Here's a query that should answer your question, that makes use of such a virtual table:

select m.startmonth,
       e.name, 
       coalesce(sum(r.value *
                    datediff(case when adddate(m.startmonth, interval 1 month) <
                                       r.enddate 
                                  then adddate(m.startmonth, interval 1 month) 
                                  else r.enddate end,
                             case when m.startmonth > r.startdate 
                                  then m.startmonth else r.startdate end) / 
                    datediff(r.enddate,r.startdate)),0) valueshare
from
(select cast('2012-01-01' as date) startmonth union all
 select cast('2012-02-01' as date) startmonth union all
 select cast('2012-03-01' as date) startmonth union all
 select cast('2012-04-01' as date) startmonth union all
 select cast('2012-05-01' as date) startmonth union all
 select cast('2012-06-01' as date) startmonth union all
 select cast('2012-07-01' as date) startmonth union all
 select cast('2012-08-01' as date) startmonth union all
 select cast('2012-09-01' as date) startmonth union all
 select cast('2012-10-01' as date) startmonth) m
cross join employees e
left join resources_spent r 
       on r.enddate > m.startmonth and 
          r.startdate < adddate(m.startmonth, interval 1 month) and
          r.name = e.name
group by m.startmonth, e.name
order by 2,1

SQLFiddle here.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

I think you need a calendar table with one row for each date. Other fields would be whatever is useful to you, such as fiscal periods, holidays, whatever.

Then, for your report, you could create a temp table and populate it like this:

insert into YourTempTable
(id, date, amount)
select id, c.datefield, amount
from YourTable join Calendar c on datefield >= startdate
and datefield <= enddate
where whatever

From there, you select from YourTempTable and YourTable, joining on the id.

Upvotes: 0

Related Questions