Creating a View - Need Alternate for Temp Table

[lesleycampbell]

I'm trying to create a view in SQL 2008. My code uses a temp table and I'm getting the error that I can not have a temp table in a View. I have included my code below. Does anyone have any suggestions on how to get around using a temp table?

CREATE TABLE #DASHBOARDLC
([Order ID] VARCHAR(15) NULL,
[Branch ID] VARCHAR(10) NULL,
[Customer ID] VARCHAR(10) NULL,
[Division ID] VARCHAR(10) NULL,
Position VARCHAR(50) NULL,
[Start Date] VARCHAR(15) NULL,
[Days Until] VARCHAR(5) NULL,
[Total Needed] CHAR(5) NULL,
[Actively Working] CHAR(5) NULL,
[Future Starts] CHAR(5) NULL,
[In Process] CHAR(5) NULL,
[Open] CHAR(5) NULL,
Applied CHAR(5) NULL,
Candidates CHAR(5) NULL,
GroupNo CHAR(3) NULL,
ColorNumber CHAR(3) NULL
)

INSERT INTO #DASHBOARDLC
SELECT DISTINCT(Order_ID),
Branch_ID,
Customer_ID,
Division_ID,
Client_Job_Title,
CONVERT(VARCHAR,Start_DateTime,1),
DATEDIFF(DAY,GETDATE(),Start_DateTime) AS 'Days Until',
Quantity_Needed,
NULL,
NULL,
NULL,
Quantity_Needed-Quantity_Active_Count AS 'Open',
NULL,
NULL,
NULL,
NULL
FROM OrderMaster
WHERE Order_ID IN ('0000559M','0000559S','0000559V','0000566G','0000566F','0000569D','0000565T','000055VG',
'0000565S','0000566T','0000565V','0000565L','000055X0','000055T7','000055NK','000056BL','000056BF','000056RW',
'000056RV','000056S1','000056S2','000056S3')

UPDATE #DASHBOARDLC
SET [Actively Working] =
(SELECT COUNT(OrderAssignment.Assignment_ID)
FROM OrderAssignment INNER JOIN OrderMaster ON ORDERASSIGNMENT.Order_ID = OrderMaster.Order_ID
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID] AND OrderAssignment.Start_DateTime <= GETDATE() AND
(OrderAssignment.End_Actual_Date IS NULL OR OrderAssignment.End_Actual_Date >= GETDATE()))
FROM #DASHBOARDLC, OrderMaster
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID]

UPDATE #DASHBOARDLC
SET [Future Starts] =
(SELECT DISTINCT COUNT(OrderAssignment.Assignment_ID)
FROM OrderAssignment INNER JOIN OrderMaster ON ORDERASSIGNMENT.Order_ID = OrderMaster.Order_ID
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID] AND OrderAssignment.Start_DateTime > GETDATE() AND
(OrderAssignment.End_Actual_Date IS NULL OR OrderAssignment.End_Actual_Date >= GETDATE()))
FROM #DASHBOARDLC, OrderMaster
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID]

UPDATE #DASHBOARDLC
SET [Future Starts] =
(SELECT DISTINCT COUNT(OrderAssignment.Assignment_ID)
FROM OrderAssignment INNER JOIN OrderMaster ON ORDERASSIGNMENT.Order_ID = OrderMaster.Order_ID
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID] AND OrderAssignment.Start_DateTime > GETDATE() AND
(OrderAssignment.End_Actual_Date IS NULL OR OrderAssignment.End_Actual_Date >= GETDATE()))
FROM #DASHBOARDLC, OrderMaster
WHERE OrderMaster.Order_ID = #DASHBOARDLC.[Order ID]


UPDATE #DASHBOARDLC
SET [In Process] = (
SELECT DISTINCT COUNT(Employee_ID)
FROM HISTORY
WHERE #DASHBOARDLC.[Order ID] = HISTORY.Order_ID AND
HISTORY.Event = 'OE')
FROM #DASHBOARDLC, HISTORY, OrderAssignment
WHERE #DASHBOARDLC.[Order ID] = HISTORY.Order_ID AND OrderAssignment.Order_ID = #DASHBOARDLC.[Order ID] AND
HISTORY.Event = 'OE' AND OrderAssignment.Start_DateTime IS NULL

UPDATE #DASHBOARDLC
SET Applied = (
SELECT DISTINCT COUNT(AMB_Recruiting.dbnlineApplication.App_ID)
FROM AMB_Recruiting.dbnlineApplication
WHERE AMB_Recruiting.dbnlineApplication.Order_ID = #DASHBOARDLC.[Order ID])
FROM #DASHBOARDLC, AMB_Recruiting.dbnlineApplication
WHERE #DASHBOARDLC.[Order ID] = AMB_Recruiting.dbnlineApplication.Order_ID

UPDATE #DASHBOARDLC
SET Candidates = (
SELECT DISTINCT COUNT(Employee_ID)
FROM HISTORY
WHERE #DASHBOARDLC.[Order ID] = HISTORY.Order_ID AND
HISTORY.Event = 'I1')
FROM #DASHBOARDLC, HISTORY
WHERE #DASHBOARDLC.[Order ID] = HISTORY.Order_ID AND
HISTORY.Event = 'I1'

UPDATE #DASHBOARDLC
SET [Days Until] = '-'
WHERE [Days Until] < 0

UPDATE #DASHBOARDLC
SET GroupNo = CASE
WHEN [Order ID] IN ('0000559M','0000559S','0000559V','0000566G','0000566F','000056S3','000056S1','000056S2','000056RV','000056RW') THEN '1' ELSE 2 END
FROM #DASHBOARDLC

UPDATE #DASHBOARDLC
SET ColorNumber = CASE
WHEN [Total Needed] = '0' THEN 3
WHEN [Open] > '0' THEN 1
WHEN [Open] <= '0' THEN 2
ELSE 4 END
FROM #DASHBOARDLC


SELECT * FROM #DASHBOARDLC
ORDER BY GroupNo, ColorNumber, Position


DROP TABLE #DASHBOARDLC



GO

 

[djj55]

Maybe you should use a stored procedure.

Or you can try using a CTE.

Also try collapsing the UPDATE statements into your initial query.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[jbenson001]

Have you tried using a table variable? I'm not sure if that would work, but worth a try.

 

[lesleycampbell]

I have it currently as a Stored Procedure but currently, my users only have access to Views.

I'm new at this so the CTE, more complicated SELECT statement, and variables are still tough for me. I'm trying.