if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GetActionHours]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_GetActionHours]
GO
CREATE Procedure SP_GetActionHours
@UserId int = 0,
@Date varchar(10)
as
begin
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--*************************************** Declare Temp Table ********************************************************
create table #TransSet(UserId int, ADesc Varchar(150), Hrs00 int, Hrs01 int, Hrs02 int, Hrs03 int, Hrs04 int, Hrs05 int,
Hrs06 int, Hrs07 int, Hrs08 int, Hrs09 int, Hrs10 int, Hrs11 int, Hrs12 int, Hrs13 int, Hrs14 int, Hrs15 int,
Hrs16 int, Hrs17 int, Hrs18 int, Hrs19 int, Hrs20 int, Hrs21 int, Hrs22 int, Hrs23 int)
--*************************************** Declare Variables for cursor **********************************************
declare @UsrId int, @ActionDesc Varchar(150), @TotAction int, @Hour int
declare @UsrId2 int, @ActionDesc2 Varchar(150), @TotAction2 int, @Hour2 int
--************************************* Declare Variables for Temp Table ********************************************
declare @00Hours int, @01Hours int, @02Hours int, @03Hours int, @04Hours int, @05Hours int,
@06Hours int, @07Hours int, @08Hours int, @09Hours int, @10Hours int, @11Hours int, @12Hours int, @13Hours int, @14Hours int,
@15Hours int, @16Hours int, @17Hours int, @18Hours int, @19Hours int, @20Hours int, @21Hours int, @22Hours int, @23Hours int
Declare @ActionFlag Varchar(150)
--**********************(************ Set Variables for Temp Table to Zero ******************************************
set @00Hours = 0 set @01Hours = 0 set @02Hours = 0 set @03Hours = 0 set @04Hours = 0 set @05Hours = 0 set @06Hours = 0
set @07Hours = 0 set @08Hours = 0 set @09Hours = 0 set @10Hours = 0 set @11Hours = 0 set @12Hours = 0 set @13Hours = 0
set @14Hours = 0 set @15Hours = 0 set @16Hours = 0 set @17Hours = 0 set @18Hours = 0 set @19Hours = 0 set @20Hours = 0
set @21Hours = 0 set @22Hours = 0 set @23Hours = 0
--Set the cursor 'SC' which will be used to populate temp table
DECLARE SC CURSOR FAST_FORWARD READ_ONLY FOR
select AA.UserID, 'Number of calls' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, @Date, 103)) = 0)
and (AA.UserID= @UserId) and (A.CategoryId in(2,3))
group by AA.UserID, datepart(HH, AA.DateCompleted)
union
select AA.UserID, 'Number of Contacts' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, @Date, 103)) = 0)
and (AA.UserID= @UserId) and (A.CategoryId = 2)and (A.ProductivityID in (1,2))
group by AA.UserID, datepart(HH, AA.DateCompleted)
OPEN SC
FETCH NEXT FROM SC into @UsrId, @ActionDesc, @TotAction, @Hour
set @UsrId2 = @UsrId set @TotAction2 = @TotAction set @Hour2 = @Hour
set @ActionFlag = @ActionDesc
WHILE @@FETCH_STATUS = 0
Begin
If @ActionFlag = @ActionDesc
begin
set @UsrId2 = @UsrId set @TotAction2 = @TotAction set @Hour2 = @Hour
if @Hour2 = 0 set @00Hours = @TotAction2
if @Hour2 = 1 set @01Hours = @TotAction2
if @Hour2 = 2 set @02Hours = @TotAction2
if @Hour2 = 3 set @03Hours = @TotAction2
if @Hour2 = 4 set @04Hours = @TotAction2
if @Hour2 = 5 set @05Hours = @TotAction2
if @Hour2 = 6 set @06Hours = @TotAction2
if @Hour2 = 7 set @07Hours = @TotAction2
if @Hour2 = 8 set @08Hours = @TotAction2
if @Hour2 = 9 set @09Hours = @TotAction2
if @Hour2 = 10 set @10Hours = @TotAction2
if @Hour2 = 11 set @11Hours = @TotAction2
if @Hour2 = 12 set @12Hours = @TotAction2
if @Hour2 = 13 set @13Hours = @TotAction2
if @Hour2 = 14 set @14Hours = @TotAction2
if @Hour2 = 15 set @15Hours = @TotAction2
if @Hour2 = 16 set @16Hours = @TotAction2
if @Hour2 = 17 set @17Hours = @TotAction2
if @Hour2 = 18 set @18Hours = @TotAction2
if @Hour2 = 19 set @19Hours = @TotAction2
if @Hour2 = 20 set @20Hours = @TotAction2
if @Hour2 = 21 set @21Hours = @TotAction2
if @Hour2 = 22 set @22Hours = @TotAction2
if @Hour2 = 23 set @23Hours = @TotAction2
FETCH NEXT FROM SC into @UsrId, @ActionDesc, @TotAction, @Hour
end
else
begin
--**********************(************ Copy values to temp table *****************************************
Insert into #TransSet select @UsrId2,@ActionFlag,@00Hours,@01Hours,@02Hours,@03Hours,@04Hours,@05Hours,@06Hours,
@07Hours,@08Hours,@09Hours,@10Hours,@11Hours,@12Hours,@13Hours,@14Hours,@15Hours,@16Hours,@17Hours,
@18Hours,@19Hours,@20Hours,@21Hours,@22Hours,@23Hours
--********************************** Reset Variables for Temp Table to Zero *****************************************
set @00Hours = 0 set @01Hours = 0 set @02Hours = 0 set @03Hours = 0 set @04Hours = 0 set @05Hours = 0 set @06Hours = 0
set @07Hours = 0 set @08Hours = 0 set @09Hours = 0 set @10Hours = 0 set @11Hours = 0 set @12Hours = 0 set @13Hours = 0
set @14Hours = 0 set @15Hours = 0 set @16Hours = 0 set @17Hours = 0 set @18Hours = 0 set @19Hours = 0 set @20Hours = 0
set @21Hours = 0 set @22Hours = 0 set @23Hours = 0
--Reset Action Flag
set @ActionFlag = @ActionDesc
end
END
CLOSE SC
DEALLOCATE SC
Insert into #TransSet select @UsrId, @ActionDesc,@00Hours,@01Hours,@02Hours,@03Hours,@04Hours,@05Hours,@06Hours,
@07Hours,@08Hours,@09Hours,@10Hours,@11Hours,@12Hours,@13Hours,@14Hours,@15Hours,@16Hours,@17Hours,
@18Hours,@19Hours,@20Hours,@21Hours,@22Hours,@23Hours
select * from #TransSet
END
