distribute budget by months/year

phpmysqlcoder
phpmysqlcoder used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
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

Top Expert 2012

Commented:
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

Author

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2012

Commented:
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?
Top Expert 2012
Commented:
Okay this was a tough one.  I took into account projects spanning more than 2 years and those starting/ending outside 2010-2012, though only getting the budget for those years.

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   
(
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  
) a
order by project_id;

Open in new window


In  action (inserted additional test cases):
mysql> insert into phpmysqlcoder values (3, '2010-05-01', '2012-04-30', 360), (4, '2009-01-01','2010-08-30', 120), (5, '2009-01-01', '2011-12-30',360), (6, '2009-01-01','2013-12-30',600);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from phpmysqlcoder order by project_id;
+------------+------------+------------+--------+
| project_id | start_date | end_date   | budget |
+------------+------------+------------+--------+
|          1 | 2010-01-01 | 2011-12-31 |    240 |
|          2 | 2011-06-30 | 2012-06-30 |    120 |
|          3 | 2010-05-01 | 2012-04-30 |    360 |
|          4 | 2009-01-01 | 2010-08-30 |    120 |
|          5 | 2009-01-01 | 2011-12-30 |    360 |
|          6 | 2009-01-01 | 2013-12-30 |    600 |
+------------+------------+------------+--------+
6 rows in set (0.00 sec)

mysql> 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   
    -> (
    -> 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  
    -> ) a
    -> order by project_id;
+------------+------------+------------+--------+----------+----------+----------+
| 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


Note that for project_id=2, it's interpreted as 13 months because it started on a June (though end of june) and ended on a june.  If you change the start date to July 1 you will get 12 months.  Suggest you just be strict with your convention for setting start and end dates because coming up with a rounding function that will determine if it should be rounded to the start of the current month or to the next month will make things even more complicated.

Whew =)  Though I don't mind the challenge, given the amount of time I spent this, I would appreciate if you do not delay in giving feedback.  Thanks.

Author

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

Author

Commented:
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

Author

Commented:
I found the solution, the workaround is to create a view for the subquery
Top Expert 2012

Commented:
I recently answered a question on that see http://www.experts-exchange.com/Database/MySQL/Q_27726839.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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial