ALTER PROCEDURE [dbo].[SP_RPT_PMOutstandingTasks]

AS 

SELECT
	 [user_id]
	,user_username as Username
	,contact_first_name + ' ' + contact_last_name as FullName
INTO #Users
FROM OPENQUERY(Project,'SELECT u.user_id,u.user_contact,u.user_username,c.contact_id,c.contact_first_name,c.contact_last_name
			FROM Users u
			INNER JOIN contacts c ON c.contact_id=u.user_contact')

CREATE UNIQUE INDEX IX_Users_UserID ON #Users([User_ID])

---------------------------------------------------------------------
SELECT *
INTO #Tasks
FROM OPENQUERY(Project,'SELECT task_id,task_name,task_start_date,task_end_date,task_description,task_creator,task_owner,task_contacts,Task_Percent_Complete FROM tasks WHERE Task_Percent_Complete < 100')

CREATE UNIQUE INDEX IX_Tasks_Task_ID ON #Tasks(Task_ID)
CREATE INDEX IX_Tasks_Task_End_Date ON #Tasks(task_end_date)

---------------------------------------------------------------------
SELECT * 
INTO #UserTasks
FROM OPENQUERY(Project,'SELECT User_ID,Task_ID FROM User_Tasks')

CREATE INDEX IX_UserTasks_User_ID ON #UserTasks([User_ID])
CREATE INDEX IX_UserTasks_Task_ID ON #UserTasks(Task_ID)

---------------------------------------------------------------------
DECLARE @dteToday DATETIME
SET @dteToday = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))
---------------------------------------------------------------------


SELECT DISTINCT
	 t.task_id
	,t.Task_Name
	,t.Task_Description
	,t.Task_Start_Date
	,t.Task_End_Date
	,t.Task_Percent_Complete
	,ut.task_id
	,u3.FullName as AssignedTo

FROM #Tasks t
LEFT JOIN #UserTasks ut ON ut.task_id=t.task_id
LEFT JOIN #Users u3 ON u3.[user_id]=ut.[User_ID]

WHERE
	t.Task_Percent_Complete < 100

AND ut.[User_ID] IS NOT NULL
AND ut.task_id NOT IN (1,5,14,24,30,33,37)

ORDER BY u3.FullName,t.Task_End_Date

DROP TABLE #Users
DROP TABLE #Tasks
DROP TABLE #UserTasks