CODEWITHSUNDEEP
sqlsql-servert-sql
David
David

Reputation: 1293

TSQL - Exact match on subset of records

I have the following table structure and data

TransID   TransType  Product  Qty  OrderRef  Date
-------   ---------  -------  ---  --------  ----
C123      Credit     Prod1    1     Order8   2014-07-08
C123      Credit     Prod2    5     Order8   2014-07-08
Inv111    Invoice    Prod1    1     Order8   2014-07-08
Inv111    Invoice    Prod2    5     Order8   2014-07-08
C999      Credit     Prod1    6     Order8   2014-07-08
C999      Credit     Prod2    9     Order8   2014-07-08
Inv666    Invoice    Prod1    6     Order8   2014-07-08

What I want to do is to be able to identify those Credit records that have an exact matching group of Invoice records. By exact matching I mean the same Product, OrderRef, Qty and Date

In the above data C123 would match with Inv111 but C999 would not match with Inv666 as Inv666 is missing a row

I want to delete both the Credit and Invoice records that have an exact match. There is no link between Invoice and Credits apart from the OrderRef

I've played around with the Except statement, something like this:-

    ;with CreditToInvoice(Product, Qty, OrderRef, Date)
as
    (select Product
                    ,Qty
                    ,OrderRef
                    ,Date)
        from @t t1
        where t1.TransType = 'Credit'
        group by TransactionID, OrderRef, Product, Date, Qty

        EXCEPT

        select Product
                      ,Qty
                      ,OrderRef
                      ,Date)
        from @t t2
        where t2.TransType = 'Invoice'
        group by TransactionID, OrderRef, Product, Date, Qty
    )

which gives me everything in table a not in table b as I would expect

The problem is I really need the TransactionID's so that I can proceed to delete correctly

Is the Except the wrong statement for this? Could I use a merge?

Upvotes: 0

Views: 356

Answers (4)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

The resulting TransIDs should be the ones you need to delete

DECLARE  @Trans TABLE
    ([TransID] varchar(6), [TransType] varchar(7), [Product] varchar(5), [Qty] int, [OrderRef] varchar(6), [Date] datetime)
;

INSERT INTO @Trans
    ([TransID], [TransType], [Product], [Qty], [OrderRef], [Date])
VALUES
    ('C123', 'Credit', 'Prod1', 1, 'Order8', '2014-07-08 00:00:00'),
    ('C123', 'Credit', 'Prod2', 5, 'Order8', '2014-07-08 00:00:00'),
    ('Inv111', 'Invoice', 'Prod1', 1, 'Order8', '2014-07-08 00:00:00'),
    ('Inv111', 'Invoice', 'Prod2', 5, 'Order8', '2014-07-08 00:00:00'),
    ('C999', 'Credit', 'Prod1', 6, 'Order8', '2014-07-08 00:00:00'),
    ('C999', 'Credit', 'Prod2', 9, 'Order8', '2014-07-08 00:00:00'),
    ('Inv666', 'Invoice', 'Prod1', 6, 'Order8', '2014-07-08 00:00:00')
;

DECLARE  @TransUnique TABLE
    ([TransID] varchar(6)
)

INSERT INTO @TransUnique
    SELECT DISTINCT TransID FROM @Trans

--Remove Credits
DELETE t
FROM @TransUnique t
INNER JOIN (
    select t1.*,t2.TransID [TransId2],t2.TransType [TransType2]
    From @Trans t1
    LEFT JOIN  @Trans t2 ON t1.OrderRef=t2.OrderRef
        AND t1.Date=t2.Date
        AND t1.Qty=t2.Qty
        AND t1.Product=t2.Product
        AND t2.TransType='Invoice'
    WHERE t1.TransType='Credit'
) joined ON t.TransID=joined.TransId AND joined.TransId2 IS NULL

--Remove Invoices
DELETE t
FROM @TransUnique t
INNER JOIN (
    select t1.*,t2.TransID [TransId2],t2.TransType [TransType2]
    From @Trans t1
    LEFT JOIN  @Trans t2 ON t1.OrderRef=t2.OrderRef
        AND t1.Date=t2.Date
        AND t1.Qty=t2.Qty
        AND t1.Product=t2.Product
        AND t2.TransType='Invoice'
    LEFT JOIN @TransUnique tu ON tu.TransID=t1.TransID
    WHERE t1.TransType='Credit'
    AND tu.TransID IS NULL
) joined ON t.TransID=joined.TransId2

SELECT * FROM @TransUnique

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239656

I think a LEFT JOIN and some GROUPing is the most obvious way to deal with this requirement:

SELECT
    cr.TransID,
    MAX(inv.TransID) as InvoiceID,
    MAX(CASE WHEN inv.TransID is NULL THEN 1 ELSE 0 END) as Unsatsified
FROM
    @t cr
       left join
    @t inv
       on
           cr.Product = inv.Product and
           cr.OrderRef = inv.OrderRef and
           cr.Qty = inv.Qty and
           cr.Date = inv.Date and
           inv.TransType = 'Invoice'
WHERE
    cr.TransType = 'Credit'
GROUP BY
    cr.TransID
HAVING
    MAX(CASE WHEN inv.TransID is NULL THEN 1 ELSE 0 END) = 0

That is, we join together all of the matching rows between a credit and an invoice, and then we only select this result if all credit rows achieved a match.

You can place this in a subquery or CTE and perform an unpivot if you need both TransID values in a single column for the next part of your processing.

Upvotes: 1

Azar
Azar

Reputation: 1867

Try this in order to get transid

Select TransId
From @t t1
join @t t2 
on t1.transtype = 'Credit' and t2.transtype = 'Invoice' 
and  t1.product=t2.product and t1.qty = t2.qty 
and t1.orderef=t2.orderref and t1.date = t2.date

Upvotes: 0

Bob
Bob

Reputation: 1055

If I am reading this correctly, something like this should work.

select TransID, TransType, Product, Qty, OrderRef, Date from @t t1
where t1.TransType = 'Credit'
and exists (
    select 1 from @t t2
    where t2.TransType = 'Invoice'
    and t2.Product = t1.Product
    and t2.Qty = t1.Qty
    and t2.OrderRef = t1.OrderRef
    and t2.Date = t1.Date
)

Upvotes: 0

Related Questions