Create a View from select statement that uses multiple temp tables in T-SQL to remove the need for the temp tables

Question

We have a very normalized SQL 2005 database. The problem is that I need a query that de-normalizes the data and puts it into a view. Currently, I have a query that gets the desired result set; but I am using three temp tables to do so and need to switch to a view in order to run the SQL via a third party reporting software that we do not want to give the ability to create temp tables to. Since the view needs to be a single select statement; I am asking for help in making my ugly query into a single select statement that can be a view.

The tables involved are: ModelResults [CustomerID,ModelID,RunDate,Score,ModelResultID(= modelresultsreviewid)]

Customers [CustomerID]

Models [ModelName,ModelID]

The views involved are: (this is the set of all ModelResults that have a Review) vw_exp_review [CustomerID,modelresultsreviewid]

Their relationship is as follows:

Every Customer has a CustomerID; but not necessarily a ModelResult or Review or either

Every Model has a ModeID and ModelName

Every ModelResult has a CustomerID,ModelID,RunDate,and Score but not necessarily a Review

Every Review(vw_exp_review) has a CustomerID and modelresultsreviewid

The goal of the query is to find the CustomerIDs,Scores,Models, and RunDates that all have the same CustomerID and ModelID where:

1. There are no modelResults or Reviews (in which case all that is returned is a CustID)

2. The Most Recently Scored Model (Max(RunDate)) regarless of review

3. The Most Recently Scored Model (Max(RunDate)) that has been reviewed

There are generally three kinds of output

1. CustomerID, MostRecentScore,MostRecentReviewedScore,Model, and MaxDate

2. CustomerID,MostRecentScore,Model, and MaxDate

3. CustomerID

As of today, I am still using the following:

DROP TABLE #_T1

-- MostRecentScore

SELECT CustomerID,ModelID,ModelResultID,RunDate,Min(Score) as MinScore

INTO #_T1

FROM ModelResults m1 WITH (NOLOCK)

WHERE RunDate IN (SELECT MAX(m.RunDate) FROM ModelResults m GROUP BY m.CustomerID)

GROUP BY

CustomerID,ModelID,ModelResultID,RunDate




DROP TABLE #_T2

--MostRecentReviewedScore

SELECT CustomerID,ModelID,RunDate,MIN(Score) AS MinScore

INTO #_T2

FROM ModelResults m1 WITH (NOLOCK)

WHERE RunDate IN (SELECT MAX(RunDate) 

                FROM ModelResults t JOIN vw_exp_review r ON 

                                    r.modelresultsreviewid = t.ModelResultID

                GROUP BY t.CustomerID)

GROUP BY CustomerID,ModelID,RunDate



DROP TABLE #_T3

--MostRecentModelResultDate

SELECT c.CustomerID,MAX(RunDATE) as MAXDate

INTO #_T3

FROM ModelResults mr WITH (NOLOCK)

RIGHT OUTER JOIN Customers C

ON mr.CustomerID = c.CustomerID

GROUP BY c.CustomerID



SELECT t3.CustomerID,t1.MinScore as MostRecentScore,

t2.MinScore as MostRecentReviewedScore,m.Model as ModelName,

t3.MaxDate

FROM #_T1 t1

LEFT OUTER JOIN #_T2 t2

ON t1.CustomerID = t2.CustomerID AND t1.ModelID = t2.ModelID

RIGHT OUTER JOIN #_T3 t3

ON t1.CustomerID = t3.CustomerID

LEFT OUTER JOIN Models m

ON t1.ModelID = m.ModelID


ORDER BY
t3.CustomerID

Sample output:

CustID,MostRecentScore,MostRecentReviewed,ModelName,MaxDate

8,2.36,4.59,Unrated Scorecard,2011-08-10 15:08:53.807

1361,2.76,NULL,SET Rated,2010-04-20 20:48:39.530

1362,NULL,NULL,NULL,NULL

Answer 1

You can use CTEs instead of your temporary tables. Those can be easily used in a view to combine your queries into one, eg:

CREATE VIEW MYVIEW
AS
    WITH T1
    AS
    (
    -- MostRecentScore
      SELECT CustomerID,ModelID,ModelResultID,RunDate,Min(Score) as MinScore
      FROM ModelResults m1 WITH (NOLOCK)
      WHERE RunDate IN (SELECT MAX(m.RunDate) FROM ModelResults m GROUP BY m.CustomerID)
      GROUP BY
      CustomerID,ModelID,ModelResultID,RunDate
    )
    ,T2
    AS
    (
      --MostRecentReviewedScore
      SELECT CustomerID,ModelID,RunDate,MIN(Score) AS MinScore
      FROM ModelResults m1 WITH (NOLOCK)
      WHERE RunDate IN (SELECT MAX(RunDate) 
                        FROM ModelResults t JOIN vw_exp_review r ON 
                        r.modelresultsreviewid = t.ModelResultID
                        GROUP BY t.CustomerID)
      GROUP BY CustomerID,ModelID,RunDate
    )
    ,T3
    AS
    ( 
      SELECT c.CustomerID,MAX(RunDATE) as MAXDate
      FROM ModelResults mr WITH (NOLOCK)
      RIGHT OUTER JOIN Customers C
      ON mr.CustomerID = c.CustomerID
      GROUP BY c.CustomerID
    )
    SELECT 
      t3.CustomerID,t1.MinScore as MostRecentScore,
      t2.MinScore as MostRecentReviewedScore,m.Model as ModelName,
      t3.MaxDate
    FROM T1 t1
    LEFT OUTER JOIN T2 t2
      ON t1.CustomerID = t2.CustomerID AND t1.ModelID = t2.ModelID
    RIGHT OUTER JOIN T3 t3
      ON t1.CustomerID = t3.CustomerID
    LEFT OUTER JOIN Models m
      ON t1.ModelID = m.ModelID
    ORDER BY
    t3.CustomerID

SQL-Fiddle not attached and therefore not tested, since no demo-data in OP.

Be aware of the fact, that CTE is executed every time, it is accessed. You may also write an UDF returning a table with your data. In the function, you can still use temp tables as in OP and create a view as select * from myfunction()