Joining Two Temp Tables to Main Table 1

[larrydavid]

Hello,

I have a query in a stored proc using a CTE that I want to join with two other queries (Test1 and Test2 in comments below):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[sp_Test] 
(
	@BeginDate	     DATETIME,
	@EndDate		   DATETIME,
	@VendorFileName	VARCHAR(32)
)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT	[Source Name], 
			  [Vendor File Name], 
			  [RecordCount] 
	FROM  
	(
		SELECT	  count(*) AS [RecordCount],
					st.[Source Name],
					a.[Vendor File Name]
		FROM		tbl_AllComp a INNER JOIN tbl_Source_Type st ON a.ID = st.ID
		GROUP BY	st.[Source Name], a.[Vendor File Name]
	) x
	WHERE		[Vendor File Load Date] BETWEEN @BeginDate And @EndDate
	GROUP BY	[Source Name],[Vendor File Name],[RecordCount] 
	HAVING	  [Source Name] Like '%' + @VendorFileName + '%'
	ORDER BY	[Vendor File Load Date] 
/*
	-- *****Test1

	SELECT		r.VENDOR_ID, 
				[Vendor File Name] =  v.Vendor_Name,
				[Vendor File Load Date] = CONVERT(VARCHAR(10), LOAD_DATE, 101), 
				Count(r.SUB_ID) AS [No of Test1 Rejects]
	FROM		dbo.TEST1_SUBMISSION_REJECTS r INNER JOIN VENDOR v ON 
				r.VENDOR_ID = v.VENDOR_ID INNER JOIN 
				BATCH_TYPE b ON r.BATCH_TYPE_ID = b.BATCH_TYPE_ID
	GROUP BY	r.VENDOR_ID, 
				v.Vendor_Name,
				b.BATCH_TYPE_NAME,
				r.LOAD_DATE, 
				CONVERT(VARCHAR(10), LOAD_DATE, 101) 
	HAVING	  CONVERT(VARCHAR(10), LOAD_DATE, 101) Between @BeginDate And @EndDate 
				AND Vendor_Name LIKE @VendorFileName 

	-- *****Test2

	SELECT		r.VENDOR_ID, 
				[Vendor File Name] = v.Vendor_Name,
				[Vendor File Load Date] = CONVERT(VARCHAR(10), [Test_Load_Date], 101), 
				Count(r.VENDOR_ID) AS [No of Test2 Rejects]
	FROM		dbo.Test_Load_Results r INNER JOIN VENDOR v ON 
				r.VENDOR_ID = v.VENDOR_ID INNER JOIN 
				BATCH_TYPE b ON r.BATCH_TYPE_ID = b.BATCH_TYPE_ID
	WHERE		r.Load_Status <> 1
	GROUP BY	r.VENDOR_ID, 
				v.Vendor_Name,
				b.BATCH_TYPE_NAME,
				r.[Test_Load_Date], 
				CONVERT(VARCHAR(10), [Test_Load_Date], 101) 
	HAVING	  CONVERT(VARCHAR(10), [Test_Load_Date], 101) Between @BeginDate And @EndDate 
				AND Vendor_Name LIKE @VendorFileName 
*/
END

If someone could show me how to use temp tables to join in these tables I would really appreciate the help.

Thanks,
Larry

 

[gmmastros]

I have some comments on your code that is likely to improve performance but I will reserve those comments unless you are interested in hearing them.

First... the code you show is not using a CTE. The technique of querying a query (the uncommented part) is called a derived table. Other than syntax, there isn't usually any difference between a derived table and a common table expression. There are times (depending on the query) where you may experience a difference in performance, but the query has to be pretty complex for that. I mention that this is a derived table because you may want to do a google search on this terminology if you don't understand how to use them.

I suggest you try this:

ALTER PROCEDURE [dbo].[sp_Test] 
(
    @BeginDate         DATETIME,
    @EndDate           DATETIME,
    @VendorFileName    VARCHAR(32)
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT    [Source Name], 
              [Vendor File Name], 
              [RecordCount] 
    FROM  
    (
        SELECT      count(*) AS [RecordCount],
                    st.[Source Name],
                    a.[Vendor File Name]
        FROM        tbl_AllComp a INNER JOIN tbl_Source_Type st ON a.ID = st.ID
        GROUP BY    st.[Source Name], a.[Vendor File Name]
    ) x
    INNER JOIN (
      SELECT        r.VENDOR_ID, 
                  [Vendor File Name] =  v.Vendor_Name,
                  [Vendor File Load Date] = CONVERT(VARCHAR(10), LOAD_DATE, 101), 
                  Count(r.SUB_ID) AS [No of Test1 Rejects]
      FROM        dbo.TEST1_SUBMISSION_REJECTS r INNER JOIN VENDOR v ON 
                  r.VENDOR_ID = v.VENDOR_ID INNER JOIN 
                  BATCH_TYPE b ON r.BATCH_TYPE_ID = b.BATCH_TYPE_ID
      GROUP BY    r.VENDOR_ID, 
                  v.Vendor_Name,
                  b.BATCH_TYPE_NAME,
                  r.LOAD_DATE, 
                  CONVERT(VARCHAR(10), LOAD_DATE, 101) 
      HAVING      CONVERT(VARCHAR(10), LOAD_DATE, 101) Between @BeginDate And @EndDate 
                  AND Vendor_Name LIKE @VendorFileName 
      ) As Test1RejectCount
      On x.[Vendor File Name] = RejectCount.[Vendor File Name]
    INNER JOIN (
      SELECT        r.VENDOR_ID, 
                  [Vendor File Name] = v.Vendor_Name,
                  [Vendor File Load Date] = CONVERT(VARCHAR(10), [Test_Load_Date], 101), 
                  Count(r.VENDOR_ID) AS [No of Test2 Rejects]
      FROM        dbo.Test_Load_Results r INNER JOIN VENDOR v ON 
                  r.VENDOR_ID = v.VENDOR_ID INNER JOIN 
                  BATCH_TYPE b ON r.BATCH_TYPE_ID = b.BATCH_TYPE_ID
      WHERE        r.Load_Status <> 1
      GROUP BY    r.VENDOR_ID, 
                  v.Vendor_Name,
                  b.BATCH_TYPE_NAME,
                  r.[Test_Load_Date], 
                  CONVERT(VARCHAR(10), [Test_Load_Date], 101) 
      HAVING      CONVERT(VARCHAR(10), [Test_Load_Date], 101) Between @BeginDate And @EndDate 
                  AND Vendor_Name LIKE @VendorFileName 
      ) As Test2RejectCount
      On x.[Vendor File Name] = Test2RejectCount.[Vendor File Name]    
    WHERE        [Vendor File Load Date] BETWEEN @BeginDate And @EndDate
                 AND [Source Name] Like '%' + @VendorFileName + '%'
    ORDER BY    [Vendor File Load Date]

I haven't tested this code, but it should work well for you. I assume you wanted to join the queries on Vendor File Name since that appears to be the only common column between the columns.






-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

In addition to George's comments I just wanted to point out that you should use anything else besides sp_ prefix for your SP name. sp_ prefix is reserved for system stored procedures and it's very bad practice to start custom stored procedures with this prefix.

PluralSight Learning Library

 

[larrydavid]

LOL, Here I thought I was getting to be a seasoned pro like you guys and I still get taken to school, but it's good and why I learn so much from you guys over the years. Thank you both very much for taking time to help and your comments.

Larry