September 18, 2019 at 4:43 pm
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
September 18, 2019 at 6:33 pm
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]
September 18, 2019 at 6:43 pm
;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;
September 18, 2019 at 7:34 pm
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