[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select Into Temp Table In Stored Procedure

Posted on 2006-04-24
13
Medium Priority
?
578 Views
Last Modified: 2008-01-09
I have a stored procedure that uses a series of Selects to get down to a specific grouping of data. I am now attempting to stick the result set into a temp table, in order to combine it with other selections. The problem is that the Select Into does not seem to work. As you'll see below, I can manually insert one record, and have it return successfully, but not stick a set of a data into it.

Testing further, even if my Select only returns a single line, the problem persists.

Any ideas?

CREATE TABLE #OT(
      Employee nvarchar(75),
      [Total Hours] float,
      [Approved Hours] float,
      Discrepancy float,
      [Over Time] float,
      [Approved OT] float
)

INSERT INTO #OT values('Test Guy',9.9,9.9,9.9,9.9,9.9)

EXEC('SELECT A.FullName as [Employee], A.[Total Hours], A.[Approved Hours], (A.[Total Hours]-A.[Approved Hours]) as [Discrepancy], (A.[Total Hours]-' + @otHours + ') as [Over Time], (A.[Approved Hours]-' + @otHours + ') as [Approved OT] INTO #OT FROM (SELECT FullName, sum(totHours) as [Total Hours], sum(AA.appMon + AA.appTues + AA.appWed + AA.appThurs + AA.appFri + AA.appSat + AA.appSun) as [Approved Hours] FROM (' + @sum1 + ' UNION ' + @sum2 + ' UNION ' + @sum3 + ' UNION ' + @sum4 + ' UNION ' + @sum5 + ' UNION ' + @sum6 + ' UNION ' + @sum7 + ' UNION ' + @statement1 + ') as AA GROUP BY FullName) as A');

SELECT * FROM #OT;
0
Comment
Question by:GENTP
  • 7
  • 2
  • 2
  • +1
13 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16527643
that's because #OT doesn't exist within the scope of the environment that the EXECUTE command operates.  You can't combine dynamic SQL and temporary tables or table variables.
0
 
LVL 5

Author Comment

by:GENTP
ID: 16527703
Any known work-arounds? That EXEC statement is actually 1800 lines into my stored procedure...
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 16527895
you would have to use a global temp table "##OT" or a permanent table
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16528001
As long as the temp table was created prior to the dynamic SQL, that should be OK.

But what do the variables you are using contain?: @sum1, @sum2, etc..
0
 
LVL 5

Author Comment

by:GENTP
ID: 16528080
They contain other select statements (basically just sum one day at a time sun-sat where that day happens to be approved).

When you say: "As long as the temp table was created prior to the dynamic SQL, that should be OK." Do you mean prior to the dynamic SQL being created, or executed? Because the table is created prior to execution, and therefore I would deduct that it is not okay...

I'm leaning towards the global temp table, since this is a weekly report and won't really have many worries of multiple connections simultaneously, but am still open to more suggestions.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 16528449
>>  Do you mean prior to the dynamic SQL being created, or executed? <<

Executed.  If the temp table exists at the time the dynamic SQL is executed, it should work.


>> They contain other select statements (basically just sum one day at a time sun-sat where that day happens to be approved). <<

Maybe there is an error in that code.  What specific error(s) do you get when you run the code in QA?
0
 
LVL 5

Author Comment

by:GENTP
ID: 16528663
Here is the QA messages. The result set just has the Test Guy - 9.9 record.

DECLARE @RC int
DECLARE @dept nvarchar(25)
DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @otHours float
SELECT @dept = N'2,5'
SELECT @startDate = '4/23/2006'
SELECT @endDate = '4/30/2006'
SELECT @otHours = 45
EXEC @RC = [TimeManager].[dbo].[new_get_user_OT_report_test2] @dept, @startDate, @endDate, @otHours
DECLARE @PrnLine nvarchar(4000)
PRINT 'Stored Procedure: TimeManager.dbo.new_get_user_OT_report_test2'
SELECT @PrnLine = '      Return Code = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
0
 
LVL 4

Expert Comment

by:BulZeyE
ID: 16528717
If you have the temp table created... then the dynamic sql should be doing a:

INSERT INTO (......)
SELECT ....
UNION
SELECT...

rather than a Select INTO..

0
 
LVL 5

Author Comment

by:GENTP
ID: 16529204
I'm turning into one of these people that drags a question out forever...sorry guys. Thanks for all of the help as well.

I get this result (running through the VS 2003 IDE) (print statements added for debugging to help show that the dynamic sql is the problem):

Running dbo."new_get_user_OT_report_test2" ( @dept = 0, @startDate = 4/23/2006, @endDate = 4/30/2006, @otHours = 0 ).

here
Invalid object name '#OT'.
here2
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."new_get_user_OT_report_test2".

From this code:

CREATE TABLE #OT(
     Employee nvarchar(75),
     [Total Hours] float,
     [Approved Hours] float,
     Discrepancy float,
     [Over Time] float,
     [Approved OT] float
)

INSERT INTO #OT values('Test Guy',9.9,9.9,9.9,9.9,9.9)
print 'here';
EXEC('INSERT INTO #OT (''Employee'',''Total Hours'', ''Approved Hours'', ''Discrepancy'', ''Over Time'', ''Approved OT'') SELECT A.FullName, A.[Total Hours], A.[Approved Hours], (A.[Total Hours]-A.[Approved Hours]), (A.[Total Hours]-' + @otHours + '), (A.[Approved Hours]-' + @otHours + ') FROM (SELECT FullName, sum(totHours) as [Total Hours], sum(AA.appMon + AA.appTues + AA.appWed + AA.appThurs + AA.appFri + AA.appSat + AA.appSun) as [Approved Hours] FROM (' + @sum1 + ' UNION ' + @sum2 + ' UNION ' + @sum3 + ' UNION ' + @sum4 + ' UNION ' + @sum5 + ' UNION ' + @sum6 + ' UNION ' + @sum7 + ' UNION ' + @statement1 + ') as AA GROUP BY FullName) as A');
print 'here2';
SELECT * FROM #OT;
GO
0
 
LVL 4

Accepted Solution

by:
BulZeyE earned 1200 total points
ID: 16530231
OK, Question 1,

is this the entire set?  Are you setting @Sum1 equal to anything?  Are they going in NULL?
Can you try this:

CREATE TABLE #OT(
     Employee nvarchar(75),
     [Total Hours] float,
     [Approved Hours] float,
     Discrepancy float,
     [Over Time] float,
     [Approved OT] float
)

INSERT INTO #OT values('Test Guy',9.9,9.9,9.9,9.9,9.9)
print 'here';
DECLARE @SQLStatement NVARCHAR(4000)

SET @SQLStatement = 'INSERT INTO #OT (''Employee'',''Total Hours'', ''Approved Hours'', ''Discrepancy'', ''Over Time'', ''Approved OT'') SELECT A.FullName, A.[Total Hours], A.[Approved Hours], (A.[Total Hours]-A.[Approved Hours]), (A.[Total Hours]-' + @otHours + '), (A.[Approved Hours]-' + @otHours + ') FROM (SELECT FullName, sum(totHours) as [Total Hours], sum(AA.appMon + AA.appTues + AA.appWed + AA.appThurs + AA.appFri + AA.appSat + AA.appSun) as [Approved Hours] FROM (' + @sum1 + ' UNION ' + @sum2 + ' UNION ' + @sum3 + ' UNION ' + @sum4 + ' UNION ' + @sum5 + ' UNION ' + @sum6 + ' UNION ' + @sum7 + ' UNION ' + @statement1 + ') as AA GROUP BY FullName) as A'
print COALESCE(@SQLStatement,'@SQLStatement is null')
EXEC(@SQLStatement);
print 'here2';
SELECT * FROM #OT;

and post the output?  I am just wondering if the SQLStatement is NULL..

0
 
LVL 5

Author Comment

by:GENTP
ID: 16533765
With:
CREATE TABLE #OT(
     Employee nvarchar(75),
     [Total Hours] float,
     [Approved Hours] float,
     Discrepancy float,
     [Over Time] float,
     [Approved OT] float
)

INSERT INTO #OT values('Test Guy',9.9,9.9,9.9,9.9,9.9)
print 'here';
DECLARE @SQLStatement NVARCHAR(4000);
SET @SQLStatement = 'INSERT INTO #OT (''Employee'',''Total Hours'', ''Approved Hours'', ''Discrepancy'', ''Over Time'', ''Approved OT'') SELECT A.FullName, A.[Total Hours], A.[Approved Hours], (A.[Total Hours]-A.[Approved Hours]), (A.[Total Hours]-' + @otHours + '), (A.[Approved Hours]-' + @otHours + ') FROM (SELECT FullName, sum(totHours) as [Total Hours], sum(AA.appMon + AA.appTues + AA.appWed + AA.appThurs + AA.appFri + AA.appSat + AA.appSun) as [Approved Hours] FROM (' + @sum1 + ' UNION ' + @sum2 + ' UNION ' + @sum3 + ' UNION ' + @sum4 + ' UNION ' + @sum5 + ' UNION ' + @sum6 + ' UNION ' + @sum7 + ' UNION ' + @statement1 + ') as AA GROUP BY FullName) as A';

print COALESCE(@SQLStatement,'@SQLStatement is null')

EXEC(@SQLStatement);

print 'here2';

SELECT * FROM #OT;
GO


I get:

Running dbo."new_get_user_OT_report_test2" ( @dept = 1, @startDate = 04/22/2006, @endDate = 04/26/2006, @otHours = 40 ).

here
Error converting data type varchar to float.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running dbo."new_get_user_OT_report_test2".
0
 
LVL 5

Author Comment

by:GENTP
ID: 16533837
As a side bar, here's a quick (or should I say verbose?) dump of sum1-7 and statement1:

SELECT FullName, sum(monHours) as appMon, 0 as appTues, 0 as appWed, 0 as appThurs, 0 as appFri, 0 as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as AA WHERE lockMon=1 GROUP BY FullName

SELECT FullName, 0 as appMon, sum(tuesHours) as appTues, 0 as appWed, 0 as appThurs, 0 as appFri, 0 as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as BA WHERE lockTues=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, sum(wedHours) as appWed, 0 as appThurs, 0 as appFri, 0 as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as CA WHERE lockWed=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, 0 as appWed, sum(thursHours) as appThurs, 0 as appFri, 0 as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as DA WHERE lockThurs=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, 0 as appWed, 0 as appThurs, sum(friHours) as appFri, 0 as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as EA WHERE lockFri=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, 0 as appWed, 0 as appThurs, 0 as appFri, sum(satHours) as appSat, 0 as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as FA WHERE lockSat=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, 0 as appWed, 0 as appThurs, 0 as appFri, 0 as appSat, sum(sunHours) as appSun, 0 as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as GA WHERE lockSun=1 GROUP BY FullName

SELECT FullName, 0 as appMon, 0 as appTues, 0 as appWed, 0 as appThurs, 0 as appFri, 0 as appSat, 0 as appSun, sum(monHours + tuesHours + wedHours + thursHours + friHours + satHours + sunHours) as totHours FROM (SELECT TOP 5000 AA.FullName, week, 0 as sunHours, 0 as monHours, 0 as tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, AA.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as AA WHERE AA.week = CONVERT(datetime, '04/16/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, 0 as wedHours, 0 as thursHours, 0 as friHours, 0 as satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week = CONVERT(datetime, '04/23/2006', 101) UNION SELECT FullName, week, BB.sunHours, BB.monHours, BB.tuesHours, BB.wedHours, BB.thursHours, BB.friHours, BB.satHours, lockSun, lockMon, lockTues, lockWed, lockThurs, lockFri, lockSat FROM (SELECT * FROM (SELECT emp.FullName, hrs.week, hrs.sunHours, hrs.monHours, hrs.tuesHours, hrs.wedHours, hrs.thursHours, hrs.friHours, hrs.satHours, hrs.lockSun, hrs.lockMon, hrs.lockTues, hrs.lockWed, hrs.lockThurs, hrs.lockFri, hrs.lockSat FROM new_tbl_hours as hrs, new_tbl_users as emp WHERE  (emp.Dept LIKE '1'  OR emp.Dept LIKE '%,1'  OR emp.Dept LIKE '1,%') AND hrs.empID = emp.[ID])  as AA WHERE  (AA.week = CONVERT(datetime, '04/16/2006', 101) OR AA.week = CONVERT(datetime, '04/23/2006', 101)  OR (AA.week <= CONVERT(datetime, '04/22/2006', 101) AND AA.week >= CONVERT(datetime, '04/23/2006', 101))) ) as BB WHERE BB.week <= CONVERT(datetime, '04/22/2006', 101)  AND BB.week >= CONVERT(datetime, '04/23/2006', 101) ) as HA GROUP BY FullName
0
 
LVL 5

Author Comment

by:GENTP
ID: 16537134
I ended up going in a completly different direction (that applies the KISS logic) to get my final result, and did not end up using a temp table.

Thanks for all of the help guys, I'm going to split this one evenly between Scott and Bullzeye.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question