Monday, March 26, 2012

Easy Q.. Query without temp table

How do I rewrite this query so that I do not use a temp table?

SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

INTO #TEMP1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID

SELECT * FROM #TEMP1 WHERE MINR1 = MAXR1

Use a derived table:

SELECT *

FROM (SELECT SITEID, MIN(R1PROGRAM) AS MINR1, MAX(R1PROGRAM) AS MAXR1

FROM SITECONTROLDATA

WHERE CALC_DATE > GETDATE() - 12

GROUP BY SITEID ) as TEMP1
WHERE MINR1 = MAXR1

|||Thanks!sql

No comments:

Post a Comment