Tuesday, July 1, 2014

#projectserver SQL to calculate running totals by month (work, actuals, Baseline)


When calculating running totals (for S-Curve/BurnUp reports) I have had to transform the periodic data returned from SQL into running totals in a pivot or used the running value SQL RS command to transform the data.

So, fast forward to happy SQL2012 land, and we have some useful script commands that can now be used.  Lets face it, our SQL boxes are usually packed to the brim with RAM and Cores these days so why not use them for what they're built for!

The following script calculates the running totals by month by project for all the normal useful measures. I am in no way saying this is an optimised script, it's just my first kick out at this.

In all honesty you'd probably want to remove the _userview references and go to the tables.  

Enjoy, ignore, laugh at the naivety to your hearts content

--SQL Script to return running totals (RTx) for Projects by Month
Select p.ProjectName
,data.month
,data.RTAssnWork
,sum(data.work)
,data.RTAssnActualWork
,sum(data.Actwork)
,data.RTAssnBAseWork
,sum(data.Basework)

from
(
select a.Projectuid,dateadd(month,datediff(month,0,a.timebyday),0) as month



,sum(a.assignmentwork) over (PArtition by a.projectuid ORDER BY dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnWork

,a.AssignmentWork as work

,sum(a.AssignmentActualWork) over (PArtition by a.projectuid ORDER BY dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnActualWork


,a.AssignmentActualWork as Actwork

,sum(a.AssignmentBaseline0Work) over (PArtition by a.projectuid ORDER BY               dateadd(month,datediff(month,0,a.timebyday),0) ) as RTAssnbaseWork
,a.AssignmentBaseline0Work as Basework

from MSP_EpmAssignmentByDay_UserView a 
Group by a.projectuid,dateadd(month,datediff(month,0,a.timebyday),0), AssignmentWork,AssignmentActualWork,assignmentbaseline0work
Data

left join msp_epmproject_Userview p on data.projectuid = p.ProjectUID

group by p.ProjectName,data.month,data.RTAssnWork,data.RTAssnActualWork,data.RTAssnbaseWork

order by p.ProjectName, data.month