Avatar of phpmysqlcoder
phpmysqlcoder
Flag for Denmark 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
MySQL Server

Avatar of undefined
Last Comment
johanntagle

8/22/2022 - Mon
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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/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