Speed up query using temp tables

I have a view that is created by two other views in order to link to excel for further analysis.  The final view is running very slow and takes over 55 minutes to run.  Will usining temp tables help optimize the views?  If so can someone help give me code to do this?
View 1
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, 
                      SUM(CASE WHEN R100_Error = 'A' THEN 1 ELSE 0 END) AS R100_Error_A, SUM(CASE WHEN R100_Error = 'E' THEN 1 ELSE 0 END) AS R100_Error_E, 
                      SUM(CASE WHEN R100_Error = 'W' THEN 1 ELSE 0 END) AS R100_Error_W, SUM(CASE WHEN R101_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R101_Error_A, SUM(CASE WHEN R101_Error = 'E' THEN 1 ELSE 0 END) AS R101_Error_E, SUM(CASE WHEN R101_Error = 'W' THEN 1 ELSE 0 END)
                       AS R101_Error_W, SUM(CASE WHEN R102_Error = 'A' THEN 1 ELSE 0 END) AS R102_Error_A, SUM(CASE WHEN R102_Error = 'E' THEN 1 ELSE 0 END)
                       AS R102_Error_E, SUM(CASE WHEN R102_Error = 'W' THEN 1 ELSE 0 END) AS R102_Error_W, 
                      SUM(CASE WHEN R200_Error = 'A' THEN 1 ELSE 0 END) AS R200_Error_A, SUM(CASE WHEN R200_Error = 'E' THEN 1 ELSE 0 END) AS R200_Error_E, 
                      SUM(CASE WHEN R200_Error = 'W' THEN 1 ELSE 0 END) AS R200_Error_W, SUM(CASE WHEN R300_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R300_Error_A, SUM(CASE WHEN R300_Error = 'E' THEN 1 ELSE 0 END) AS R300_Error_E, SUM(CASE WHEN R300_Error = 'W' THEN 1 ELSE 0 END)
                       AS R300_Error_W, SUM(CASE WHEN R301_Error = 'A' THEN 1 ELSE 0 END) AS R301_Error_A, SUM(CASE WHEN R301_Error = 'E' THEN 1 ELSE 0 END)
                       AS R301_Error_E, SUM(CASE WHEN R301_Error = 'W' THEN 1 ELSE 0 END) AS R301_Error_W, 
                      SUM(CASE WHEN R302_Error = 'A' THEN 1 ELSE 0 END) AS R302_Error_A, SUM(CASE WHEN R302_Error = 'E' THEN 1 ELSE 0 END) AS R302_Error_E, 
                      SUM(CASE WHEN R302_Error = 'W' THEN 1 ELSE 0 END) AS R302_Error_W, SUM(CASE WHEN R400_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R400_Error_A, SUM(CASE WHEN R400_Error = 'E' THEN 1 ELSE 0 END) AS R400_Error_E, SUM(CASE WHEN R400_Error = 'W' THEN 1 ELSE 0 END)
                       AS R400_Error_W, SUM(CASE WHEN R401_Error = 'A' THEN 1 ELSE 0 END) AS R401_Error_A, SUM(CASE WHEN R401_Error = 'E' THEN 1 ELSE 0 END)
                       AS R401_Error_E, SUM(CASE WHEN R401_Error = 'W' THEN 1 ELSE 0 END) AS R401_Error_W, 
                      SUM(CASE WHEN R402_Error = 'A' THEN 1 ELSE 0 END) AS R402_Error_A, SUM(CASE WHEN R402_Error = 'E' THEN 1 ELSE 0 END) AS R402_Error_E, 
                      SUM(CASE WHEN R402_Error = 'W' THEN 1 ELSE 0 END) AS R402_Error_W
FROM         dbo.Rxxx_Current_Year_Products
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
 
View 2
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, COUNT(PMG_BG_Level3) 
                      AS NPI_Counts
FROM         dbo.Rxxx_Current_Year_Products
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
HAVING      (PMG_BG IS NOT NULL)
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
Final view comprised of joining view 1 and view 2
 
SELECT     TOP (100) PERCENT dbo.Rxxx_Current_Year_Products_Smry.PMG_BG, dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level0, 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level1, dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level2, 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level3, dbo.Rxxx_Current_Year_Products_Smry.NPI_Counts, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R100_Error_A, dbo.Rxxx_Current_Year_Products_Error_Smry.R100_Error_E, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R100_Error_W, dbo.Rxxx_Current_Year_Products_Error_Smry.R101_Error_A, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R101_Error_E, dbo.Rxxx_Current_Year_Products_Error_Smry.R101_Error_W, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R102_Error_A, dbo.Rxxx_Current_Year_Products_Error_Smry.R102_Error_E, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R102_Error_W, dbo.Rxxx_Current_Year_Products_Error_Smry.R200_Error_A, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R200_Error_E, dbo.Rxxx_Current_Year_Products_Error_Smry.R200_Error_W, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R300_Error_A, dbo.Rxxx_Current_Year_Products_Error_Smry.R300_Error_E, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R300_Error_W, dbo.Rxxx_Current_Year_Products_Error_Smry.R301_Error_A, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R301_Error_E, dbo.Rxxx_Current_Year_Products_Error_Smry.R301_Error_W, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R302_Error_A, dbo.Rxxx_Current_Year_Products_Error_Smry.R302_Error_E, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R302_Error_W, dbo.Rxxx_Current_Year_Products_Error_Smry.R400_Error_A, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R400_Error_E, dbo.Rxxx_Current_Year_Products_Error_Smry.R400_Error_W, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R401_Error_A, dbo.Rxxx_Current_Year_Products_Error_Smry.R401_Error_E, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R401_Error_W, dbo.Rxxx_Current_Year_Products_Error_Smry.R402_Error_A, 
                      dbo.Rxxx_Current_Year_Products_Error_Smry.R402_Error_E, dbo.Rxxx_Current_Year_Products_Error_Smry.R402_Error_W
FROM         dbo.Rxxx_Current_Year_Products_Smry LEFT OUTER JOIN
                      dbo.Rxxx_Current_Year_Products_Error_Smry ON 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG = dbo.Rxxx_Current_Year_Products_Error_Smry.PMG_BG AND 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level0 = dbo.Rxxx_Current_Year_Products_Error_Smry.PMG_BG_Level0 AND 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level1 = dbo.Rxxx_Current_Year_Products_Error_Smry.PMG_BG_Level1 AND 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level2 = dbo.Rxxx_Current_Year_Products_Error_Smry.PMG_BG_Level2 AND 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level3 = dbo.Rxxx_Current_Year_Products_Error_Smry.PMG_BG_Level3
ORDER BY dbo.Rxxx_Current_Year_Products_Smry.PMG_BG, dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level0, 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level1, dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level2, 
                      dbo.Rxxx_Current_Year_Products_Smry.PMG_BG_Level3

Open in new window

FairfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
I have seen where on very large datasets and with lots of aggregations like all your sums, that using a #temp table can speed things up.  See if the code below works, I assumed View1 was dbo.Rxxx_Current_Year_Products_Smry and View2 was dbo.Rxxx_Current_Year_Products_Error_Smry so if I got that backwards then you will need to switch
#tmpTable1 TT1 LEFT OUTER JOIN
                      #tmpTable2 TT2
to
#tmpTable1 TT2 LEFT OUTER JOIN
                      #tmpTable2 TT1
--View 1
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, 
                      SUM(CASE WHEN R100_Error = 'A' THEN 1 ELSE 0 END) AS R100_Error_A, SUM(CASE WHEN R100_Error = 'E' THEN 1 ELSE 0 END) AS R100_Error_E, 
                      SUM(CASE WHEN R100_Error = 'W' THEN 1 ELSE 0 END) AS R100_Error_W, SUM(CASE WHEN R101_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R101_Error_A, SUM(CASE WHEN R101_Error = 'E' THEN 1 ELSE 0 END) AS R101_Error_E, SUM(CASE WHEN R101_Error = 'W' THEN 1 ELSE 0 END)
                       AS R101_Error_W, SUM(CASE WHEN R102_Error = 'A' THEN 1 ELSE 0 END) AS R102_Error_A, SUM(CASE WHEN R102_Error = 'E' THEN 1 ELSE 0 END)
                       AS R102_Error_E, SUM(CASE WHEN R102_Error = 'W' THEN 1 ELSE 0 END) AS R102_Error_W, 
                      SUM(CASE WHEN R200_Error = 'A' THEN 1 ELSE 0 END) AS R200_Error_A, SUM(CASE WHEN R200_Error = 'E' THEN 1 ELSE 0 END) AS R200_Error_E, 
                      SUM(CASE WHEN R200_Error = 'W' THEN 1 ELSE 0 END) AS R200_Error_W, SUM(CASE WHEN R300_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R300_Error_A, SUM(CASE WHEN R300_Error = 'E' THEN 1 ELSE 0 END) AS R300_Error_E, SUM(CASE WHEN R300_Error = 'W' THEN 1 ELSE 0 END)
                       AS R300_Error_W, SUM(CASE WHEN R301_Error = 'A' THEN 1 ELSE 0 END) AS R301_Error_A, SUM(CASE WHEN R301_Error = 'E' THEN 1 ELSE 0 END)
                       AS R301_Error_E, SUM(CASE WHEN R301_Error = 'W' THEN 1 ELSE 0 END) AS R301_Error_W, 
                      SUM(CASE WHEN R302_Error = 'A' THEN 1 ELSE 0 END) AS R302_Error_A, SUM(CASE WHEN R302_Error = 'E' THEN 1 ELSE 0 END) AS R302_Error_E, 
                      SUM(CASE WHEN R302_Error = 'W' THEN 1 ELSE 0 END) AS R302_Error_W, SUM(CASE WHEN R400_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R400_Error_A, SUM(CASE WHEN R400_Error = 'E' THEN 1 ELSE 0 END) AS R400_Error_E, SUM(CASE WHEN R400_Error = 'W' THEN 1 ELSE 0 END)
                       AS R400_Error_W, SUM(CASE WHEN R401_Error = 'A' THEN 1 ELSE 0 END) AS R401_Error_A, SUM(CASE WHEN R401_Error = 'E' THEN 1 ELSE 0 END)
                       AS R401_Error_E, SUM(CASE WHEN R401_Error = 'W' THEN 1 ELSE 0 END) AS R401_Error_W, 
                      SUM(CASE WHEN R402_Error = 'A' THEN 1 ELSE 0 END) AS R402_Error_A, SUM(CASE WHEN R402_Error = 'E' THEN 1 ELSE 0 END) AS R402_Error_E, 
                      SUM(CASE WHEN R402_Error = 'W' THEN 1 ELSE 0 END) AS R402_Error_W
INTO #tmpTable1
FROM         dbo.Rxxx_Current_Year_Products
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
 
--View 2
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, COUNT(PMG_BG_Level3) 
                      AS NPI_Counts
FROM         dbo.Rxxx_Current_Year_Products
INTO #tmpTable2
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
HAVING      (PMG_BG IS NOT NULL)
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
--Final view comprised of joining view 1 and view 2
 
SELECT     TOP (100) PERCENT TT1.PMG_BG, TT1.PMG_BG_Level0, 
                      TT1.PMG_BG_Level1, TT1.PMG_BG_Level2, 
                      TT1.PMG_BG_Level3, TT1.NPI_Counts, 
                      TT2.R100_Error_A, TT2.R100_Error_E, 
                      TT2.R100_Error_W, TT2.R101_Error_A, 
                      TT2.R101_Error_E, TT2.R101_Error_W, 
                      TT2.R102_Error_A, TT2.R102_Error_E, 
                      TT2.R102_Error_W, TT2.R200_Error_A, 
                      TT2.R200_Error_E, TT2.R200_Error_W, 
                      TT2.R300_Error_A, TT2.R300_Error_E, 
                      TT2.R300_Error_W, TT2.R301_Error_A, 
                      TT2.R301_Error_E, TT2.R301_Error_W, 
                      TT2.R302_Error_A, TT2.R302_Error_E, 
                      TT2.R302_Error_W, TT2.R400_Error_A, 
                      TT2.R400_Error_E, TT2.R400_Error_W, 
                      TT2.R401_Error_A, TT2.R401_Error_E, 
                      TT2.R401_Error_W, TT2.R402_Error_A, 
                      TT2.R402_Error_E, TT2.R402_Error_W
FROM         #tmpTable1 TT1 LEFT OUTER JOIN
                      #tmpTable2 TT2 ON 
                      TT1.PMG_BG = TT2.PMG_BG AND 
                      TT1.PMG_BG_Level0 = TT2.PMG_BG_Level0 AND 
                      TT1.PMG_BG_Level1 = TT2.PMG_BG_Level1 AND 
                      TT1.PMG_BG_Level2 = TT2.PMG_BG_Level2 AND 
                      TT1.PMG_BG_Level3 = TT2.PMG_BG_Level3
ORDER BY TT1.PMG_BG, TT1.PMG_BG_Level0, 
                      TT1.PMG_BG_Level1, TT1.PMG_BG_Level2, 
                      TT1.PMG_BG_Level3

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FairfieldAuthor Commented:
Using this I recieved the following error.


(54 row(s) affected)

(53 row(s) affected)
Msg 207, Level 16, State 1, Line 40
Invalid column name 'NPI_Counts'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R100_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R100_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R100_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R101_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R101_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R101_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R102_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R102_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R102_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R200_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R200_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R200_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R300_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R300_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R300_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R301_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R301_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R301_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R302_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R302_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R302_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R400_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R400_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R400_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R401_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R401_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R401_Error_W'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R402_Error_A'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R402_Error_E'.
Msg 207, Level 16, State 1, Line 40
Invalid column name 'R402_Error_W'.





--View 1
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, 
                      SUM(CASE WHEN R100_Error = 'A' THEN 1 ELSE 0 END) AS R100_Error_A, SUM(CASE WHEN R100_Error = 'E' THEN 1 ELSE 0 END) AS R100_Error_E, 
                      SUM(CASE WHEN R100_Error = 'W' THEN 1 ELSE 0 END) AS R100_Error_W, SUM(CASE WHEN R101_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R101_Error_A, SUM(CASE WHEN R101_Error = 'E' THEN 1 ELSE 0 END) AS R101_Error_E, SUM(CASE WHEN R101_Error = 'W' THEN 1 ELSE 0 END)
                       AS R101_Error_W, SUM(CASE WHEN R102_Error = 'A' THEN 1 ELSE 0 END) AS R102_Error_A, SUM(CASE WHEN R102_Error = 'E' THEN 1 ELSE 0 END)
                       AS R102_Error_E, SUM(CASE WHEN R102_Error = 'W' THEN 1 ELSE 0 END) AS R102_Error_W, 
                      SUM(CASE WHEN R200_Error = 'A' THEN 1 ELSE 0 END) AS R200_Error_A, SUM(CASE WHEN R200_Error = 'E' THEN 1 ELSE 0 END) AS R200_Error_E, 
                      SUM(CASE WHEN R200_Error = 'W' THEN 1 ELSE 0 END) AS R200_Error_W, SUM(CASE WHEN R300_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R300_Error_A, SUM(CASE WHEN R300_Error = 'E' THEN 1 ELSE 0 END) AS R300_Error_E, SUM(CASE WHEN R300_Error = 'W' THEN 1 ELSE 0 END)
                       AS R300_Error_W, SUM(CASE WHEN R301_Error = 'A' THEN 1 ELSE 0 END) AS R301_Error_A, SUM(CASE WHEN R301_Error = 'E' THEN 1 ELSE 0 END)
                       AS R301_Error_E, SUM(CASE WHEN R301_Error = 'W' THEN 1 ELSE 0 END) AS R301_Error_W, 
                      SUM(CASE WHEN R302_Error = 'A' THEN 1 ELSE 0 END) AS R302_Error_A, SUM(CASE WHEN R302_Error = 'E' THEN 1 ELSE 0 END) AS R302_Error_E, 
                      SUM(CASE WHEN R302_Error = 'W' THEN 1 ELSE 0 END) AS R302_Error_W, SUM(CASE WHEN R400_Error = 'A' THEN 1 ELSE 0 END) 
                      AS R400_Error_A, SUM(CASE WHEN R400_Error = 'E' THEN 1 ELSE 0 END) AS R400_Error_E, SUM(CASE WHEN R400_Error = 'W' THEN 1 ELSE 0 END)
                       AS R400_Error_W, SUM(CASE WHEN R401_Error = 'A' THEN 1 ELSE 0 END) AS R401_Error_A, SUM(CASE WHEN R401_Error = 'E' THEN 1 ELSE 0 END)
                       AS R401_Error_E, SUM(CASE WHEN R401_Error = 'W' THEN 1 ELSE 0 END) AS R401_Error_W, 
                      SUM(CASE WHEN R402_Error = 'A' THEN 1 ELSE 0 END) AS R402_Error_A, SUM(CASE WHEN R402_Error = 'E' THEN 1 ELSE 0 END) AS R402_Error_E, 
                      SUM(CASE WHEN R402_Error = 'W' THEN 1 ELSE 0 END) AS R402_Error_W
INTO #tmpTable1
FROM         dbo.Rxxx_Current_Year_Products
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
 
--View 2
 
SELECT     TOP (100) PERCENT PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3, COUNT(PMG_BG_Level3) 
                      AS NPI_Counts
INTO #tmpTable2
FROM         dbo.Rxxx_Current_Year_Products
 
GROUP BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
HAVING      (PMG_BG IS NOT NULL)
ORDER BY PMG_BG, PMG_BG_Level0, PMG_BG_Level1, PMG_BG_Level2, PMG_BG_Level3
 
--Final view comprised of joining view 1 and view 2
 
SELECT     TOP (100) PERCENT TT1.PMG_BG, TT1.PMG_BG_Level0, 
                      TT1.PMG_BG_Level1, TT1.PMG_BG_Level2, 
                      TT1.PMG_BG_Level3, TT1.NPI_Counts, 
                      TT2.R100_Error_A, TT2.R100_Error_E, 
                      TT2.R100_Error_W, TT2.R101_Error_A, 
                      TT2.R101_Error_E, TT2.R101_Error_W, 
                      TT2.R102_Error_A, TT2.R102_Error_E, 
                      TT2.R102_Error_W, TT2.R200_Error_A, 
                      TT2.R200_Error_E, TT2.R200_Error_W, 
                      TT2.R300_Error_A, TT2.R300_Error_E, 
                      TT2.R300_Error_W, TT2.R301_Error_A, 
                      TT2.R301_Error_E, TT2.R301_Error_W, 
                      TT2.R302_Error_A, TT2.R302_Error_E, 
                      TT2.R302_Error_W, TT2.R400_Error_A, 
                      TT2.R400_Error_E, TT2.R400_Error_W, 
                      TT2.R401_Error_A, TT2.R401_Error_E, 
                      TT2.R401_Error_W, TT2.R402_Error_A, 
                      TT2.R402_Error_E, TT2.R402_Error_W
FROM         #tmpTable1 TT1 LEFT OUTER JOIN
                      #tmpTable2 TT2 ON 
                      TT1.PMG_BG = TT2.PMG_BG AND 
                      TT1.PMG_BG_Level0 = TT2.PMG_BG_Level0 AND 
                      TT1.PMG_BG_Level1 = TT2.PMG_BG_Level1 AND 
                      TT1.PMG_BG_Level2 = TT2.PMG_BG_Level2 AND 
                      TT1.PMG_BG_Level3 = TT2.PMG_BG_Level3
ORDER BY TT1.PMG_BG, TT1.PMG_BG_Level0, 
                      TT1.PMG_BG_Level1, TT1.PMG_BG_Level2, 
                      TT1.PMG_BG_Level3

Open in new window

0
Chris LuttrellSenior Database ArchitectCommented:
Did I have the correct tmpTable matched to the correct view name?  I just cut and pasted your code and only added the 2 lines to create the #tmpTables and replaced the name of the views with the tmpTable name, gave them an alias and replaced the cooresponding table specification on each column with the new alias.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Anthony PerkinsCommented:
Unrelated, but using TOP 100 PERCENT and ORDER BY is pointless.  If you think the results are going to be sorted you are delluding yourself.
0
FairfieldAuthor Commented:
Is that how you select records from a temp table (TT1?)

SELECT     TOP (100) PERCENT TT1.PMG_BG, TT1.PMG_BG_Level0,
                      TT1.PMG_BG_Level1, TT1.PMG_BG_Level2,
                      TT1.PMG_BG_Level3, TT1.NPI_Counts,
                      TT2.R100_Error_A, TT2.R100_Error_E,
                      TT2.R100_Error_W, TT2.R101_Error_A,
                      TT2.R101_Error_E, TT2.R101_Error_W,
                      TT2.R102_Error_A, TT2.R102_Error_E,
                      TT2.R102_Error_W, TT2.R200_Error_A,
                      TT2.R200_Error_E, TT2.R200_Error_W,
                      TT2.R300_Error_A, TT2.R300_Error_E,
                      TT2.R300_Error_W, TT2.R301_Error_A,
                      TT2.R301_Error_E, TT2.R301_Error_W,
                      TT2.R302_Error_A, TT2.R302_Error_E,
                      TT2.R302_Error_W, TT2.R400_Error_A,
                      TT2.R400_Error_E, TT2.R400_Error_W,
                      TT2.R401_Error_A, TT2.R401_Error_E,
                      TT2.R401_Error_W, TT2.R402_Error_A,
                      TT2.R402_Error_E, TT2.R402_Error_W
FROM         #tmpTable1 TT1 LEFT OUTER JOIN
0
Chris LuttrellSenior Database ArchitectCommented:
yes that is how you select from a temp table, the #tmpTable1 is the name of the table created instead of the first view above.  I gave it the Table Alias of TT1 to use in place of the view name where you qualified the column names in the query because it does not like to use the # symbol in the rest of the query unless you wrapped all of them with the [] like [#tmpTable1].PG_BG_LEVEL0, TT1 just is easlier to use.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.