CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedurespivot-table
user3590485
user3590485

Reputation: 241

Store Sum of rows in another column by Stored Procedure

I am getting list of all the employees with it's working time. The query is here :

ALTER PROCEDURE [dbo].[Get_EmployeeWiseWorkingTime] 
@PayPeriodID as int
AS
BEGIN
DECLARE @StartDate DATETIME='2014-04-21 00:00:00.000'
DECLARE @EndDate DATETIME='2014-05-04 00:00:00.000'
Select @StartDate=[PeriodStartDate],@EndDate=[PeriodEndDate] from PayPeriod --where         PayPeriodId=@PayPeriodID

DECLARE @DateList VARCHAR(MAX), @DateListCoalesced VARCHAR(MAX)
SELECT @DateList = '', @DateListCoalesced = ''
;WITH DateLimits AS (
  SELECT CAST(@StartDate AS DATE) AS dt
  UNION ALL
  SELECT DATEADD(dd, 1, dt)
  FROM DateLimits s
  WHERE DATEADD(dd, 1, dt) <= CAST(@EndDate AS DATE))
  SELECT @DateList =  @DateList + '[' + CAST(dt AS VARCHAR)+ '], ' ,
  @DateListCoalesced = @DateListCoalesced + ' COALESCE( [' + CAST(dt AS VARCHAR)+ '] , 0) as [' + CAST(dt AS VARCHAR)+ '], ' 
 FROM DateLimits

;SET @DateList = LEFT(@DateList, LEN(@DateList) - 1)
;SET @DateListCoalesced = LEFT(@DateListCoalesced, LEN(@DateListCoalesced) - 1)


DECLARE @query NVARCHAR(max)
 SET @query = N'SELECT [EmployeeID],FirstName,LastName, ' +  @DateListCoalesced +'
FROM   
(SELECT DayLog.[EmployeeID],FirstName,LastName, DayLog.[CreatedDate], [WorkingTime] 
 FROM DayLog
INNER JOIN Employees ON Employees.EmployeeID = DayLog.EmployeeID    
) p
PIVOT
(
    Sum ([WorkingTime] )
    FOR [CreatedDate] IN ( '+ @DateList +' )
) AS pvt order by EmployeeID'

EXEC sp_executesql @Query END

the result is:

enter image description here

I want to add a Column name Total which calculates and add total working time for the data listed. Any examples would be appreciated.

Upvotes: 1

Views: 827

Answers (1)

amit_g
amit_g

Reputation: 31250

;SET @DateList = @DateList + 'Total'
;SET @DateListCoalesced = @DateListCoalesced + 'Total'

SET @query = N'SELECT [EmployeeID],FirstName,LastName, ' +  @DateListCoalesced +'
FROM   
(
    SELECT DayLog.[EmployeeID],FirstName,LastName, Convert(varchar(50), DayLog.[CreatedDate], 120) as CreatedDate, [WorkingTime] 
    FROM DayLog
    INNER JOIN Employees ON Employees.EmployeeID = DayLog.EmployeeID    

    UNION

    SELECT DayLog.[EmployeeID],FirstName,LastName, ''Total'', Sum([WorkingTime]) 
    FROM DayLog
    INNER JOIN Employees ON Employees.EmployeeID = DayLog.EmployeeID    
    GROUP BY DayLog.[EmployeeID],FirstName,LastName
) p
PIVOT
(
    Sum ([WorkingTime] )
    FOR [CreatedDate] IN ( '+ @DateList +' )
) AS pvt order by EmployeeID'

Upvotes: 1

Related Questions