How to loop/pivot between start and end date to display monthly cost

Question

I have a table with a PROJECTNAME, ACTUALCOST, PROJECTSTART and PROJECTEND columns like so:

PROJECTNAME          ACTUAL COST          PROJECTSTART          PROJECTEND
abc                  1500                 2011-12-01            2012-07-31
prj1                 1170                 2012-01-09            2012-06-30  
xyz                  5350                 2012-01-30            2012-03-30

I am trying to get an output like below:

PRJNAME     DEC11     JAN12     FEB12     MAR12     APR12     MAY12     JUN12     JUL12 ...
abc         187.5     187.5     187.5     187.5     187.5     187.5     187.5     187.5
prj1                  117       195       195       195       195       195

I need the monthly cost breakdowns of each project name, depending on how many days left are in each month.

here's a good answer the explains PIVOT, a good solution for what you need: http://stackoverflow.com/questions/7232593/pivot-data-in-t-sql — Tahbaza, Feb 19 '13 at 03:34
You can find many questions on this site about ["dynamic pivots"](http://stackoverflow.com/search?q=sql+dynamic+pivot), but this would be a lot easier in a front end application or reporting tool than in TSQL. — Pondlife, Feb 19 '13 at 20:31
Thanks for the responses, but these examples use column header names that already exist as column values. I need to dynamically create header names based on PROJECTSTART and PROJECTEND columns. — 0onetwo, Feb 20 '13 at 01:13

score 1 · Accepted Answer · answered Feb 20 '13 at 01:43

You can use the PIVOT function to get the result.

The way I would recommend doing this would be to write a hard-coded version of this first, then convert it to dynamic sql.

Note: I determined the cost per month by dividing the Actual Cost by the number of days per between the projectstart/projectend. This should at least get you started for your actual report.

The static version would be similar to this:

;with cte as
(
  select projectname, [ACTUAL COST], [PROJECTSTART], [PROJECTEND]
  from yourtable
  union all
  select projectname, [ACTUAL COST], 
    dateadd(d, 1, PROJECTSTART),
    PROJECTEND
  from cte
  where dateadd(d, 1, PROJECTSTART) <= ProjectEnd
) 
select *
from
(
  select 
    my.projectname,
    my.monthyear,
    my.totaldayspermonth * a.perdaycost AmountPerMonth
  from
  (
    select projectname,
    left(datename(m, projectstart), 3) + cast(year(projectstart) as varchar(4)) monthyear,
    count(*) TotalDaysPerMonth
    from cte
    group by projectname,
      [actual cost],
      left(datename(m, projectstart), 3) + cast(year(projectstart) as varchar(4))
  ) my
  cross apply
  (
    select projectname, 
      round([actual cost] / (datediff(d, projectstart, projectend) *1.0), 2) PerDayCost
    from yourtable a
    where my.projectname = a.projectname
  ) a
) src
pivot
(
  max(AMOUNTPERMONTH)
  for monthyear in (Dec2011, Jan2012, Feb2012, Mar2012,
                    Apr2012, May2012, Jun2012, Jul2012, Aug2012)
) piv
OPTION(MAXRECURSION 0);

See SQL Fiddle with Demo.

Once you have a static version, it makes it much easier to convert it to dynamic SQL. The dynamic SQL would be:

;with cte as
(
  select projectname, [ACTUAL COST], [PROJECTSTART], [PROJECTEND]
  from yourtable
  union all
  select projectname, [ACTUAL COST], 
    dateadd(d, 1, PROJECTSTART),
    PROJECTEND
  from cte
  where dateadd(d, 1, PROJECTSTART) <= ProjectEnd
) 
select *
into #dates
from cte
OPTION(MAXRECURSION 0)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(left(datename(m, projectstart), 3) + cast(year(projectstart) as varchar(4))) 
                    from #dates
                    group by datename(m, projectstart), year(projectstart), month(projectstart)
                    order by year(projectstart), month(projectstart)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ';with cte as
              (
                select projectname, [ACTUAL COST], [PROJECTSTART], [PROJECTEND]
                from yourtable
                union all
                select projectname, [ACTUAL COST], 
                  dateadd(d, 1, PROJECTSTART),
                  PROJECTEND
                from cte
                where dateadd(d, 1, PROJECTSTART) <= ProjectEnd
              ) 
              select projectname, '+@cols+' 
              from
              (
                select 
                  my.projectname,
                  my.monthyear,
                  my.totaldayspermonth * a.perdaycost AmountPerMonth
                from
                (
                  select projectname,
                  left(datename(m, projectstart), 3) + cast(year(projectstart) as varchar(4)) monthyear,
                  count(*) TotalDaysPerMonth
                  from cte
                  group by projectname,
                    [actual cost],
                    left(datename(m, projectstart), 3) + cast(year(projectstart) as varchar(4))
                ) my
                cross apply
                (
                  select projectname, 
                    round([actual cost] / (datediff(d, projectstart, projectend) *1.0), 2) PerDayCost
                  from yourtable a
                  where my.projectname = a.projectname
                ) a
              ) src
              pivot
              (
                max(AMOUNTPERMONTH)
                for monthyear in ('+@cols+')
              )piv
              OPTION(MAXRECURSION 0)'

execute(@query)

See SQL Fiddle with Demo

The result of both queries is:

| PROJECTNAME | DEC2011 | JAN2012 | FEB2012 | MAR2012 | APR2012 | MAY2012 | JUN2012 | JUL2012 |
-----------------------------------------------------------------------------------------------
|         abc |  191.27 |  191.27 |  178.93 |  191.27 |   185.1 |  191.27 |   185.1 |  191.27 |
|        prj1 |  (null) |  155.48 |  196.04 |  209.56 |   202.8 |  209.56 |   202.8 |  (null) |
|         xyz |  (null) |  178.34 | 2585.93 |  2675.1 |  (null) |  (null) |  (null) |  (null) |

How to loop/pivot between start and end date to display monthly cost

1 Answers1