SQL Server - Select Rows in Derived Table that have count > 1 of value in one column

Question

Help please. I have the following Select query and want to select only the rows where the column A.Name repeats more than 1 time:

SELECT
    A.Payer,
    A.PaymentDate,
    A.Name
FROM 
    (SELECT
         T.InstitutionRoleXrefLongName AS 'Payer',
         T.PaymentDate AS 'PaymentDate',
         T.FullName AS 'Name'
     FROM 
         Transfer T
     UNION ALL
     SELECT
         T.InstitutionRoleXrefLongName AS 'Payer',
         T.PaymentDate AS 'PaymentDate',
         T.FullName AS 'Name'
     FROM 
         TransferClosed T) A
WHERE 
    PaymentDate BETWEEN '20180101' AND '20180331 23:59:59'

Answer 1

using CTE and self join to get the count which greater than 1.

You can try this.

 ;with CTE AS (
    SELECT
        A.Payer,
        A.PaymentDate,
        A.Name
    FROM (
        SELECT
            T.InstitutionRoleXrefLongName AS 'Payer',
            T.PaymentDate AS 'PaymentDate',
            T.FullName AS 'Name'
        FROM Transfer T
    UNION ALL
        SELECT
            T.InstitutionRoleXrefLongName AS 'Payer',
            T.PaymentDate AS 'PaymentDate',
            T.FullName AS 'Name'
        FROM TransferClosed T
    ) A
    WHERE PaymentDate Between '20180101' AND '20180331 23:59:59'
)
select t2.*
from (
    SELECT name,count(1) totle 
    FROM CTE
    GROUP BY Name
) t1 inner join CTE t2 
ON t1.totle > 1 and t1.Name = t2.Name

The sqlfiddle CTE mock your result set

sqlfiddle:http://sqlfiddle.com/#!18/cc68f/9