How to sum the Actualwork column values with specific resource

Question

Below there is the Query and its result .

In the query there is separate Actual Work on the basis of resource with different dates.

I want to Sum the Actual Work on this query on the basis of Resources.

One resource with its sum of actual work with all of the dates which provides in the start and end date of parameter

Query

--------

Select Distinct

T.ProjectName ,
R.ResourceName ,
R.[Resource Departments] Departments,

CAST((SUM(T.ActualWorkBillable)/8) as decimal(19,1)) AS ActualWork ,

CONVERT(VarChar(10),CAST(PeriodStartDate AS DATETIME),6) as StartDate ,

CONVERT(VarChar(10),CAST(PeriodEndDate AS DATETIME),6)as EndDate ,

@WorkingDays AS WorkingDays -- ,

FROM MSP_TimesheetLine_UserView T
 
Inner Join MSP_EPMResource_UserView R
 
ON T.[ResourceUID]=R.[ResourceUID]
 
Group By T.ProjectName,T.PeriodStartDate,T.PeriodEndDate,R.ResourceName,T.TimesheetLineStatus,R.[Resource Departments]

Having convert(VarChar(10),CAST(PeriodStartDate AS DATETIME),126) >= @StartDate AND convert(VarChar(10),CAST(PeriodEndDate AS DATETIME),126) <= @EndDate

AND SUM(T.ActualWorkBillable) <> 0

AND T.TimesheetLineStatus = 'APPROVED'
 
AND (@ResourceName = 'ALL' OR R.ResourceName = @ResourceName)

Result :

--------- 

Answer 1

Try the following solution with nested CTE-s:

;WITH cte 
     AS (SELECT DISTINCT T.projectname, 
                         R.resourcename, 
                         R.[resource departments] 
                            Departments, 
                         Cast(( Sum(T.actualworkbillable) / 8 ) AS 
                              DECIMAL(19, 1)) 
                         AS 
                            ActualWork, 
                         CONVERT(VARCHAR(10), Cast(periodstartdate AS DATETIME),
                          6) 
                         AS 
                            StartDate, 
                         CONVERT(VARCHAR(10), Cast(periodenddate AS DATETIME), 6
                          ) 
                         AS 
                            EndDate, 
                         @WorkingDays 
                         AS 
                            WorkingDays -- , 
         FROM   msp_timesheetline_userview T 
                INNER JOIN msp_epmresource_userview R 
                        ON T.[resourceuid] = R.[resourceuid] 
         GROUP  BY T.projectname, 
                   T.periodstartdate, 
                   T.periodenddate, 
                   R.resourcename, 
                   T.timesheetlinestatus, 
                   R.[resource departments] 
         HAVING CONVERT(VARCHAR(10), Cast(periodstartdate AS DATETIME), 126) >= 
                @StartDate 
                AND CONVERT(VARCHAR(10), Cast(periodenddate AS DATETIME), 126) 
                    <= 
                    @EndDate 
                AND Sum(T.actualworkbillable) <> 0 
                AND T.timesheetlinestatus = 'APPROVED' 
                AND ( @ResourceName = 'ALL' 
                       OR R.resourcename = @ResourceName )), 
     cteresourcetotal 
     AS (SELECT RsrcTot = Sum(actualwork), 
                resourcename 
         FROM   cte 
         GROUP  BY resourcename) 
SELECT cte.*, 
       rsrctot 
FROM   cte 
       INNER JOIN cteresourcetotal RT 
               ON cte.resourcename = RT.resourcename;  

INNER JOIN GROUP BY article:

http://www.sqlusa.com/bestpractices2005/joinwithgroupby/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

This is the Main Query:

 WITH MAIN                                                                                                                                                      -- The Main Query 
     AS (SELECT DISTINCT T.ProjectName,
                         R.ResourceName,
                         R.[Resource Departments] Departments,
                           
                         Cast(( Sum(T.ActualWorkBillable) / 8 ) AS DECIMAL(19, 1)) AS ActualWork,
                         CONVERT(VARCHAR(10), Cast(PeriodStartDate AS DATETIME),6) AS StartDate,
                         CONVERT(VARCHAR(10), Cast(PeriodEndDate AS DATETIME), 6)  AS EndDate,
                         @WorkingDays AS WorkingDays
                         
         FROM   MSP_TimesheetLine_UserView T
        
                INNER JOIN MSP_EpmResource_UserView R
                           ON T.[ResourceUID] = R.[ResourceUID] 
                          
                          
         GROUP  BY T.ProjectName,
                   T.PeriodStartDate,
                   T.PeriodEndDate,
                   R.ResourceName,
                   T.TimesheetLineStatus, 
                   R.[Resource Departments]
                  
         HAVING CONVERT(VARCHAR(10), Cast(PeriodStartDate AS DATETIME), 126) >= @StartDate
                AND CONVERT(VARCHAR(10), Cast(PeriodEndDate AS DATETIME), 126) <= @EndDate
                AND Sum(T.ActualWorkBillable) <> 0
                AND T.TimesheetLineStatus = 'APPROVED'
                AND (@ResourceName = 'ALL'
                       OR R.ResourceName = @ResourceName)),
                                 
     MainGrandTotal                                     -- Calculate GrandTotal
     AS (SELECT GrandTotal = Sum(ActualWork),ResourceName 
         FROM   MAIN
         GROUP  BY ResourceName),
        
     MAINProjectTotal                          -- Calculate Total ProjectWise
     AS (Select ProjectTotal = SUM(ActualWork),ProjectName
   From MAIN
   Group By ProjectName ) 
 
 
        
SELECT MAIN.*,
                            -- Calling All columns from MAIN and from subQueriesmain
       --Distinct main.Departments,main.ProjectName,MAIN.ResourceName,MAIN.WorkingDays,
       GrandTotal,
      
       CAST(((GrandTotal/WorkingDays*100)) AS Decimal(19,0)) as OverallUtilization,
       ProjectTotal,
      
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage
      
FROM   MAIN

       INNER JOIN MainGrandTotal RT              -- Joining MAIN Query with GrandTotal Query on the basis of ResourceName
               ON MAIN.ResourceName = RT.ResourceName  
       INNER JOIN MAINProjectTotal PT             -- Joining Main Query with MainProjectTotal Query on the basis of ProjectName         
               ON MAIN.ProjectName =PT.ProjectName
       
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal