CODEWITHSUNDEEP
sql-servert-sql
Harry Reems
Harry Reems

Reputation: 3

Duplicate values in T-SQL

I have searched the issue I am having but I can't seem to find a solution that will work for what I need. I was able to come up with the code below but it seems to not work correctly.I get the duplicate matches that I am looking for but it also returns a lot of single matches. The phone column represents phone numbers and the reason column is the "reason" for the call. I am trying to find where a phone number appears more than once and the reason for the calls.

Columns I am using:

Code:

SELECT 
    phone, reason, columndate 
FROM 
    atable
WHERE
    phone IN (SELECT phone  
              FROM atable
              WHERE columndate > '2017-10-01 00:00:00.000'
                AND columndate < '2018-01-01 00:00:00.000'
              GROUP BY phone  
              HAVING COUNT(*) >= 2)  
    AND department LIKE 'accounting'

Upvotes: 0

Views: 48

Answers (1)

GarethD
GarethD

Reputation: 69749

The single matches are likely to be where the department is different, with this very simple example:

Phone       Reason      Department      ColumnDate
----------------------------------------------------------
000000000   A Reason    Credit Control  2017-12-29 16:29
000000000   A Reason    Accounting      2017-12-29 16:29

Your subquery will return 000000000 since there are 2 rows between 2017-10-01 AND 2018-01-01

SELECT phone  
  FROM atable
  where columndate > '2017-10-01 00:00:00.000'
    and columndate < '2018-01-01 00:00:00.000'
  GROUP BY phone  
  HAVING count(*) >= 2

So your query is then essentially:

SELECT phone, reason, columndate 
FROM atable
WHERE Phone IN ('000000000')
and department like 'accounting'

Which will only return one row.

You also run the risk of returning additional records outside of your original date range, since you have no date filter on the outer query.

I think you can do this all in one query with windowed functions:

SELECT  Phone, Reason, ColumnDate
FROM    (   SELECT  phone, 
                    reason, 
                    columndate,
                    Calls = COUNT(*) OVER(PARTITION BY phone)
            FROM    aTable
            WHERE   columndate > '2017-10-01 00:00:00.000'
            AND     columndate < '2018-01-01 00:00:00.000'
            AND     Department LIKE 'accounting' 
        ) AS t
WHERE   t.Calls >= 2;

Upvotes: 2

Related Questions