combine two interval tables into single result set

Question

Post reply

combine two interval tables into single result set

rbroersma

SSC Enthusiast

Points: 175
More actions
March 5, 2021 at 5:45 am
Go to Answer
#3853644

Hello,
Looking for help combining two interval tables together. The tables are pretty simple...
Table 1:
Table 2:
I'd like to create a result having 4 columns -> PhoneNbr, Interval, CountA, CountB. I'd like to join the tables on PhoneNbr and Interval if there is a match so that we get both counts (CountA and CountB) in the same row on a PhoneNbr/Interval match, but each table may or may not have the same PhoneNbr's and Intervals.
I've been trying to use full outer joins to do this but my results are just not working. It seem like it should be a simple thing, but I'm not a SQL expert and I'm hoping someone here will be able to help.
Appreciate your help and input.

DesNorton

SSC-Insane

Points: 24495

March 5, 2021 at 6:50 am

Answer

#3853672

This should do the trick

/* First get all of the records from Table1, and the Table2 values if they exist */
SELECT    T1.PhoneNbr, T1.Interval, T1.CountA, T2.CountB
FROM      Table1 AS T1
LEFT JOIN Table2 AS T2
       ON T1.PhoneNbr = T2.PhoneNbr
      AND T1.Interval = T2.Interval
/* Now add all of the records from Table2 that do not exist in Table1 */
UNION ALL
SELECT    T2.PhoneNbr, T2.Interval, NULL AS CountA, T2.CountB
FROM      Table2 AS T2
WHERE NOT EXISTS (SELECT 1 FROM Table1 AS T1
                  WHERE T1.PhoneNbr = T2.PhoneNbr
                    AND T1.Interval = T2.Interval
                  );

How to post data/code on a forum to get the best help.

Viewing 6 posts - 1 through 5 (of 5 total)

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

rbroersma SSC Enthusiast Points: 175 More actions · Answer 1

Forgot to mention... I need all the records from both tables, but I'd like them combined into a single row IF there is a match on PhoneNbr and Interval. Thanks.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

Yes, this is the exact usage case for a FULL OUTER JOIN:

SELECT 
    COALESCE(t1.PhoneNbr, t2.PhoneNbr) AS PhoneNbr,
    COALESCE(t1.Interval, t2.Interval) AS Interval,
    t1.CountA, t2.CountB
FROM [Table 1] t1
FULL OUTER JOIN [Table 2] t2 ON t2.PhoneNbr = T1.PhoneNbr AND
    t2.Interval = t1.Interval
ORDER BY 1, 2

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

rbroersma SSC Enthusiast Points: 175 More actions · Answer 3

Thanks for the response. This worked perfectly. I was a little concerned about performance when I expanded the date range, but the query ran fast even when evaluating tens of thousands of rows in each of the tables (not that this would necessarily be practical, but wanted to do some validation).

Thanks again!

rbroersma SSC Enthusiast Points: 175 More actions · Answer 4

@scottpletcher - Thanks for the response. I'll try this solution as well when I get a chance. I wasn't familiar w/ the COALESCE function so I really appreciate your input and expanding my SQL knowledge.

combine two interval tables into single result set

Cookies on SQLServerCentral