Can I avoid temporary tables? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can I avoid temporary tables?

Author

Topic

sviskon
Starting Member

2 Posts

Posted - 2006-10-11 : 10:01:40

Hi,

I got the following select statement. As it is now I do a SELECT INTO to create a temporary table for me to be able to the SUM in my second SELECT statement. What I want to do is sum up the column incidentTime grouped by incidentCode and lastDayOfWeek. Anyone can direct me how I can avoid creating the temporary table and maybe the sum in the first selectstatement? I tried alot of variations but non seem to work. I am quite new to more advanced sql statements. I work with SQL Server 2000. Thanks in advance!

SQL STATEMENT 1
-
SELECT
incidentCode,
POWER(-1,isNegative)*(finishTime-startTime) As incidentTime,
dateadd(dd,datediff(dd,0,dateadd(wk,datediff(wk,0,occurrenceDate),0)),5) As lastDayofWeek
INTO tmpIncidents
FROM tblIncidents
WHERE occurrenceDate >= DATEADD(dd,DATEDIFF(dd,0,getdate()),-180)
AND occurrenceDate <= getDate()
AND sentToUniPay=0
AND empID = 2847239843
-
SQL STATEMENT 2
-
SELECT i.incidentCode,SUM(i.incidentTime) As value,i.lastDayOfWeek,ic.uniPayCode
FROM tmpIncidents i
INNER JOIN tblIncidentCodes ic
ON i.incidentCode = ic.incidentCode
GROUP BY i.incidentCode,i.lastDayOfWeek,ic.uniPayCode
-

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-11 : 10:08:09

You could try replacing the temp table by making the query that creates the temp table a derived table in the second query.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-11 : 10:12:03

Sure, no problem


SELECT i.incidentCode,SUM(i.incidentTime) As value,i.lastDayOfWeek,ic.uniPayCode 
FROM  (
SELECT    incidentCode
	, POWER(-1,isNegative)*(finishTime-startTime) As incidentTime
	, dateadd(dd,datediff(dd,0,dateadd(wk,datediff(wk,0,occurrenceDate),0)),5) As lastDayofWeek 
  FROM    tblIncidents 
 WHERE    occurrenceDate >= DATEADD(dd,DATEDIFF(dd,0,getdate()),-180)
   AND    occurrenceDate <= getDate()
   AND    sentToUniPay=0
   AND    empID = 2847239843
) AS i
INNER JOIN tblIncidentCodes ic 
ON i.incidentCode = ic.incidentCode 
GROUP BY i.incidentCode,i.lastDayOfWeek,ic.uniPayCode

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-11 : 10:12:55

You could replace tmpIncidents by #tmpIncidents. That wouild stop it creating a permanent table. And maybe an index would help. What is the problem you are trying to solve?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sviskon
Starting Member

2 Posts

Posted - 2006-10-11 : 15:53:32

Worked great with the help I got here. Thanks alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources