How to create new column named "PercentageTotal" group by Projects

Question

USE ProjectServer_Reporting_IT
GO

Declare @StartDate date;
Declare @EndDate date;
--Declare @ResourceName varchar(50);
Declare @WorkingDays varchar(50);
Declare @Departments varchar(50);

Set @WorkingDays = 20
--Set @ResourceName = 'ALL'
Set @StartDate = '2013-01-01' ;
Set @EndDate = '2013-01-30' ;
Set @Departments = 'IT.Governance';

 WITH MAIN                   
     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
                AND R.[Resource Departments] = @Departments
                ),
                                 
     MainGrandTotal                                       
     AS (SELECT GrandTotal = Sum(ActualWork),MAIN.ResourceName 
         FROM   MAIN
         GROUP  BY MAIN.ResourceName),
        
     MAINProjectTotal                          
     AS (Select ProjectTotal = SUM(ActualWork),MAIN.ProjectName,ResourceName
   From MAIN
   Group By ProjectName,ResourceName)

 
        
SELECT --MAIN.*,
                          
       Distinct main.Departments,main.ProjectName,
            
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage
      
   
FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT 
WHERE MAIN.ResourceName = RT.ResourceName  
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName                 
       
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal

 

Answer 1

Chk this

Declare @StartDate date;
Declare @EndDate date;
--Declare @ResourceName varchar(50);
Declare @WorkingDays varchar(50);
Declare @Departments varchar(50);

Set @WorkingDays = 20
--Set @ResourceName = 'ALL'
Set @StartDate = '2013-01-01' ;
Set @EndDate = '2013-01-30' ;
Set @Departments = 'IT.Governance';


 WITH MAIN                   
     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
                AND R.[Resource Departments] = @Departments
                ),
                                  
     MainGrandTotal                                        
     AS (SELECT GrandTotal = Sum(ActualWork),MAIN.ResourceName  
         FROM   MAIN 
         GROUP  BY MAIN.ResourceName),
         
     MAINProjectTotal                           
     AS (Select ProjectTotal = SUM(ActualWork),MAIN.ProjectName,ResourceName 
   From MAIN
   Group By ProjectName,ResourceName)

 
         
SELECT --MAIN.*,
                          
       Distinct main.Departments,main.ProjectName,
             
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage,
       SUM(CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)))OVER(PARTITION BY main.Departments,main.ProjectName)
       
    
FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT 
WHERE MAIN.ResourceName = RT.ResourceName   
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName                  
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal

 

---

Please have look on the comment

Answer 2

new column is showing wrong answer

Answer 3

the query will do something like this Declare @Table Table
(
Departments Varchar(100),
ProjectName Varchar(100),
Percentage Decimal(10,4)
)

Insert @Table
Select 'IT.Governace','Administartive','5.1' Union All
Select 'IT.Governace','Develpoment','2.0' Union All
Select 'IT.Governace','Develpoment','4.8' Union All
Select 'IT.Governace','GenAdmin','5.1' Union All
Select 'IT.Governace','Tm Admin','5.1' Union All
Select 'IT.Governace','Tm Admin','5.1' Union All
Select 'IT.Governace','TM Supoort','5.1' ;

Select *,Sum(Percentage) Over (Partition by Departments,ProjectName) FRom @Table

---

Please have look on the comment

Answer 4

new column is showing wrong answer

This shud wrk atleast

Declare @StartDate date;
Declare @EndDate date;
--Declare @ResourceName varchar(50);
Declare @WorkingDays varchar(50);
Declare @Departments varchar(50);

Set @WorkingDays = 20
--Set @ResourceName = 'ALL'
Set @StartDate = '2013-01-01' ;
Set @EndDate = '2013-01-30' ;
Set @Departments = 'IT.Governance';


 WITH MAIN                   
     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
                AND R.[Resource Departments] = @Departments
                ),
                                  
     MainGrandTotal                                        
     AS (SELECT GrandTotal = Sum(ActualWork),MAIN.ResourceName  
         FROM   MAIN 
         GROUP  BY MAIN.ResourceName),
         
     MAINProjectTotal                           
     AS (Select ProjectTotal = SUM(ActualWork),MAIN.ProjectName,ResourceName 
   From MAIN
   Group By ProjectName,ResourceName
   ),
   RESULT AS(
 
         
SELECT Distinct main.Departments,main.ProjectName,
             
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage
       FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT 
WHERE MAIN.ResourceName = RT.ResourceName   
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName                  
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal
)
SELECT *,SUM(Percentage)OVER (PARTITION BY Departments,ProjectName) AS PercentageTotal
FROM RESULT

 

---

Please have look on the comment

Answer 5

Try below code....

USE ProjectServer_Reporting_IT
GO

Declare @StartDate date;
Declare @EndDate date;
--Declare @ResourceName varchar(50);
Declare @WorkingDays varchar(50);
Declare @Departments varchar(50);

Set @WorkingDays = 20
--Set @ResourceName = 'ALL'
Set @StartDate = '2013-01-01' ;
Set @EndDate = '2013-01-30' ;
Set @Departments = 'IT.Governance';


 WITH MAIN                   
     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
                AND R.[Resource Departments] = @Departments
                ),
                                  
     MainGrandTotal                                        
     AS (SELECT GrandTotal = Sum(ActualWork),MAIN.ResourceName  
         FROM   MAIN 
         GROUP  BY MAIN.ResourceName),
         
     MAINProjectTotal                           
     AS (Select ProjectTotal = SUM(ActualWork),MAIN.ProjectName,ResourceName 
   From MAIN
   Group By ProjectName,ResourceName)
    MainProject_Percent
    as ( 
          Select Percentage_Total=sum(CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1))),a.ResourceName  
         from  MainGrandTotal A join  MAINProjectTotal b on a.ResourceName =b.ResourceName
          group by a.ResourceName
         
        )
 
         
SELECT --MAIN.*,
                          
       Distinct main.Departments,main.ProjectName,
             
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage,
       MP.Percentage_Total 
       
    
FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT ,MainProject_Percent MP
WHERE MAIN.ResourceName = RT.ResourceName   
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName
      AND Main.ResourceName=MP.ResourceName                
        
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal

 

Thanks,

Saurabh

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 6

Hello John ,

  Santhosh's solution should work fine for you.

  Could you make sure about one more thing , by adding the below ORDER BY clause in the final query to make sure how many records available for "IT.Governance" & "Development" combination.

  ORDER BY main.Departments,main.ProjectName

---

Best Regards Sorna

Answer 7

Answer 8

Hi John,

You have missed the code....

SELECT --MAIN.*,
                         
       Distinct main.Departments,main.ProjectName,
            
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage,
       MP.Percentage_Total
      
   
FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT ,MainProject_Percent MP
WHERE MAIN.ResourceName = RT.ResourceName  
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName
      AND Main.ResourceName=MP.ResourceName               
       
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal

Run Complete Code and them try...

Thanks,

Saurabh

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 9

InCorrect Result:

Answer 10

Hey John,

Did u try with code that i have pasted in this thread.

---

Please have look on the comment

Answer 11

Hello John,

  Your final select statment is having a DISTINCT which is filtering out the duplicate rows of with same percentage. Please confirm is that is what you need. If so , try using the below enchanced code of Sanjeevan's solution. Added a distinct inside the SUM.

SELECT --MAIN.*,
                          
       Distinct main.Departments,main.ProjectName,
             
       CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)) as Percentage,
       SUM(DISTINCT CAST(((GrandTotal/ProjectTotal)) AS DECIMAL(19,1)))OVER(PARTITION BY main.Departments,main.ProjectName)
       
    
FROM   MAIN,MainGrandTotal RT,MAINProjectTotal PT 
WHERE MAIN.ResourceName = RT.ResourceName   
      AND MAIN.ProjectName = PT.ProjectName
      AND MAIN.ResourceName = PT.ResourceName                  
GROUP BY MAIN.ProjectName,MAIN.ResourceName,MAIN.Departments,MAIN.ActualWork,MAIN.StartDate,MAIN.EndDate,MAIN.WorkingDays,GrandTotal,ProjectTotal

---

Best Regards Sorna