Demonstration on how to pivot data using temp tables

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[selectReportAttendance]
 @entitymode VARCHAR(50),
 @periodmode VARCHAR(50),
 @wherestring VARCHAR(500),
 @userid VARCHAR(36)
AS 
 BEGIN
 DECLARE @ContextID VARCHAR(100)
 DECLARE @id VARCHAR(50)
 SET @id = ( SELECT ISNULL(MAX(id), 0) + 1
 FROM ReportsDailyAttendanceWorkingTable
 )
 SET @ContextID = ( SELECT ( @userid + ':' + @entitymode + ':'
 + @periodmode + ':'
 + CONVERT(VARCHAR(50), GETDATE(), 111)
 + ':' + @id )
 )


 EXECUTE ( ' 

INSERT INTO [ReportsDailyAttendanceWorkingTable]
 ([Sunday]
 ,[Saturday]
 ,[Friday]
 ,[Thursday]
 ,[Wednesday]
 ,[Tuesday]
 ,[Monday]
 ,[AgentID]
 ,[AgentName]
 ,[TeamLeaderName]
 ,[TeamName]
 ,[AgentLevelID]
 ,[DivisionID]
 ,[ClientID]
 ,[CampaignID]
 ,[LoggedDate]
 ,[CampaignTeamID]
 ,[Hours]
 ,[ContextID])
 
select [Sunday]
 ,[Saturday]
 ,[Friday]
 ,[Thursday]
 ,[Wednesday]
 ,[Tuesday]
 ,[Monday]
 ,[AgentID]
 ,[AgentName]
 ,[TeamLeaderName]
 ,[TeamName]
 ,[AgentLevelID]
 ,[DivisionID]
 ,[ClientID]
 ,[CampaignID]
 ,convert(varchar(50),loggeddate, 111) AS [LoggedDate]
 ,[CampaignTeamID]
 ,[Hours]
     , ''' + @ContextID + '''

FROM [vw_ReportDailyAttendance]
' + @wherestring
 )

 DECLARE @attendance TABLE
 (
 id BIGINT IDENTITY(1, 1)
 NOT NULL,
 [Sunday] VARCHAR(50),
 [Saturday] VARCHAR(50),
 [Friday] VARCHAR(50),
 [Thursday] VARCHAR(50),
 [Wednesday] VARCHAR(50),
 [Tuesday] VARCHAR(50),
 [Monday] VARCHAR(50),
 [AgentID] UNIQUEIDENTIFIER,
 [AgentName] VARCHAR(50),
 [TeamLeaderName] VARCHAR(50),
 [TeamName] VARCHAR(50),
 [AgentLevelID] BIGINT,
 [DivisionID] BIGINT,
 [ClientID] BIGINT,
 [CampaignID] BIGINT,
 [LoggedDate] VARCHAR(50),
 [CampaignTeamID] BIGINT,
 [Hours] DECIMAL(18, 2)
 )



 DECLARE @uniqueagents TABLE
 (
 id BIGINT IDENTITY(1, 1)
 NOT NULL,
 agentid UNIQUEIDENTIFIER NOT NULL
 )

 INSERT INTO @uniqueagents ( agentid )
 SELECT DISTINCT
 agentid
 FROM [ReportsDailyAttendanceWorkingTable]

 DECLARE @acounter BIGINT
 SET @acounter = 1

 WHILE @acounter <= ( SELECT MAX(id)
 FROM @uniqueagents
 )
 BEGIN
 DECLARE @agentid UNIQUEIDENTIFIER 

 SET @agentid = ( SELECT agentid
 FROM @uniqueagents
 WHERE id = @acounter
 )

 DECLARE @indvidualattendance TABLE
 (
 id BIGINT IDENTITY(1, 1)
 NOT NULL,
 [Sunday] VARCHAR(50),
 [Saturday] VARCHAR(50),
 [Friday] VARCHAR(50),
 [Thursday] VARCHAR(50),
 [Wednesday] VARCHAR(50),
 [Tuesday] VARCHAR(50),
 [Monday] VARCHAR(50),
 [AgentID] UNIQUEIDENTIFIER,
 [AgentName] VARCHAR(50),
 [TeamLeaderName] VARCHAR(50),
 [TeamName] VARCHAR(50),
 [AgentLevelID] BIGINT,
 [DivisionID] BIGINT,
 [ClientID] BIGINT,
 [CampaignID] BIGINT,
 [LoggedDate] VARCHAR(50),
 [CampaignTeamID] BIGINT,
 [Hours] DECIMAL(18, 2),
 [ContextID] VARCHAR(100)
 )


 DECLARE @counter TINYINT

 DECLARE @monday VARCHAR(50)
 DECLARE @tuesday VARCHAR(50)
 DECLARE @wednesday VARCHAR(50)
 DECLARE @thursday VARCHAR(50)
 DECLARE @friday VARCHAR(50)
 DECLARE @saturday VARCHAR(50)
 DECLARE @sunday VARCHAR(50)
 DECLARE @totalhours DECIMAL(18, 2)

 SET @totalhours = 0

 SET @counter = 1

 WHILE @counter < 8

 BEGIN

 DELETE FROM @indvidualattendance

 INSERT INTO @indvidualattendance
 (
 [Sunday],
 [Saturday],
 [Friday],
 [Thursday],
 [Wednesday],
 [Tuesday],
 [Monday],
 [AgentID],
 [AgentName],
 [TeamLeaderName],
 [TeamName],
 [AgentLevelID],
 [DivisionID],
 [ClientID],
 [CampaignID],
 [LoggedDate],
 [CampaignTeamID],
 [Hours],
 [ContextID]
 )
 SELECT [Sunday],
 [Saturday],
 [Friday],
 [Thursday],
 [Wednesday],
 [Tuesday],
 [Monday],
 [AgentID],
 [AgentName],
 [TeamLeaderName],
 [TeamName],
 [AgentLevelID],
 [DivisionID],
 [ClientID],
 [CampaignID],
 [LoggedDate],
 [CampaignTeamID],
 [Hours],
 [ContextID]
 FROM [ReportsDailyAttendanceWorkingTable]
 WHERE agentid = @agentid
 AND ContextID = @contextid 


 IF @counter = 1 
 BEGIN

 SET @monday = ( SELECT Monday
 FROM @indvidualattendance
 WHERE monday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE monday = 'Present'
 ), 0)

 END
 IF @counter = 2 
 BEGIN

 SET @tuesday = ( SELECT tuesday
 FROM @indvidualattendance
 WHERE tuesday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE tuesday = 'Present'
 ), 0)

 END
 IF @counter = 3 
 BEGIN

 SET @wednesday = ( SELECT wednesday
 FROM @indvidualattendance
 WHERE wednesday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE wednesday = 'Present'
 ), 0)

 END
 IF @counter = 4 
 BEGIN

 SET @thursday = ( SELECT thursday
 FROM @indvidualattendance
 WHERE thursday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE thursday = 'Present'
 ), 0)

 END
 IF @counter = 5 
 BEGIN

 SET @friday = ( SELECT friday
 FROM @indvidualattendance
 WHERE friday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE friday = 'Present'
 ), 0)


 END
 IF @counter = 6 
 BEGIN

 SET @saturday = ( SELECT Saturday
 FROM @indvidualattendance
 WHERE Saturday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE Saturday = 'Present'
 ), 0)

 END
 IF @counter = 7 
 BEGIN
-- '?' + loggeddate
 SET @sunday = ( SELECT sunday
 FROM @indvidualattendance
 WHERE sunday IS NOT NULL
 )
 SET @totalhours = @totalhours
 + ISNULL(( SELECT hours
 FROM @indvidualattendance
 WHERE sunday = 'Present'
 ), 0)

 END

 SET @counter = @counter + 1
 END


 INSERT INTO @attendance
 (
 [Monday],
 [Tuesday],
 [Wednesday],
 [Thursday],
 [Friday],
 [Saturday],
 [Sunday],
 [AgentID],
 [AgentName],
 [TeamLeaderName],
 [TeamName],
 [AgentLevelID],
 [DivisionID],
 [ClientID],
 [CampaignID],
 [CampaignTeamID],
 [Hours]
 )
 SELECT DISTINCT
 @monday AS Monday,
 @tuesday AS Tuesday,
 @wednesday AS Wednesday,
 @thursday AS Thursday,
 @friday AS Friday,
 @saturday AS Saturday,
 @sunday AS Sunday,
 [AgentID],
 [AgentName],
 [TeamLeaderName],
 [TeamName],
 [AgentLevelID],
 [DivisionID],
 [ClientID],
 [CampaignID],
 [CampaignTeamID],
 @totalhours
 FROM @indvidualattendance

 SET @acounter = @acounter + 1

 END


 SELECT DISTINCT
 [AgentName],
 [Monday],
 [Tuesday],
 [Wednesday],
 [Thursday],
 [Friday],
 [Saturday],
 [Sunday],
 [Hours]
 FROM @attendance

 END