CODEWITHSUNDEEP
sqlsql-server
Bob
Bob

Reputation: 127

Aggregate a table by Month

I have a rather interesting problem. I need to aggregate a table by month when I have it broken out by Day. It does not seem that a grouping will work so I am not sure where to go. I would prefer to do this as a Year not as a function (Permissions and all) Right now the table is by Day which is not too handy (Who wants to see a project resource cost every day over a 9 month project) I need to roll all this up so that rather than by day it is by month. I need to group by month for ProjectUID, AssignmentUID, ResourceUID, TaskUID Then the other fields get summed up. So I have something that looks like Total cost for this project, for this assignment, this resource, for this task - for the month (not the day) I am not sure where to even start. A part of the table looks like this: ProjectUID, TimeByDay, AssignmentUID, ResourceUID, TaskUID, AssignmentCost, resourcePlanCost (many other columns that would need totaled)

    SELECT [AssignmentUID]
  ,[ResourcesUID]
  ,[TimeByDay]
  ,[ProjectUID]
  ,[TaskUID]
  ,[AssignmentCost]
  ,[AssignmentOvertimeCost]
  ,[AssignmentActualCost]
  ,[AssignmentActualOvertimeCost]
  ,[AssignmentWork]
  ,[AssignmentOvertimeWork]
  ,[AssignmentActualWork]
  ,[AssignmentActualOvertimeWork]
  ,[AssignmentMaterialWork]
  ,[AssignmentMaterialActualWork]
  ,[AssignmentBudgetCost]
  ,[AssignmentBudgetWork]
  ,[AssignmentBudgetMaterialWork]
  ,[AssignmentResourcePlanWork]
  ,[TaskIsActive]
  ,[AssignmentRegularCost]
  ,[AssignmentRemainingCost]
  ,[AssignmentRemainingOvertimeCost]
  ,[AssignmentActualRegularCost]
  ,[AssignmentRemainingRegularCost]
  ,[AssignmentRegularWork]
  ,[AssignmentRemainingWork]
  ,[AssignmentRemainingOvertimeWork]
  ,[AssignmentActualRegularWork]
  ,[AssignmentRemainingRegularWork]
  ,[AssignmentCombinedWork]
  ,[AssignmentCount]
   ,[AssignmentBaseline4Cost]
  ,[AssignmentBaseline4Work]
  ,[AssignmentBaseline4MaterialWork]
  ,[AssignmentBaseline4BudgetCost]
  ,[AssignmentBaseline4BudgetWork]
  ,[AssignmentBaseline4BudgetMaterialWork]
  ,[AssignmentBaseline5Cost]
  ,[AssignmentBaseline5Work]
  ,[AssignmentBaseline5MaterialWork]
  ,[AssignmentBaseline5BudgetCost]
  ,[AssignmentBaseline5BudgetWork]
  ,[AssignmentBaseline5BudgetMaterialWork]
  FROM [PSIT].[dbo].[MSP_EpmAssignmentByDay_UserView]

this is from Project Server 2013 any help would be appreciated (Analysis Services is not available so I can just build a cube.)

Upvotes: 0

Views: 150

Answers (1)

René Vogt
René Vogt

Reputation: 43876

Why wouldn't a GROUP BY work?

SELECT 
    ProjectUID, 
    AssignmentUID, 
    ResourceUID, 
    TaskUID, 
    SUM(AssignmentCost)
FROM
    [PSIT].[dbo].[MSP_EpmAssignmentByDay_UserView]
GROUP BY
    ProjectUID, 
    AssignmentUID, 
    ResourceUID, 
    TaskUID,
    DATEPART(year, TimeByDay), DATEPART(month, TimeByDay)

The DATEPART functions gives you the year and month of your TimeByDay and you can group by those values.

Upvotes: 2

Related Questions