Getting a result set from temp tables

  • So I have 5 queries . Each query fills a different temp table.  For each table the first two columns will be named (GovState, FirmID) . A third column will also exist. It will be named differently depending on the temp table. ("CountOfPolicies", "CountOfQuotes", etc)  This column will normally show a count of something.

    I want to write a query to show the results of all 5 temp tables. I am thinking my result set should have 7 columns.(GovState, FirmID, plus the 5 other aggregate (Count) colums  How do I do it?

    I am thinking I want to join on GovState and FirmID. However, if there is no match in say the second table (between GovState and FirmID) I obviously still want the GovState and FirmID filled out with the relative GovState and FirmID from the second table. Any advice or direction would be most appreciated.

    --Jason

  • i slapped together an example, but the logic is this:

    join the tables together and use a group by. getting the max() of each column so you get the desired look of the data.

    CREATE TABLE #t1(GovState VARCHAR(30), FirmID VARCHAR(30),CountOfPolicies int)
    CREATE TABLE #t2(GovState VARCHAR(30), FirmID VARCHAR(30),CountOfQuotes int)
    CREATE TABLE #t3(GovState VARCHAR(30), FirmID VARCHAR(30),CountOfEmployees int)
    CREATE TABLE #t4(GovState VARCHAR(30), FirmID VARCHAR(30),DirectorName VARCHAR(30))
    INSERT INTO #t1 SELECT 'FL','DOE',67
    INSERT INTO #t2 SELECT 'FL','DOE',42
    INSERT INTO #t3 SELECT 'FL','DOE',4250
    INSERT INTO #t4 SELECT 'FL','DOE','Lowell'

    SELECT
    [#t1].GovState,
    [#t1].[FirmID],
    MAX(#t1.CountOfPolicies) AS CountOfPolicies ,
    MAX(#t2.CountOfQuotes) AS CountOfQuotes,
    MAX(#t3.CountOfEmployees) AS CountOfEmployees ,
    MAX(#t4.DirectorName) AS DirectorName
    FROM [#t1]
    INNER JOIN #t2 ON #t1.GovState = #t1.GovState AND #t1.FirmID = #t2.FirmID
    INNER JOIN #t3 ON #t1.GovState = #t1.GovState AND #t1.FirmID = #t3.FirmID
    INNER JOIN #t4 ON #t1.GovState = #t1.GovState AND #t1.FirmID = #t4.FirmID
    GROUP BY
    [#t1].GovState,
    [#t1].[FirmID]
  • ;WITH KeyCTE
    AS (SELECT GovState, FirmID FROM #t1
    UNION
    SELECT GovState, FirmID FROM #t2
    UNION
    SELECT GovState, FirmID FROM #t3
    UNION
    SELECT GovState, FirmID FROM #t4
    UNION
    SELECT GovState, FirmID FROM #t5)

    SELECT KeyCTE.GovState,
    KeyCTE.FirmID,
    ISNULL(#t1.count1, 0) AS count1,
    ISNULL(#t2.count2, 0) AS count2,
    ISNULL(#t3.count3, 0) AS count3,
    ISNULL(#t4.count4, 0) AS count4,
    ISNULL(#t5.count5, 0) AS count5
    FROM KeyCTE
    LEFT OUTER JOIN #t1 ON #t1.GovState = KeyCTE.GovState AND #t1.FirmID = KeyCTE.FirmID
    LEFT OUTER JOIN #t2 ON #t2.GovState = KeyCTE.GovState AND #t2.FirmID = KeyCTE.FirmID
    LEFT OUTER JOIN #t3 ON #t3.GovState = KeyCTE.GovState AND #t3.FirmID = KeyCTE.FirmID
    LEFT OUTER JOIN #t4 ON #t4.GovState = KeyCTE.GovState AND #t4.FirmID = KeyCTE.FirmID
    LEFT OUTER JOIN #t5 ON #t5.GovState = KeyCTE.GovState AND #t5.FirmID = KeyCTE.FirmID;

    • This reply was modified 2 years, 8 months ago by  tjolliffe.
  • tjolliffe's approach requires two scans of each of the tables.  The following only uses one scan of each of the tables, but you may be better off just skipping the temp tables altogether and using the original tables in the CTE.

    WITH temp_tables AS
    (
    SELECT t.GovState, t.FirmID, t.CountOfPolicies, 0 AS CountOfQuotes, 0 AS CountOfEmployees, NULL AS DirectorName
    FROM #t1 AS t
    UNION ALL
    SELECT t.GovState, t.FirmID, 0, t.CountOfQuotes, 0, NULL
    FROM #t2 AS t
    UNION ALL
    SELECT t.GovState, t.FirmID, 0, 0, t.CountOfEmployees, NULL
    FROM #t3 AS t
    UNION ALL
    SELECT t.GovState, t.FirmID, 0, 0, 0, t.DirectorName
    FROM #t4 AS t
    )
    SELECT tt.GovState, tt.FirmID, SUM(tt.CountOfPolicies) AS CountOfPolicies, SUM(tt.CountOfQuotes) AS CountOfQuotes, SUM(tt.CountOfEmployees) AS CountOfEmployees, COUNT(tt.DirectorName) AS CountOfDirectors
    FROM temp_tables AS tt
    GROUP BY tt.GovState, tt.FirmID
    ORDER BY tt.GovState, tt.FirmID;

    Drew

    PS: I used BobbyTables' data even though you said that they all contained counts, but their fourth table contains text instead.

    PPS: Yes, I used a form of singular "they", because I didn't want to assume a specific gender for BobbyTables.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply