asked on

distribute budget by months/year

I have the following table:

Project start date end date budget
--------- ----------- ------------- ------------
1 2010-01-01 2011-12-31 240
2 2011-06-30 2012-06-30 120

I would like to allocate the budget evenly by YEAR, so the end result would be something like this:

Project start date end date budget 2010 2011 2012
--------- ----------- ------------- ------------ -------- -------- ---------
1 2010-01-01 2011-12-31 240 120 120 0
2 2011-06-30 2012-06-30 120 0 60 60

Is there a way to achieve this using mysql ?
question.pdf

johanntagle

Something like:

mysql> create table phpmysqlcoder (project_id int, start_date date, end_date date, budget int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into phpmysqlcoder values (1, '2010-01-01', '2011-12-31', 240), (2, '2011-06-30','2012-06-30', 120);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select project_id, start_date, end_date, budget,
    ->  if(2010 between year(start_date) and year(end_date), budget/num_years, 0) as '2010',
    ->  if(2011 between year(start_date) and year(end_date), budget/num_years, 0) as '2011',
    ->  if(2012 between year(start_date) and year(end_date), budget/num_years, 0) as '2012'
    -> from 
    ->  (select project_id, start_date, end_date, budget, 
    ->   (year(end_date)-year(start_date) + 1) as num_years
    ->  from phpmysqlcoder
    ->  ) a;
+------------+------------+------------+--------+----------+----------+---------+
| project_id | start_date | end_date   | budget | 2010     | 2011     | 2012    |
+------------+------------+------------+--------+----------+----------+---------+
|          1 | 2010-01-01 | 2011-12-31 |    240 | 120.0000 | 120.0000 |       0 |
|          2 | 2011-06-30 | 2012-06-30 |    120 |        0 |  60.0000 | 60.0000 |
+------------+------------+------------+--------+----------+----------+---------+
2 rows in set (0.00 sec)

Open in new window

johanntagle

Version without using subquery, though I really dislike the repetitive "(year(end_date)-year(start_date) + 1"

mysql> select project_id, start_date, end_date, budget,
    ->  if(2010 between year(start_date) and year(end_date), budget/(year(end_date)-year(start_date) + 1), 0) as '2010',
    ->  if(2011 between year(start_date) and year(end_date), budget/(year(end_date)-year(start_date) + 1), 0) as '2011',
    ->  if(2012 between year(start_date) and year(end_date), budget/(year(end_date)-year(start_date) + 1), 0) as '2012'
    ->  from phpmysqlcoder;
+------------+------------+------------+--------+----------+----------+---------+
| project_id | start_date | end_date   | budget | 2010     | 2011     | 2012    |
+------------+------------+------------+--------+----------+----------+---------+
|          1 | 2010-01-01 | 2011-12-31 |    240 | 120.0000 | 120.0000 |       0 |
|          2 | 2011-06-30 | 2012-06-30 |    120 |        0 |  60.0000 | 60.0000 |
+------------+------------+------------+--------+----------+----------+---------+
2 rows in set (0.00 sec)

Open in new window

phpmysqlcoder

ASKER

Thanks johanntagle for this, I realized that I am was not clear in my phrasing, the budget should be distributed proportionally based on the number of months so if the start_date is Sept 1, 2010 and end_date is Dec 31, 2011 ...so the total duration in months => 4+12=16 months

year 2010 share will equal 4/16 X budget
year 2011 share will equal 12/16 X budget

Can we round the date? for example 10th will be rounded to the 1st

johanntagle

Not just your phrasing, even your sample desired output was wrong =). Anyway, that will complicate things, but it's still doable. But before I proceed, can I ask first if it's possible for a project to span across more than 2 years?

ASKER CERTIFIED SOLUTION

johanntagle

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

phpmysqlcoder

ASKER

I am impressed that you accomplished it without stored procedure/function. Good Job and Thanks.

phpmysqlcoder

ASKER

Hmmm, i am trying to make a view out of it but getting the following error:

SQL Error(1349): view's SELECT contains a subquery in the FROM clause

any ideas, I need it as a view

phpmysqlcoder

ASKER

I found the solution, the workaround is to create a view for the subquery

johanntagle

I recently answered a question on that see https://www.experts-exchange.com/questions/27726839/SQL-Error-View's-SELECT-contains-a-subquery-in-the-FROM-clause.html. Basically create a view using the subquery then create another view that uses that view:

mysql> create view v1 as select project_id, start_date, end_date, budget,
    ->     period_diff(date_format(end_date,'%Y%m'),date_format(start_date,'%Y%m')) + 1 as num_months,
    ->     case 
    ->       when year(start_date)<2010 and year(end_date)=2010 then period_diff(date_format(end_date,'%Y%m'),201001) + 1
    ->       when year(start_date)<2010 and year(end_date)>2010 then 12
    ->       when year(start_date)=2010 and year(end_date)=2010 then period_diff(date_format(end_date,'%Y%m'),date_format(start_date,'%Y%m')) + 1
    ->       when year(start_date)=2010 and year(end_date)>2010 then period_diff('201012',date_format(start_date,'%Y%m')) + 1
    ->       else 0 
    ->     end as 'months2010',
    ->     case 
    ->       when year(start_date)<2011 and year(end_date)=2011 then period_diff(date_format(end_date,'%Y%m'),201101) + 1
    ->       when year(start_date)<2011 and year(end_date)>2011 then 12
    ->       when year(start_date)=2011 and year(end_date)=2011 then period_diff(date_format(end_date,'%Y%m'),date_format(start_date,'%Y%m')) + 1
    ->       when year(start_date)=2011 and year(end_date)>2011 then period_diff('201112',date_format(start_date,'%Y%m')) + 1
    ->       else 0
    ->     end as 'months2011',
    ->     case 
    ->       when year(start_date)<2012 and year(end_date)=2012 then period_diff(date_format(end_date,'%Y%m'),201201) + 1
    ->       when year(start_date)<2012 and year(end_date)>2012 then 12
    ->       when year(start_date)=2012 and year(end_date)=2012 then period_diff(date_format(end_date,'%Y%m'),date_format(start_date,'%Y%m')) + 1
    ->       when year(start_date)=2012 and year(end_date)>2012 then period_diff('201212',date_format(start_date,'%Y%m')) +1
    ->       else 0
    ->     end as 'months2012'
    -> from phpmysqlcoder;
Query OK, 0 rows affected (0.05 sec)

mysql> create view v2 as select project_id, start_date, end_date, budget, 
    ->     budget * months2010 / num_months as '2010', 
    ->     budget * months2011 / num_months as '2011',  
    ->     budget * months2012 / num_months as '2012'
    -> from v1 order by project_id;
Query OK, 0 rows affected (0.07 sec)

mysql> select * from v2;
+------------+------------+------------+--------+----------+----------+----------+
| project_id | start_date | end_date   | budget | 2010     | 2011     | 2012     |
+------------+------------+------------+--------+----------+----------+----------+
|          1 | 2010-01-01 | 2011-12-31 |    240 | 120.0000 | 120.0000 |   0.0000 |
|          2 | 2011-06-30 | 2012-06-30 |    120 |   0.0000 |  64.6154 |  55.3846 |
|          3 | 2010-05-01 | 2012-04-30 |    360 | 120.0000 | 180.0000 |  60.0000 |
|          4 | 2009-01-01 | 2010-08-30 |    120 |  48.0000 |   0.0000 |   0.0000 |
|          5 | 2009-01-01 | 2011-12-30 |    360 | 120.0000 | 120.0000 |   0.0000 |
|          6 | 2009-01-01 | 2013-12-30 |    600 | 120.0000 | 120.0000 | 120.0000 |
+------------+------------+------------+--------+----------+----------+----------+
6 rows in set (0.00 sec)

Open in new window