0

My table is something like this

Allocated_cost,Start_date,End_date
12,000          11/2014,11/2015
12,000          12/2014,12/2015

I would like to calculate rolling cost by month, for example if you look above cost for 11/14 & 12/15 is $1000 per month whereas the rest is $2000/month.I have to show this inform of graph.This can be done either in ruby or sql. Can someone help with the logic, all I need is some kind of array 

  [11/2014,1000],[12/2014,2000]...etc.

Just need some help with the logic..

Thanks

user501307
  • 59
  • 1
  • 2
  • 9

1 Answers1

0

Here's what I'd do in SQL Server:

declare @CostPeriod table
(
    AllocatedCost money not null
    ,StartDate date
    ,EndDate date
)

insert @CostPeriod
select 12000,'2014-11-01','2015-11-01'
union select 12000,'2014-12-01','2015-12-01'

declare @start date
, @stop date

select @start = MIN(startdate)
, @stop = MAX(enddate)
from @CostPeriod

select [Month]
, SUM(AllocatedCost / (DATEDIFF(month,startdate,enddate))) ValueThisMonth
from (select dateadd(month,n,@start) [Month] from dbo.generate_series(0, DATEDIFF(month,@start,@stop), 1, 0)) x
left outer join @CostPeriod on [Month] between StartDate and EndDate
group by [Month]
order by [Month]

Using Generate_Series code from here: https://developer42.wordpress.com/tag/generate_series/

Will have a go at converting to MySQL now / will post back soon - hopefully this gives you something to go on whilst you wait though...

JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • NB: can't access SQLFiddle to play with MySQL atm; in the meantime take a look at: http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates - to get the list of dates between min start and max end dates, then use the logic above to split the costs out over that period. – JohnLBevan Nov 17 '14 at 22:05