SQL compare phone numbers from previous quarters-CodePudding

I am trying to compare phone numbers from 2 different quarters. What I need to do is compare phone numbers from the reporting quarter (previous quarter) to the quarter prior. We are looking to return only new clients so we want SQL to return the data where phone numbers from the reporting quarter do NOT match any numbers from the previous quarter. We are using SQL Server. Current data ex:

Year|Quarter|PhoneNumber
-------------------------
2022|   2   | 1111111111
2022|   2   | 2222222222
2022|   2   | 3333333333
2022|   1   | 2222222222

What we want returned:
Year|Quarter|PhoneNumber
------------------------
2022|   2   | 1111111111
2022|   2   | 3333333333

So just the numbers that are in quarter 2 but not in quarter 1 needs to be returned in the above example.

Any guidance here would be greatly appreciated.

CodePudding user response：

A minimal reproducible example is not provided. So I am shooting from the hip.

Please try the following solution. You can use it as a conceptual example.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, Year INT, QUARTER INT, PhoneNumber VARCHAR(20));
INSERT @tbl (Year, QUARTER, PhoneNumber) VALUES
(2022, 2, '1111111111'),
(2022, 2, '2222222222'),
(2022, 2, '3333333333'),
(2022, 1, '2222222222');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT PhoneNumber FROM @tbl
    WHERE QUARTER = 2
    EXCEPT
    SELECT PhoneNumber FROM @tbl
    WHERE QUARTER = 1
)
SELECT t.* 
FROM @tbl AS t
INNER JOIN rs ON t.PhoneNumber = rs.PhoneNumber;

Output

 ---- ------ --------- ------------- 
| ID | Year | QUARTER | PhoneNumber |
 ---- ------ --------- ------------- 
|  1 | 2022 |       2 |  1111111111 |
|  3 | 2022 |       2 |  3333333333 |
 ---- ------ --------- -------------