Select Into Temp Table In Stored Procedure

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;
LVL 5
GENTPAsked:
Who is Participating?
 
BulZeyECommented:
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
 
Brian CroweDatabase AdministratorCommented:
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
 
GENTPAuthor Commented:
Any known work-arounds? That EXEC statement is actually 1800 lines into my stored procedure...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Brian CroweDatabase AdministratorCommented:
you would have to use a global temp table "##OT" or a permanent table
0
 
Scott PletcherSenior DBACommented:
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
 
GENTPAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
>>  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
 
GENTPAuthor Commented:
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
 
BulZeyECommented:
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
 
GENTPAuthor Commented:
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
 
GENTPAuthor Commented:
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
 
GENTPAuthor Commented:
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
 
GENTPAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.