I know you are brand new around here but what you posted is a mess. I ran it through a free formatter.

-- sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II

--select Oid

--,(Select appidentity from project where oid = projectoid) ProjectId

--,Appidentity PositionID

--,name PositionName

--,sdr_StartDate PositionStartDate

--,sdr_EndDate PositionEndDate

--,isnull(CName,'NA')CName

--,isnull(CCMFlag,0) CCMFlag

--,PA.OrgName OrgName

--,financialRulesOid

--,projectoid

--into #ProjectPosition_JR_CD

--from PROJECTTEAMSLOT PTS --(Position Details)

--LEFT OUTER JOIN rptProject_Position_JR_CD_View PJC

-- ON PTS.OID = PJC.ProjectTeamSlotOid

--,(select W.Oid FinancialOid,O.name OrgName,OrgWunitOid from OrganizationalWUnit O, CommonProjectFinancials W

-- where O.oid = W.OrgWunitOid)PA

--where PA.FinancialOid = financialRulesOid

--order by oid

--

--

--sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS

--PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II '12/13/2012','12/15/2012','%','%','%','%',1,1,1,1,1,1,1,0

--sp_helptext PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS '10/01/2012','10/20/2012','All','all','%','%',1,0,1,1,1,1,1,0

CREATE PROCEDURE [dbo].[PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS_NCCM_Phase_II] (

@pStartDate VARCHAR(10)

,@pEndDate VARCHAR(10)

,@pRequestorsPool VARCHAR(255)

,@pRegion VARCHAR(255)

,@pPC VARCHAR(255)

,@pProjectPool VARCHAR(255)

,@pRequestStatusAccepted INT

,@pRequestStatusCancelled INT

,@pRequestStatusDeclined INT

,@pRequestStatusDenied INT

,@pRequestStatusTimeout INT

,@pRequestStatusWaitingConfirm INT

,@pRequestStatusWaitingReply INT

,@pinternalRegion INT

)

AS

BEGIN

/**

Author: Jayaraj Arulandu

Date: 1/10/2010

Function: [PVC_PROC_SERVICE_DELIVERY_REQUEST_MEASUREMENTS]

Description: This report includes all requests general and specific, whether fulfilled within the MU or sent to the GSDC.

It measures the response times at several points during the process: Overall time to “Suggested/Approved” status,

Overall time to "Accepted" status, Overall MU lead-time and time to first and latest action.

Changes History:

Date ChangedBy Description

1/10/2010 Jayaraj Changed the report for Q409 release extensively.

1/23/2010 Jayaraj Added additional enhancements to this report

Promoted to Production.

01/23/10 Jayaraj Promoted to Production - Q409 Release

02/24/2011 Hemanth Excluding Project and Opportunity Pools

*/

SET NOCOUNT ON

CREATE TABLE #internal (RequestOID VARCHAR(50))

CREATE TABLE #regiontable (

requestoid NUMERIC(19, 0)

,ReceivedDatetime DATETIME

,Region VARCHAR(500)

,recipientName VARCHAR(500)

)

CREATE TABLE #pctable (

requestoid NUMERIC(19, 0)

,ReceivedDatetime DATETIME

,personname VARCHAR(500)

,recipientsPoolName VARCHAR(800)

,inbox VARCHAR(200)

)

/****** Object: Table [dbo].[rrm_temp] Script Date: 10/03/2012 16:18:08 ******/

IF EXISTS (

SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[rrm_temp]')

AND type IN (N'U')

)

DROP TABLE [dbo].[rrm_temp]

--Create table RRM_Parameter

--

--(pStartDate VARCHAR(10),

-- pEndDate VARCHAR(10),

-- pRequestorsPool VARCHAR(255),

-- pRegion VARCHAR(255),

-- pPC VARCHAR(255),

-- pProjectPool VARCHAR(255),

-- pRequestStatusAccepted int,

-- pRequestStatusCancelled int,

-- pRequestStatusDeclined int,

-- pRequestStatusDenied int,

-- pRequestStatusTimeout int,

-- pRequestStatusWaitingConfirm int,

-- pRequestStatusWaitingReply int,

-- pinternalRegion int

--)

--

--insert into RRM_Parameter

--values( @pStartDate

-- ,@pEndDate

-- ,@pRequestorsPool

-- ,@pRegion

-- ,@pPC

-- ,@pProjectPool

-- ,@pRequestStatusAccepted

-- ,@pRequestStatusCancelled

-- ,@pRequestStatusDeclined

-- ,@pRequestStatusDenied

-- ,@pRequestStatusTimeout

-- ,@pRequestStatusWaitingConfirm

-- ,@pRequestStatusWaitingReply

-- ,@pinternalRegion

--)

CREATE TABLE #results (

--ProjectId varchar(50)

--,PositionID varchar(50)

--,PositionName varchar(255)

--,PositionStartDate datetime

--,CName varchar(255)

--,CCMFlag int

--,OrgName varchar(255),

RequestOID NUMERIC(19, 0)

,ProjectPool VARCHAR(500)

,Region VARCHAR(500)

,PC VARCHAR(500)

,RequestID VARCHAR(50)

,Creator VARCHAR(500)

,CreatorsPool VARCHAR(500)

,CreateDate DATETIME

,Client VARCHAR(500)

,Project VARCHAR(500)

,Position VARCHAR(500)

,PositionOID NUMERIC(19, 0)

,FunctionalRole VARCHAR(500)

,RequestStatus VARCHAR(100)

,PositionCreated DATETIME

,Initiation DATETIME

,Completed DATETIME

,ResourceSuggested DATETIME

,StaffingAssignmentStatus VARCHAR(100)

,StaffingAssignmentResource VARCHAR(500)

,CommitmentStartDate DATETIME

,DaysToSuggested INT

,SuggestedInTime INT

,DaysToAccepted INT

,DaysToCommitment INT

,DaysToSuggestedIndicator INT

,DaysToAcceptedIndicator INT

,RequestCreatedInTime INT

,RecipientName VARCHAR(500)

,Recipient_First_Action VARCHAR(500)

,Recipient_Last_Action VARCHAR(500)

,DaysSince_First_Action INT

,DaysSince_Last_Action INT

,Request_First_Action VARCHAR(500)

,DaysSince_Request_First_Action INT

,OwnerPoolsName VARCHAR(500)

,RecipientDaysToSuggested INT

,RecipientDaysToAccepted INT

,RecipientStatus VARCHAR(500)

,ResipientCreateDate DATETIME

,RecipientRespondedDate DATETIME

,RequestTimedoutDate DATETIME

,ReceivedDatetime DATETIME

)

CREATE TABLE #temp_results (

--ProjectId varchar(50)

--,PositionID varchar(50)

--,PositionName varchar(255)

--,PositionStartDate datetime

--,CName varchar(255)

--,CCMFlag int

--,OrgName varchar(255),

RequestOID NUMERIC(19, 0)

,ProjectPool VARCHAR(500)

,Region VARCHAR(500)

,PC VARCHAR(500)

,RequestID VARCHAR(50)

,Creator VARCHAR(500)

,CreatorsPool VARCHAR(500)

,CreateDate DATETIME

,Client VARCHAR(500)

,Project VARCHAR(500)

,Position VARCHAR(500)

,PositionOID NUMERIC(19, 0)

,FunctionalRole VARCHAR(500)

,RequestStatus VARCHAR(100)

,PositionCreated DATETIME

,Initiation DATETIME

,Completed DATETIME

,ResourceSuggested DATETIME

,StaffingAssignmentStatus VARCHAR(100)

,StaffingAssignmentResource VARCHAR(500)

,CommitmentStartDate DATETIME

,DaysToSuggested INT

,SuggestedInTime INT

,DaysToAccepted INT

,DaysToCommitment INT

,DaysToSuggestedIndicator INT

,DaysToAcceptedIndicator INT

,RequestCreatedInTime INT

,RecipientName VARCHAR(500)

,Recipient_First_Action VARCHAR(500)

,Recipient_Last_Action VARCHAR(500)

,DaysSince_First_Action INT

,DaysSince_Last_Action INT

,Request_First_Action VARCHAR(500)

,DaysSince_Request_First_Action INT

,OwnerPoolsName VARCHAR(500)

,RecipientDaysToSuggested INT

,RecipientDaysToAccepted INT

,RecipientStatus VARCHAR(500)

,ResipientCreateDate DATETIME

,RecipientRespondedDate DATETIME

,RequestTimedoutDate DATETIME

,ReceivedDatetime DATETIME

)

CREATE TABLE #leadtime (

RequestOID NUMERIC(19, 0)

,ProjectPool VARCHAR(500)

,Region VARCHAR(500)

,PC VARCHAR(500)

,RequestID VARCHAR(50)

,Creator VARCHAR(500)

,CreatorsPool VARCHAR(500)

,CreateDate DATETIME

,Client VARCHAR(500)

,Project VARCHAR(500)

,Position VARCHAR(500)

,PositionOID NUMERIC(19, 0)

,FunctionalRole VARCHAR(500)

,RequestStatus VARCHAR(100)

,PositionCreated DATETIME

,Initiation DATETIME

,Completed DATETIME

,ResourceSuggested DATETIME

,StaffingAssignmentStatus VARCHAR(100)

,StaffingAssignmentResource VARCHAR(500)

,CommitmentStartDate DATETIME

,DaysToSuggested INT

,SuggestedInTime INT

,DaysToAccepted INT

,DaysToCommitment INT

,DaysToSuggestedIndicator INT

,DaysToAcceptedIndicator INT

,RequestCreatedInTime INT

,RecipientName VARCHAR(500)

,Recipient_First_Action VARCHAR(500)

,Recipient_Last_Action VARCHAR(500)

,DaysSince_First_Action INT

,DaysSince_Last_Action INT

,Request_First_Action VARCHAR(500)

,DaysSince_Request_First_Action INT

,OwnerPoolsName VARCHAR(500)

,RecipientDaysToSuggested INT

,RecipientDaysToAccepted INT

,RecipientStatus VARCHAR(500)

,ResipientCreateDate DATETIME

,RecipientRespondedDate DATETIME

,RequestTimedoutDate DATETIME

,ReceivedDatetime DATETIME

)

DECLARE @vRequestStatusAccepted VARCHAR(20)

,@vRequestStatusCancelled VARCHAR(20)

,@vRequestStatusDeclined VARCHAR(20)

,@vRequestStatusDenied VARCHAR(20)

,@vRequestStatusTimeout VARCHAR(20)

,@vRequestStatusWaitingConfirm VARCHAR(20)

,@vRequestStatusWaitingReply VARCHAR(20)

,@vRequestCreateTime INT

,@vGSRfunctionvalue VARCHAR(20)

SET @vRequestCreateTime = 28

IF @pRequestStatusAccepted = 1

SET @vRequestStatusAccepted = 'COMPLETED_ACCEPTED'

ELSE

SET @vRequestStatusAccepted = ''

IF @pRequestStatusCancelled = 1

SET @vRequestStatusCancelled = 'COMPLETED_CANCELLED'

ELSE

SET @vRequestStatusCancelled = ''

IF @pRequestStatusDeclined = 1

SET @vRequestStatusDeclined = 'COMPLETED_DECLINED'

ELSE

SET @vRequestStatusDeclined = ''

IF @pRequestStatusDenied = 1

SET @vRequestStatusDenied = 'COMPLETED_DENIED'

ELSE

SET @vRequestStatusDenied = ''

IF @pRequestStatusTimeout = 1

SET @vRequestStatusTimeout = 'COMPLETED_TIMEDOUT'

ELSE

SET @vRequestStatusTimeout = ''

IF @pRequestStatusWaitingConfirm = 1

SET @vRequestStatusWaitingConfirm = 'WAITING_FOR_CONFIRM'

ELSE

SET @vRequestStatusWaitingConfirm = ''

IF @pRequestStatusWaitingReply = 1

SET @vRequestStatusWaitingReply = 'WAITING_FOR_REPLY'

ELSE

SET @vRequestStatusWaitingReply = ''

SET @pRequestorsPool = ltrim(rtrim(@pRequestorsPool))

IF @pRequestorsPool LIKE ''

SET @pRequestorsPool = '%'

SET @pProjectPool = ltrim(rtrim(@pProjectPool))

IF @pProjectPool LIKE ''

SET @pProjectPool = '%'

SET @pPC = ltrim(rtrim(@pPC))

IF @pPC LIKE ''

SET @pPC = '%'

SET @pRegion = ltrim(rtrim(@pRegion))

CREATE CLUSTERED INDEX IX_temp_results ON #temp_results (RequestOID)

CREATE NONCLUSTERED INDEX IX_temp_results2 ON #temp_results (

RequestOID

,recipientName

) --,Region)

CREATE NONCLUSTERED INDEX IX_leadtime ON #leadtime (

RequestOID

,ProjectPool

)

CREATE NONCLUSTERED INDEX IX_results ON #results (

requestid

,projectpool

)

CREATE NONCLUSTERED INDEX IX_region ON #regiontable (

requestoid

,receiveddatetime

)

CREATE NONCLUSTERED INDEX IX_region ON #pctable (

requestoid

,recipientsPoolName

,ReceivedDatetime

)

SELECT *

INTO #StaffingRequest

FROM STAFFINGREQUEST sr WITH (NOLOCK)

WHERE sr.requestedDatetime >= @pStartDate

AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate) -- add one day so that the day is included (12 am is default)

AND sr.overallstatus IN (

@vRequestStatusAccepted

,@vRequestStatusCancelled

,@vRequestStatusDeclined

,@vRequestStatusDenied

,@vRequestStatusTimeout

,@vRequestStatusWaitingConfirm

,@vRequestStatusWaitingReply

)

SELECT *

INTO #ProjectTeamSlot

FROM ProjectTeamSlot WITH (NOLOCK)

WHERE oid IN (

SELECT positionOid

FROM StaffingAssignment

WHERE Oid IN (

SELECT requestedAssignmentOid

FROM #StaffingRequest

)

)

----***** Hanamesh

SELECT Oid

,(

SELECT appidentity

FROM project

WHERE oid = projectoid

) ProjectId

,Appidentity PositionID

,NAME PositionName

,sdr_StartDate PositionStartDate

,sdr_EndDate PositionEndDate

--,' ' as CDName

--,' ' as JRName

--,isnull(CName,'NA')CName

--,isnull(CCMFlag,0) CCMFlag

,PA.OrgName OrgName

,financialRulesOid

,projectoid

INTO #ProjectPosition_JR_CD

FROM #ProjectTeamSlot PTS --(Position Details)

--LEFT OUTER JOIN rptProject_Position_JR_CD_View PJC

-- ON PTS.OID = PJC.ProjectTeamSlotOid

,(

SELECT W.Oid FinancialOid

,O.NAME OrgName

,OrgWunitOid

FROM OrganizationalWUnit O

,CommonProjectFinancials W

WHERE O.oid = W.OrgWunitOid

) PA

WHERE PA.FinancialOid = financialRulesOid

---- *** Hanamesh

ALTER TABLE #ProjectPosition_JR_CD ADD CDName VARCHAR(max)

ALTER TABLE #ProjectPosition_JR_CD ADD JRName VARCHAR(max)

CREATE TABLE #TempCD (

OID VARCHAR(19)

,CDName VARCHAR(max)

)

---- ***** CD Hanamesh ***** -----

TRUNCATE TABLE #TEMPCD

DECLARE @Cnt AS BIGINT

DECLARE @PTS_OID VARCHAR(19)

DECLARE @Name VARCHAR(max)

SET @Cnt = 9999

WHILE @cnt <> 0

BEGIN

--insert into #Temp

SELECT TOP 1 @PTS_OID = PT_S.OID

FROM assignedskill ASK

,CCMAllChild CD

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = CD.Oid

AND ccmflag = 4

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid NOT IN (

SELECT OID

FROM #TempCD

)

INSERT INTO #TempCD (OID)

SELECT @PTS_OID

SELECT TOP 1 @Cnt = count(PT_S.OID)

FROM assignedskill ASK

,CCMAllChild CD

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = CD.Oid

AND ccmflag = 4

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid NOT IN (

SELECT OID

FROM #TempCD

)

SELECT /* distinct */

@Name = replace(replace((

SELECT DISTINCT CD.NAME

FROM assignedskill ASK

,CCMAllChild CD

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = CD.Oid

AND ccmflag = 4 --CD

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid = @PTS_OID

FOR XML PATH('')

), '</Name>', CHAR(13)), '<Name>', '')

--print @vorgname

--select

--Print @Name

--select @Name OrgName into #result

UPDATE #TempCD

SET CDName = replace(@Name, '&', '&')

WHERE OID = @PTS_OID

--print @Cnt

IF @Cnt = 0

BREAK;

END

UPDATE #ProjectPosition_JR_CD

SET #ProjectPosition_JR_CD.CDName = TCD.CDName

FROM #TempCD TCD

WHERE TCD.OID = #ProjectPosition_JR_CD.OID

CREATE TABLE #TempJR (

OID VARCHAR(19)

,JRName NVARCHAR(max)

)

---- ***** CD Hanamesh ***** -----

TRUNCATE TABLE #TEMPJR

--Declare @Cnt as Bigint

--Declare @PTS_OID varchar(19)

--Declare @Name varchar(max)

SET @Cnt = 9999

WHILE @cnt <> 0

BEGIN

--insert into #Temp

SELECT TOP 1 @PTS_OID = PT_S.OID

FROM assignedskill ASK

,CCMAllChild JR

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = JR.Oid

AND ccmflag = 5

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid NOT IN (

SELECT OID

FROM #TempJR

)

INSERT INTO #TempJR (OID)

SELECT @PTS_OID

SELECT TOP 1 @Cnt = count(PT_S.OID)

FROM assignedskill ASK

,CCMAllChild JR

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = JR.Oid

AND ccmflag = 5

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid NOT IN (

SELECT OID

FROM #TempJR

)

SELECT /* distinct */

@Name = replace(replace((

SELECT DISTINCT JR.NAME

FROM assignedskill ASK

,CCMAllChild JR

,#ProjectTeamSlot PT_S

WHERE ASK.Skilloid = JR.Oid

AND ccmflag = 5

AND PT_S.Oid = ASK.EntityOwnerOid

AND PT_S.Oid = @PTS_OID

FOR XML PATH('')

), '</Name>', CHAR(13)), '<Name>', '')

--print @vorgname

--select

--Print @Name

--select @Name OrgName into #result

UPDATE #TempJR

SET JRName = replace(@Name, '&', '&')

WHERE OID = @PTS_OID

--print @Cnt

IF @Cnt = 0

BREAK;

END

UPDATE #ProjectPosition_JR_CD

SET #ProjectPosition_JR_CD.JRName = TJR.JRName

FROM #TempJR TJR

WHERE TJR.OID = #ProjectPosition_JR_CD.OID

INSERT INTO #results

SELECT

--ProjectId

--,PositionID

--,PositionName

--,PositionStartDate

--,CName

--,CCMFlag

--,OrgName,

sr.oid AS RequestOID

,MU.NAME AS ProjectPool

,NULL AS Region

,NULL AS PC

,sr.appidentity AS RequestID

,Requestor.personName AS Creator

,RequestorsPool.NAME AS CreatorsPool

,sr.creationDateTime AS CreateDate

,Client.NAME AS Client

,Project.NAME AS Project

,position.NAME AS Position

,position.oid AS PositionOID

,NULL AS FunctionalRole

,sr.overallstatus AS RequestStatus

,position.creationDateTime AS PositionCreated

,sr.requestedDatetime AS Initiation

,sr.completedDatetime AS Completed

,NULL AS ResourceSuggested

,CASE

WHEN (

employee.NAME IS NOT NULL

AND pc.ischangerequested = 1

)

THEN 'Change Requested'

WHEN employee.NAME IS NOT NULL

THEN 'Specific'

ELSE 'General'

END AS StaffingAssignmentStatus

,employee.NAME AS StaffingAssignmentResource

,sa.dateRange_StartDate AS CommitmentStartDate

,NULL AS DaysToSuggested

,NULL AS SuggestedInTime

,NULL AS DaysToAccepted

,NULL AS DaysToCommitment

,NULL AS DaysToSuggestedIndicator

,NULL AS DaysToAcceptedIndicator

,NULL AS RequestCreatedInTime

,Recipient.personName AS RecipientName

,ISNULL((

SELECT first_action

FROM sr_first_action(sr.oid, Recipient.oid)

), 'No Action') Recipient_First_Action

,ISNULL((

SELECT last_action

FROM sr_last_action(sr.oid, Recipient.oid)

), 'No Action') Recipient_Last_Action

,ISNULL((

SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)

FROM sr_first_action(sr.oid, Recipient.oid)

), 0) DaysSince_First_Action

,ISNULL((

SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)

FROM sr_last_action(sr.oid, Recipient.oid)

), 0) DaysSince_Last_Action

,(

SELECT first_action

FROM sr_first_action_Request(sr.oid)

) Request_First_Action

,(

SELECT DATEDIFF(day, sr.requestedDatetime, historydatetime)

FROM sr_first_action_Request(sr.oid)

) DaysSince_Request_First_Action

,pool.NAME AS OwnerPoolsName

,NULL AS RecipientDaysToSuggested

,NULL AS RecipientDaysToAccepted

,CASE srt.STATUS

WHEN 'ACCEPTED'

THEN 'Accepted'

WHEN 'APPROVED'

THEN 'Approved'

WHEN 'CANCELLED'

THEN 'Cancelled'

WHEN 'CONFIRMATION_TIMEDOUT'

THEN 'Confirmation_Timedout'

WHEN 'DECLINED'

THEN 'Declined'

WHEN 'DENIED'

THEN 'Denied'

WHEN 'REQUEST_TIMEDOUT'

THEN 'Request_Timedout'

WHEN 'REQUESTED'

THEN 'Requested'

WHEN 'SUGGESTED'

THEN 'Suggested'

END RecipientStatus

,srt.creationDateTime AS ResipientCreateDate

,srt.respondedDateTime AS RecipientRespondedDate

,sr.timedoutDateTime AS RequestTimedoutDate

,srt.ReceivedDatetime AS ReceivedDatetime

FROM #STAFFINGREQUEST sr WITH (NOLOCK)

INNER JOIN STAFFINGASSIGNMENT sa WITH (NOLOCK) ON sa.oid = sr.requestedAssignmentOID

AND sa.STATUS IN (

0

,2

,4

,6

,8

,10

) -- resource requests

INNER JOIN #ProjectTeamSlot position WITH (NOLOCK) ON sa.positionOID = position.oid

INNER JOIN PROJECT Project WITH (NOLOCK) ON Project.OID = position.ProjectOID

LEFT JOIN PROJECTCOMMITMENT pc WITH (NOLOCK) ON sa.commitmentoid = pc.oid

LEFT JOIN CLIENT Client WITH (NOLOCK) ON Project.clientOID = Client.OID

INNER JOIN POOL ProjectPool WITH (NOLOCK) ON Project.parentPoolOID = ProjectPool.OID

INNER JOIN POOL MU WITH (NOLOCK) ON ProjectPool.poolid LIKE MU.Poolid + '%'

AND MU.poollevel = 1

INNER JOIN evuser Requestor WITH (NOLOCK) ON sr.requesterOID = Requestor.oid

INNER JOIN Pool RequestorsPool WITH (NOLOCK) ON Requestor.parentPoolOID = RequestorsPool.oid

LEFT JOIN employee WITH (NOLOCK) ON sa.resourceoid = employee.oid

LEFT JOIN STAFFINGREQUESTTHREAD srt WITH (NOLOCK) ON sr.OID = srt.parentOID

LEFT JOIN StaffingRequestThreadHL srthl WITH (NOLOCK) ON srthl.StaffingRequestThreadOID = srt.oid

LEFT JOIN evuser Recipient WITH (NOLOCK) ON srt.recipientOID = Recipient.OID

LEFT JOIN OwnerEntryDLView OwnerPool WITH (NOLOCK) ON Recipient.OID = OwnerPool.entityOID

LEFT JOIN Pool pool WITH (NOLOCK) ON OwnerPool.poolID = pool.poolID

-- JOIN #ProjectPosition_JR_CD PJRC on

-- Project.oid = PJRC.projectoid

--WHERE sr.requestedDatetime >= @pStartDate

--AND sr.requestedDatetime <= dateadd(day,1,@pEndDate) -- add one day so that the day is included (12 am is default)

--AND sr.overallstatus IN (@vRequestStatusAccepted,@vRequestStatusCancelled,@vRequestStatusDeclined,@vRequestStatusDenied,@vRequestStatusTimeout,@vRequestStatusWaitingConfirm,@vRequestStatusWaitingReply)

WHERE RequestorsPool.NAME LIKE @pRequestorsPool

AND MU.NAME LIKE @pProjectPool

/*Removing Project/Opportunities Pool from the poolOwners list*/

AND pool.pooltype NOT IN (

1

,6

)

--and pool.pooltype = 2 or

--(pool.pooltype = 0 and (pool.name not like '%_Projects_%' and pool.name not like '%_Projects') )

--and (pool.pooltype = 0 and (pool.name not like '%_Opportunities_%' and pool.name not like '%_Opportunities'))

--AND MU.name not in('R&D','Request_PC Flows','Request Flows')

IF EXISTS (

SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[RRMFix1]')

AND type IN (N'U')

)

DROP TABLE [dbo].[RRMFix1]

SELECT *

INTO RRMFix1

FROM #results

/* Remove Duplicate records from #results table and loading into temp_Results*/

/* Delete Project/Opportunity Domain Pools*/

DELETE

FROM #results

WHERE OwnerPoolsName NOT LIKE '%_Project%Resources%'

AND (

OwnerPoolsName LIKE '%_Projects_%'

OR OwnerPoolsName LIKE '%_Projects'

)

OR (

OwnerPoolsName LIKE '%_Opportunities_%'

OR OwnerPoolsName LIKE '%_Opportunities'

)

INSERT INTO #temp_results

SELECT DISTINCT

--ProjectId

--,PositionID

--,PositionName

--,PositionStartDate

--,CName

--,CCMFlag

--,OrgName,

RequestOID

,ProjectPool

,Region

,PC

,RequestID

,Creator

,CreatorsPool

,CreateDate

,Client

,Project

,Position

,PositionOID

,FunctionalRole

,RequestStatus

,PositionCreated

,Initiation

,Completed

,ResourceSuggested

,StaffingAssignmentStatus

,StaffingAssignmentResource

,CommitmentStartDate

,DaysToSuggested

,SuggestedInTime

,DaysToAccepted

,DaysToCommitment

,DaysToSuggestedIndicator

,DaysToAcceptedIndicator

,RequestCreatedInTime

,RecipientName

,Recipient_First_Action

,Recipient_Last_Action

,DaysSince_First_Action

,DaysSince_Last_Action

,Request_First_Action

,DaysSince_Request_First_Action

,OwnerPoolsName

,RecipientDaysToSuggested

,RecipientDaysToAccepted

,RecipientStatus

,ResipientCreateDate

,RecipientRespondedDate

,RequestTimedoutDate

,ReceivedDatetime

FROM #results

--select * into RRMFix1 from #temp_results

INSERT INTO #regiontable (

requestoid

,ReceivedDatetime

,Region

,recipientName

)

SELECT srt.parentoid AS RequestOID

,srt.receivedDatetime AS ReceivedDatetime

,CASE

WHEN (

SELECT NAME

FROM pool

WHERE oid = recipient.parentpooloid

) = 'Request Flows'

THEN (

SELECT rtrim(ltrim(substring(PERSONNAME, charindex(',', PERSONNAME) + 1, len(PERSONNAME))))

FROM evuser

WHERE oid = srt.recipientoid

)

ELSE (

SELECT NAME

FROM pool

WHERE oid = recipient.parentpooloid

)

END AS Region

,recipient.personName AS recipientName

FROM STAFFINGREQUESTTHREAD srt WITH (NOLOCK)

INNER JOIN evuser recipient WITH (NOLOCK) ON recipient.oid = srt.recipientOID

INNER JOIN #STAFFINGREQUEST sr WITH (NOLOCK) ON sr.OID = srt.parentOID

WHERE sr.requestedDatetime >= @pStartDate

AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate)

UPDATE #temp_results

SET Region = d.Region

FROM #temp_results r

INNER JOIN #regiontable d ON r.RequestOID = d.RequestOID

AND r.recipientName = d.RecipientName

/*Added by Gopi Krishna Namana for displaying results when the requests sent from Region to GSRR Function Inbox on 20111115'*/

UPDATE #temp_results

SET Region = 'GSRR Function'

WHERE Region IN (

'Americas'

,'APAC'

,'EMEA'

)

/*update #temp_results

set Region = d.Region

from

#temp_results r

join [PVC_DateRequestReceivedByRegion] d

on r.RequestOID = d.RequestOID and r.recipientName=d.RecipientName */

IF (@pinternalRegion = 1)

BEGIN

INSERT INTO #internal

SELECT DISTINCT t.requestoid

FROM #temp_results t

INNER JOIN PVC_DateRequestReceivedByRegion a ON t.requestoid = a.requestoid

AND a.region <> t.region

END

IF (@pinternalRegion = 0)

BEGIN

DELETE

FROM #temp_results

WHERE Region NOT LIKE '%' + @pRegion + '%'

END

INSERT INTO #pctable (

requestoid

,ReceivedDatetime

,personname

,recipientsPoolName

,inbox

)

SELECT srt.parentoid AS RequestOID

,srt.receivedDatetime AS ReceivedDatetime

,(

SELECT personname

FROM evuser

WHERE oid = poolownerentryRecipient.useroid

) personname

,recipientsPool.NAME recipientsPoolName

,CASE

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Radio Access Networks'

THEN 'RAN'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Media and Applications'

THEN 'MA'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Consulting'

THEN 'CON'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Customer Support'

THEN 'CS'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Education Center'

THEN 'ED'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Engagement Practices'

THEN 'EP'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'IP&BB and Core'

THEN 'IPBB'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Local Delivery'

THEN 'LD'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'TP&I'

THEN 'TPI'

WHEN (recipient.personname LIKE 'Global%')

THEN rtrim(ltrim(substring(recipient.personname, charindex(',', recipient.personname) + 1, len(recipient.personname))))

ELSE substring(recipient.personname, 0, charindex(',', recipient.personname))

END Inbox

FROM STAFFINGREQUESTTHREAD(NOLOCK) srt

INNER JOIN evuser(NOLOCK) recipient ON recipient.oid = srt.recipientOID

INNER JOIN poolownerentry(NOLOCK) poolownerentryRecipient ON recipient.oid = poolownerentryRecipient.userOID

INNER JOIN Pool(NOLOCK) recipientsPool ON poolownerentryRecipient.poolOID = recipientsPool.oid

INNER JOIN STAFFINGREQUEST sr WITH (NOLOCK) ON sr.OID = srt.parentOID

INNER JOIN PVC_PrimaryCenter_GSR(NOLOCK) GSR ON GSR.PrimaryCenterName = CASE

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Radio Access Networks'

THEN 'RAN'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Media and Applications'

THEN 'MA'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Consulting'

THEN 'CON'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Customer Support'

THEN 'CS'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Education Center'

THEN 'ED'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Engagement Practices'

THEN 'EP'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'IP&BB and Core'

THEN 'IPBB'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'Local Delivery'

THEN 'LD'

WHEN substring(recipient.personname, 0, charindex(',', recipient.personname)) = 'TP&I'

THEN 'TPI'

WHEN (recipient.personname LIKE 'Global%')

THEN rtrim(ltrim(substring(recipient.personname, charindex(',', recipient.personname) + 1, len(recipient.personname))))

ELSE substring(recipient.personname, 0, charindex(',', recipient.personname))

END

WHERE sr.requestedDatetime >= @pStartDate

AND sr.requestedDatetime <= dateadd(day, 1, @pEndDate)

UPDATE #temp_results

SET PC = d.Inbox

FROM #temp_results r

INNER JOIN #pctable d ON r.RequestOID = d.RequestOID

AND r.Ownerpoolsname = d.RecipientspoolName

AND r.ReceivedDatetime = d.ReceivedDatetime

/*update #temp_results

set PC = d.Inbox

from

#temp_results r

join [PVC_DateRequestReceivedByGSR] d

on r.RequestOID = d.RequestOID

and r.Ownerpoolsname=d.RecipientspoolName

and r.ReceivedDatetime = d.ReceivedDatetime */

/*

END V 1.1

--*/

UPDATE #temp_results

SET PC = isnull(PC, 'Internal LM')

,Region = isnull(Region, 'Missing Recipient')

/*Commented on 20111115

update #temp_results

set Region = 'GSR Function' where Region in ('Americas','APAC','EMEA')*/

DELETE

FROM #temp_results

WHERE PC NOT LIKE @pPC + '%'

UPDATE #temp_results

SET ResourceSuggested = x.historyDatetime

FROM #temp_results r

INNER JOIN (

SELECT srtsuggested.parentOID

,min(suggested.historyDatetime) AS historyDatetime

FROM STAFFINGREQUESTTHREAD srtsuggested

INNER JOIN STAFFINGREQUESTTHREADHL suggested ON suggested.staffingRequestThreadOID = srtsuggested.oid

--where actionType = 'SUGGESTED'

--Jayaraj Arulandu 11/1/09 Fix for ST0000007262668

WHERE actionType IN ('SUGGESTED')

GROUP BY srtsuggested.parentOID

) x ON x.parentOID = r.RequestOID

WHERE StaffingAssignmentStatus <> 'Specific'

-- added by Jayaraj for specific requests

UPDATE #temp_results

SET ResourceSuggested = x.historyDatetime

FROM #temp_results r

INNER JOIN (

SELECT srtsuggested.parentOID

,min(suggested.historyDatetime) AS historyDatetime

FROM STAFFINGREQUESTTHREAD srtsuggested

INNER JOIN STAFFINGREQUESTTHREADHL suggested ON suggested.staffingRequestThreadOID = srtsuggested.oid

--where actionType = 'SUGGESTED'

--Jayaraj Arulandu 11/1/09 Fix for ST0000007262668

WHERE actionType IN (

'SUGGESTED'

,'APPROVED'

)

GROUP BY srtsuggested.parentOID

) x ON x.parentOID = r.RequestOID

WHERE StaffingAssignmentStatus = 'Specific'

UPDATE #temp_results

SET FunctionalRole = skill.NAME

FROM #temp_results r

INNER JOIN PROJECTTEAMSLOT position ON r.PositionOID = position.oid

INNER JOIN ASSIGNEDSKILL ON ASSIGNEDSKILL.entityOwnerOID = position.oid

INNER JOIN SKILL ON ASSIGNEDSKILL.skillOID = SKILL.oid

AND skill.NAME LIKE 'FR-%'

UPDATE #temp_results

SET RequestStatus = CASE RequestStatus

WHEN 'COMPLETED_DENIED'

THEN 'Completed, Denied'

WHEN 'COMPLETED_CANCELLED'

THEN 'Completed, Cancelled'

WHEN 'COMPLETED_DECLINED'

THEN 'Completed, Declined'

WHEN 'COMPLETED_TIMEDOUT'

THEN 'Completed, TimedOut'

WHEN 'WAITING_FOR_CONFIRM'

THEN 'Waiting for Confirm'

WHEN 'COMPLETED_ACCEPTED'

THEN 'Completed, Accepted'

WHEN 'WAITING_FOR_REPLY'

THEN 'Waiting for Reply'

END

-- there is a known bug that sometimes the completedDatetime is not set for timed out requests

-- so this corrects for that bug for this report

UPDATE #temp_results

SET Completed = a.timeoutdate

FROM #temp_results r

INNER JOIN (

SELECT sr.oid

,min(hl.historydatetime) AS timeoutdate

FROM staffingrequest sr

INNER JOIN staffingrequesthl hl ON hl.staffingrequestoid = sr.oid

AND hl.actiontype LIKE 'COMPLETED_TIMEDOUT'

WHERE sr.overallstatus LIKE 'COMPLETED_TIMEDOUT'

AND sr.completeddatetime IS NULL

GROUP BY sr.oid

) a ON a.oid = r.RequestOID

WHERE Completed IS NULL

AND RequestStatus = 'Completed, TimedOut'

UPDATE #temp_results

SET DaysToCommitment = DATEDIFF(day, Initiation, CommitmentStartDate)

--"Clock should stop when either of this occurs: --1) When first recipient suggests a resource --2) When first recipient approves a resource --3) When the request is cancelled --4) When the request times out"

--5) When the request is accepted

--6) When the sender suggests a resource

--7) When the sender approves a resource

UPDATE #temp_results

SET DaysToSuggested = CASE

WHEN RequestStatus IN ('Completed, TimedOut')

THEN DATEDIFF(day, Initiation, RequestTimedoutDate)

WHEN RequestStatus IN (

'Completed, Accepted'

,'Completed, Cancelled'

)

THEN DATEDIFF(day, Initiation, Completed)

ELSE DATEDIFF(day, Initiation, getdate())

END

-- When First Recipient Suggests a Resource or Approves a Resource

UPDATE #temp_results

SET DaysToSuggested = tmp.dts

,DaysToSuggestedIndicator = 1

FROM (

SELECT requestid

,(

CASE

WHEN Request_First_Action IN (

'SUGGESTED'

,'APPROVED'

)

THEN DaysSince_Request_First_Action

END

) dts

FROM #temp_results

WHERE (

Request_First_Action IN (

'SUGGESTED'

,'APPROVED'

)

)

--group by requestid

) tmp

WHERE #temp_results.requestid = tmp.requestid

--update results1

--set SuggestedInTime =

-- case

-- when isnull(ResourceSuggested, -1) = -1 then 0

-- when DaysToSuggested <= 7 then 1

-- else 0

-- end

--update results1

--set DaysToSuggestedIndicator =

-- case

-- when isnull(ResourceSuggested, -1) = -1 then 0 -- else 1

-- end

--"Clock should stop when either of this occurs: --1) When the requests is accepted --2) When the request is cancelled --3) When request times out"

UPDATE #temp_results

SET DaysToAccepted =

-- case isnull(Completed, -1)

-- when -1 then DATEDIFF(day, Initiation, getdate())

-- else DATEDIFF(day, Initiation, Completed)

-- end

CASE -- added by jayaraj with requirement for accepted.

WHEN RequestStatus = 'Completed, Accepted'

OR RequestStatus = 'Completed, Cancelled'

THEN DATEDIFF(day, Initiation, Completed)

--when RequestStatus ='Completed, Cancelled'

WHEN RequestStatus = 'Completed, TimedOut'

THEN DATEDIFF(day, Initiation, RequestTimedoutDate)

ELSE DATEDIFF(day, Initiation, getdate())

END

UPDATE #temp_results

SET DaysToAcceptedIndicator = CASE

--when isnull(Completed, -1) = -1 then 0

WHEN RequestStatus LIKE 'Completed, Accepted'

THEN 1

ELSE 0

END

--update temp_results

--set RequestCreatedInTime =

-- case

-- when DaysToCommitment >= @vRequestCreateTime then 1

-- else 0

-- end

--FROM

-- (select requestid,region,

-- from temp_results

-- --group by requestid

-- ) tmp

--join PVC_PrimaryCenter_GSR GSR

--on GSR.PrimaryCenterName= substring((select personname from evuser where oid = poolownerentryRecipient.useroid),0,

-- charindex(',',(select personname from evuser where oid = poolownerentryRecipient.useroid)))

--where temp_results.requestid=tmp.requestid and temp_results.[region]=tmp.[region]

--and temp_results.ownerpoolsname is not null and

/* Distinct Values into Temp_leadtime table*/

INSERT INTO #leadtime (

RequestOID

,ProjectPool

,Region

,PC

,RequestID

,Creator

,CreatorsPool

,CreateDate

,Client

,Project

,Position

,PositionOID

,FunctionalRole

,RequestStatus

,PositionCreated

,Initiation

,Completed

,ResourceSuggested

,StaffingAssignmentStatus

,StaffingAssignmentResource

,CommitmentStartDate

,DaysToSuggested

,SuggestedInTime

,DaysToAccepted

,DaysToCommitment

,DaysToSuggestedIndicator

,DaysToAcceptedIndicator

,RequestCreatedInTime

,RecipientName

,Recipient_First_Action

,Recipient_Last_Action

,DaysSince_First_Action

,DaysSince_Last_Action

,Request_First_Action

,DaysSince_Request_First_Action

,OwnerPoolsName

,RecipientDaysToSuggested

,RecipientDaysToAccepted

,RecipientStatus

,ResipientCreateDate

,RecipientRespondedDate

,RequestTimedoutDate

,ReceivedDatetime

)

SELECT DISTINCT RequestOID

,ProjectPool

,Region

,PC

,RequestID

,Creator

,CreatorsPool

,CreateDate

,Client

,Project

,Position

,PositionOID

,FunctionalRole

,RequestStatus

,PositionCreated

,Initiation

,Completed

,ResourceSuggested

,StaffingAssignmentStatus

,StaffingAssignmentResource

,CommitmentStartDate

,DaysToSuggested

,SuggestedInTime

,DaysToAccepted

,DaysToCommitment

,DaysToSuggestedIndicator

,DaysToAcceptedIndicator

,RequestCreatedInTime

,RecipientName

,Recipient_First_Action

,Recipient_Last_Action

,DaysSince_First_Action

,DaysSince_Last_Action

,Request_First_Action

,DaysSince_Request_First_Action

,NULL

,RecipientDaysToSuggested

,RecipientDaysToAccepted

,RecipientStatus

,ResipientCreateDate

,RecipientRespondedDate

,RequestTimedoutDate

,ReceivedDatetime

FROM #temp_results

UPDATE #leadtime

SET #leadtime.OwnerPoolsName = #temp_results.OwnerPoolsName

FROM #leadtime

INNER JOIN #temp_results ON #temp_results.RequestOID = #leadtime.RequestOID

AND #temp_results.ProjectPool = #leadtime.ProjectPool

AND #temp_results.Region = #leadtime.Region

AND #temp_results.PC = #leadtime.PC

--

--select * into rrm_tempresult from #temp_results

--select * into rrm_leadtime from #leadtime

UPDATE #temp_results

SET RequestCreatedInTime = CASE

WHEN #temp_results.DaysToCommitment >= @vRequestCreateTime

THEN 1

--and temp_results.region=rtrim(substring(temp_results.ownerpoolsname,1,charindex('_',temp_results.ownerpoolsname)-1))

ELSE 0

END

FROM #leadtime

INNER JOIN #temp_results ON #temp_results.RequestOID = #leadtime.RequestOID

AND #temp_results.ProjectPool = #leadtime.ProjectPool

AND #temp_results.Region = #leadtime.Region

AND #temp_results.PC = #leadtime.PC

--and #temp_results.OwnerPoolsName=#leadtime.OwnerPoolsName /* change done on 27-10-2010 debasis*/

--where #temp_results.OwnerPoolsName is not null /* change done on 27-10-2010 debasis*/

UPDATE #temp_results

SET RecipientDaysToSuggested = CASE

WHEN RequestStatus = 'Completed, Accepted'

OR RequestStatus = 'Completed, Cancelled'

THEN DATEDIFF(day, Initiation, Completed)

WHEN RecipientStatus IN ('Request_Timedout')

THEN --ADDED

DATEDIFF(day, Initiation, RequestTimedoutDate) --ADDED

WHEN RequestStatus = 'Completed, TimedOut'

THEN DATEDIFF(day, Initiation, RequestTimedoutDate)

ELSE DATEDIFF(day, Initiation, getdate())

END

UPDATE #temp_results

SET RecipientDaysToSuggested = tmp.dts

,SuggestedInTime = DaysSince_First_Action --1 --- RecipientDaysToSuggestedIndicator

FROM (

SELECT requestoid

,recipientname

,ProjectPool

,Region

,PC

,CASE

WHEN Recipient_First_Action IN (

'SUGGESTED'

,'APPROVED'

,'FORWARDED'

)

THEN DaysSince_First_Action

WHEN Recipient_Last_Action IN (

'SUGGESTED'

,'APPROVED'

,'FORWARDED'

)

THEN DaysSince_Last_Action

END dts

FROM #temp_results

WHERE (

Recipient_First_Action IN (

'SUGGESTED'

,'APPROVED'

,'FORWARDED'

)

OR Recipient_Last_Action IN (

'SUGGESTED'

,'APPROVED'

,'FORWARDED'

)

)

GROUP BY recipientname

,requestoid

,recipientstatus

,Recipient_First_Action

,Recipient_Last_Action

,DaysSince_First_Action

,DaysSince_Last_Action

,ProjectPool

,Region

,PC

) tmp

WHERE #temp_results.requestoid = tmp.requestoid

AND #temp_results.ProjectPool = tmp.ProjectPool

AND #temp_results.Region = tmp.Region

AND #temp_results.PC = tmp.PC

AND #temp_results.recipientname = tmp.recipientname

--and #temp_results.ownerpoolsname is not null /* change done on 27-10-2010 debasis*/

UPDATE #temp_results

SET RecipientDaysToAccepted = CASE

WHEN RequestStatus = 'Completed, Accepted'

THEN DATEDIFF(day, Initiation, Completed)

WHEN RequestStatus != 'Completed, Accepted'

THEN DATEDIFF(day, Initiation, getdate())

WHEN RequestStatus = 'Completed, TimedOut'

THEN DATEDIFF(day, Initiation, RequestTimedoutDate)

ELSE 0

END

TRUNCATE TABLE #results

INSERT INTO #results

SELECT *

FROM #temp_results

--delete from #results where OwnerPoolsName is null and Region <> 'Missing Recipient'

UPDATE #results

SET OwnerPoolsName = 'Pool not available'

WHERE OwnerPoolsName IS NULL

INSERT INTO StatusReader

VALUES ('Insert @pinternalRegion=1')

IF (@pinternalRegion = 1)

BEGIN

DELETE #results

FROM #results

INNER JOIN #internal ON #results.requestoid = #internal.requestoid

END

TRUNCATE TABLE TempRRMResults

INSERT INTO TempRRMResults

SELECT *

FROM #results

--select * into TempRRMResults from #results

IF (

SELECT count(1)

FROM rrmfix1

) = 0

BEGIN

SELECT '0' AS ProjectId

,'-' AS PositionID

,'-' AS PositionName

,getdate() AS PositionStartDate

,'NA' AS CDName

,'NA' AS JRName

,'-' AS OrgName

,

--0 as RequestOID,

'-' AS ProjectPool

,'-' AS Region

,'-' AS PC

,'' AS RequestID

,'No qualifing data returned.' AS Creator

,'' AS CreatorsPool

,getdate() AS CreateDate

,'' AS Client

,'' AS Project

,'' AS Position

,0 AS PositionOID

,'' AS FunctionalRole

,'' AS RequestStatus

,getdate() AS Initiation

,getdate() AS Completed

,getdate() AS ResourceSuggested

,'' AS StaffingAssignmentStatus

,'' AS StaffingAssignmentResource

,getdate() AS CommitmentStartDate

,0 AS DaysToSuggested

,0 AS SuggestedInTime

,0 AS DaysToAccepted

,0 AS DaysToCommitment

,0 AS DaysToSuggestedIndicator

,0 AS DaysToAcceptedIndicator

,0 AS RequestCreatedInTime

,'' AS RecipientName

,'' AS Recipient_First_Action

,'' AS Recipient_Last_Action

,0 AS DaysSince_First_Action

,0 AS DaysSince_Last_Action

,'' AS OwnerPoolsName

,0 AS RecipientDaysToSuggested

,0 AS RecipientDaysToAccepted

,'' AS RecipientStatus

,getdate() AS ResipientCreateDate

,getdate() AS RecipientRespondedDate

,getdate() AS RequestTimedoutDate

,'-' AS PCR

,getdate() AS ReceivedDatetime

END

IF (@pinternalRegion = 1)

BEGIN

IF EXISTS (

SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'[dbo].[ABC]')

AND type IN (N'U')

)

DROP TABLE [dbo].[ABC]

SELECT *

INTO ABC

FROM #ProjectPosition_JR_CD

SELECT DISTINCT rs.ProjectId

,rs.PositionID

,rs.PositionName

,rs.PositionStartDate

,rs.CDName

,rs.JRName

,rs.OrgName OrgName

,r.ProjectPool

,

--ProjectPool,

r.Region

,r.PC

,r.RequestID

,r.Creator

,r.CreatorsPool

,r.CreateDate

,r.Client

,r.Project

,r.Position

,r.PositionOID

,r.FunctionalRole

,r.RequestStatus

,r.Initiation

,r.Completed

,r.ResourceSuggested

,r.StaffingAssignmentStatus

,r.StaffingAssignmentResource

,r.CommitmentStartDate

,r.DaysToSuggested

,r.SuggestedInTime

,r.DaysToAccepted

,r.DaysToCommitment

,r.DaysToSuggestedIndicator

,r.DaysToAcceptedIndicator

,r.RequestCreatedInTime

,r.RecipientName

,r.Recipient_First_Action

,r.Recipient_Last_Action

,r.DaysSince_First_Action

,r.DaysSince_Last_Action

,ISNULL(r.OwnerPoolsName, '') AS OwnerPoolsName

,r.RecipientDaysToSuggested

,r.RecipientDaysToAccepted

,r.RecipientStatus

,r.ResipientCreateDate

,r.RecipientRespondedDate

,r.RequestTimedoutDate

,upper(r.pc) AS PCR

,r.ReceivedDateTime

-- into #rrm_temp

FROM #results r

LEFT JOIN (

SELECT *

FROM #ProjectPosition_JR_CD

) rs ON r.positionoid = rs.oid

WHERE r.Region = @pProjectPool

---- select distinct

---- rs.ProjectId

---- ,rs.PositionID

---- ,rs.PositionName

---- ,rs.PositionStartDate

---- ,rs.CDName

---- ,rs.JRName

---- ,rs.OrgName OrgName,

---- r.*

---- from #rrm_temp r left outer join (select * from #ProjectPosition_JR_CD) rs

---- ON r.positionoid = rs.oid

----select distinct

---- rs.ProjectId

---- ,rs.PositionID

---- ,rs.PositionName

---- ,rs.PositionStartDate

---- ,rs.CDName

---- ,rs.JRName

---- ,rs.OrgName OrgName,

----

---- r.ProjectPool ,

---- --ProjectPool,

---- r.Region,

---- r.PC,

---- r.RequestID,

---- r.Creator,

---- r.CreatorsPool,

---- r.CreateDate,

---- r.Client,

---- r.Project,

---- r.Position,

---- r.PositionOID,

---- r.FunctionalRole,

---- r.RequestStatus,

---- r.Initiation,

---- r.Completed,

---- r.ResourceSuggested,

---- r.StaffingAssignmentStatus,

---- r.StaffingAssignmentResource,

---- r.CommitmentStartDate,

---- r.DaysToSuggested, r.SuggestedInTime,

---- r.DaysToAccepted,

---- r.DaysToCommitment,

---- r.DaysToSuggestedIndicator,

---- r.DaysToAcceptedIndicator,

---- r.RequestCreatedInTime,

---- r.RecipientName,

---- r.Recipient_First_Action ,

---- r.Recipient_Last_Action ,

---- r.DaysSince_First_Action ,

---- r.DaysSince_Last_Action ,

---- ISNULL(r.OwnerPoolsName, '') as OwnerPoolsName,

---- r.RecipientDaysToSuggested,

---- r.RecipientDaysToAccepted,

---- r.RecipientStatus,

---- r.ResipientCreateDate,

---- r.RecipientRespondedDate,

---- r.RequestTimedoutDate ,

---- upper(r.pc) as PCR,

---- r.ReceivedDateTime

------ into #rrm_temp

---- from #results r left outer join (select * from #ProjectPosition_JR_CD) rs

---- ON rs.oid = r.positionoid

---- Where r.Region=@pProjectPool

END

ELSE

BEGIN

SELECT DISTINCT r.ProjectId

,r.PositionID

,r.PositionName

,r.PositionStartDate

,r.CDName

,r.JRName

,r.OrgName OrgName

,

--ProjectPool as

ProjectPool

,Region

,PC

,RequestID

,Creator

,CreatorsPool

,CreateDate

,Client

,Project

,Position

,PositionOID

,FunctionalRole

,RequestStatus

,Initiation

,Completed

,ResourceSuggested

,StaffingAssignmentStatus

,StaffingAssignmentResource

,CommitmentStartDate

,DaysToSuggested

,SuggestedInTime

,DaysToAccepted

,DaysToCommitment

,DaysToSuggestedIndicator

,DaysToAcceptedIndicator

,RequestCreatedInTime

,RecipientName

,Recipient_First_Action

,Recipient_Last_Action

,DaysSince_First_Action

,DaysSince_Last_Action

,ISNULL(OwnerPoolsName, '') AS OwnerPoolsName

,RecipientDaysToSuggested

,RecipientDaysToAccepted

,RecipientStatus

,ResipientCreateDate

,RecipientRespondedDate

,RequestTimedoutDate

,upper(pc) AS PCR

,ReceivedDatetime

--into rrm_Temp

FROM #results

LEFT JOIN (

SELECT *

FROM #ProjectPosition_JR_CD

) r ON positionoid = r.oid -- AND (r.ccmflag=0 OR r.ccmflag=4)

------ UNION ALL

END

--select * from #rrm_temp

END -- proc

--select * into RptRegionRequestMeasurementsTempResults from #results

DROP TABLE #temp_results

DROP TABLE #leadtime

DROP TABLE #regiontable

DROP TABLE #PCtable

DROP TABLE #ProjectPosition_JR_CD

DROP TABLE #ProjectTeamSlot

DROP TABLE #StaffingRequest

SET NOCOUNT OFF

--select * from RptRegionRequestMeasurementsTempResults

--where requestid = 524441

----

OK so now we have code that is at least legible. The problem here is that you posted a stored proc with over 1600 lines and expect somebody to come along and make this fast. I don't mean in any way to sound negative but you need a lot more help than can be provided by an online forum. You need to hire a professional to come on site and help sort this out. There are so many issues with this code.

Why do you need to truncate temp tables immediately after creating them?

You have lots of looping and very inefficient code. To properly tune this query I would quote a client that it would take 2-3 weeks at a minimum. There are dozens of tables and functions in here.

Why all the NOLOCK hints? Are you familiar with the issues that hint brings to the table? I have a feeling that hint was thrown in here to make it faster without understanding the pitfalls.

You have setup this process with multiple execution paths so that no matter the tuning this will continue to perform poorly because the execution plan will need to be different for each execution.