SQL Server: select records with same payment id, but different payment type

Question

I need to select only the records that have same payment id but different payment types. In the output example down below, rows 1 and 2 have the same payment id, and same payment type. I need to exclude those, and only return rows like 3, 4 and 5.

transID     transType   createdDate             paymentAmount_rtp   paymentAmount_tenders   DesiredResult   paymentDate             payment_Id  batchId TenderType  PaymentType
40064259    Payment     2019-12-16 09:07:16.160 286.30              -13.70                  0               2019-12-17 00:00:00.000 1072405     127737  2           CASH      
40064259    Payment     2019-12-16 09:07:16.160 286.30              300.00                  0               2019-12-17 00:00:00.000 1072405     127737  2           CASH      
40064261    Payment     2019-12-16 12:43:10.700 3958.91             -41.09                  0               2019-12-17 00:00:00.000 1072667     127737  2           CASH      
40064261    Payment     2019-12-16 12:43:10.700 3958.91             1600.00                 0               2019-12-17 00:00:00.000 1072667     127737  2           CASH      
40064261    Payment     2019-12-16 12:43:10.700 3958.91             2400.00                 0               2019-12-17 00:00:00.000 1072667     127737  1           CHECK     

This is the query I am using, it might not be the best written query. Any help is much appreciated:

SELECT 
    transID
,   transType
,   createdDate
,   paymentAmount_rtp
,   paymentAmount_tenders
,   DesiredResult
,   paymentDate
,   payment_Id
,   batchId
,   TenderType
,   PaymentType 
FROM
(
    SELECT DISTINCT
        CAST(RTP.ExternalId AS CHAR(25)) AS 'transID'
    ,   CASE RTP.Activity 
            WHEN 0 THEN 'Payment' 
            WHEN 1 THEN 'Void' 
        END AS 'transType'
    ,   RTP.TransactionCreateDate AS 'createdDate'
    ,   RTP.Amount AS 'paymentAmount_rtp'
    ,   T.Amount AS 'paymentAmount_tenders'
    ,   CASE 
            WHEN RTP.Amount = T.Amount 
            THEN '1' 
            ELSE '0' 
        END AS DesiredResult
    ,   RTP.EffectiveDate AS 'paymentDate'
    ,   CAST(RTP.Id AS NVARCHAR(50)) AS 'payment_Id'
    ,   RTP.TransactionBatchId AS 'batchId'
    ,   T.TenderTypeId AS 'TenderType'
    ,   CAST(TType.Name AS CHAR(10)) AS 'PaymentType'
    FROM 
        Reporting.TransactionPayments RTP
    INNER JOIN dbo.Tenders T
        ON T.TransactionId = RTP.TransactionId
    INNER JOIN dbo.TenderTypes TType
        ON TType.Id = T.TenderTypeId
    WHERE 
        CAST(RTP.TransactionCreateDate AS DATE) >= '12/01/2019'     
) m
WHERE payment_Id IN 
(
    SELECT 
        n.payment_Id
    FROM 
    (
        SELECT DISTINCT
            CAST(RTP.ExternalId AS CHAR(25)) AS 'transID'
        ,   CASE RTP.Activity 
                WHEN 0 THEN 'Payment' 
                WHEN 1 THEN 'Void' 
                END AS 'transType'
        ,   RTP.TransactionCreateDate AS 'createdDate'
        ,   RTP.Amount AS 'paymentAmount_rtp'
        ,   T.Amount AS 'paymentAmount_tenders'
        ,   CASE 
                WHEN RTP.Amount = T.Amount 
                THEN '1' 
                ELSE '0' 
            END AS DesiredResult
        ,   RTP.EffectiveDate AS 'paymentDate'
        ,   CAST(RTP.Id AS NVARCHAR(50)) AS 'payment_Id'
        ,   RTP.TransactionBatchId AS 'batchId'
        ,   T.TenderTypeId AS 'TenderType'
        ,   CAST(TType.Name AS CHAR(10)) AS 'PaymentType'
        FROM 
            Reporting.TransactionPayments RTP
        INNER JOIN dbo.Tenders T
            ON T.TransactionId = RTP.TransactionId
        INNER JOIN dbo.TenderTypes TType
            ON TType.Id = T.TenderTypeId
        WHERE 
            CAST(RTP.TransactionCreateDate AS DATE) >= '12/01/2019'
    ) n
    GROUP BY 
        n.payment_Id
    HAVING COUNT(*) > 1
)

UPDATE

If you look at the screen shot, the rows in red, those are the ones I want to exclude, these are same payment id, same payment type "CASH".

The rows in green are the type of records I need to find, same payment id, 3 payments, 2 "CASH", and 1 "CHECK".

I tried the code that was shared, but the results remove the row that has a payment type of "CHECK" leaving everything else there.

Answer 1

You can select transactions with same payment id but different payment type by next query:

SELECT payment_Id FROM payments GROUP BY payment_Id HAVING COUNT(DISTINCT paymentType) > 1;

So your final query may be next:

SELECT 
    transID,   
    transType,   
    createdDate,   
    paymentAmount_rtp,   
    paymentAmount_tenders,   
    DesiredResult,   
    paymentDate,   
    payment_Id,   
    batchId,   
    TenderType,
   PaymentType 
FROM payments 
JOIN (
    SELECT payment_Id 
    FROM payments 
    GROUP BY payment_Id 
    HAVING COUNT(DISTINCT paymentType) > 1
) dp on dp.payment_Id = payments.payment_Id;