SQL Server – Exclude Rows Based on Conditions of Another Row

querysql server

My data set is quite simple;

Ref          Value      Voucher No
------------ ---------- ----------
M019922      -154.80    100081968
M019922A     154.80     100081968
M019922B     -154.80    100081991

These represent three lines in a sales ledger. The ref without a suffix is the original transaction. The ref with the A suffix is a reversal of the original transaction (it doesn't matter why, just that it was reversed). The ref with suffix B is the new, correct transaction.

So I have three scenarios to cover, and for simplicity I would like to use just one query, but if that's not possible so be it

Scenario 1

There is only a single transaction with no suffix. This means it's just an invoice we have processed, so I want to return the first row.

Scenario 2

There are two rows, one with no suffix and one with an A suffix. In this case I am not interested in either row, as we have just reversed the transaction.

Scenario 3

As above, I have three rows. In this case, I am only interested in the row with the B suffix.

There must be something I can do using the Voucher No, excluding the duplicates, bit I can't see what the answer is.

I don't think this is possible, but I am happy to be proved wrong.

Before you ask, I have interrogated the rest of the database to see if I can find a flag that tells me a transaction has been reversed out. This is a very old system……(I have the original invoice from 1984)

Best Answer

Given your requirements, this should work for you, I think. If you have another table with just the vouchers in it, that could help it along, as well as an index on the VoucherNo column.

DECLARE @Sample TABLE
    (
    Ref VARCHAR(10) NOT NULL
    , Amount DECIMAL(10,2) NOT NULL
    , VoucherNo INT NOT NULL
    )

INSERT INTO @Sample
(Ref, Amount, VoucherNo)
VALUES ('M019922', -154.80, 100081968)
    , ('M019922A', 154.80, 100081968)
    , ('M019922B', -154.80, 100081991)

;WITH CTE_Voucher AS
    (
    SELECT S.VoucherNo
        , SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) AS TotalAmount
    FROM @Sample AS S
    GROUP BY S.VoucherNo
    HAVING SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) <> CONVERT(DECIMAL(10,2), 0.0)
    )
SELECT C.VoucherNo
    , S.Ref
    , S.Amount
FROM CTE_Voucher AS C
    INNER JOIN @Sample AS S ON S.VoucherNo = C.VoucherNo

Related Solutions

Sql-server – Select multiple rows based on date interval from two fields

Do you have a calendar table ? I am building a kind of calendar table with object cteSeries. Then adjust the Initial_Date and Final_Date to hold BOM (beginning of Month YYYY-MM-01) in cteSample , fields: Initial_Date_M,Final_Date_M

DECLARE @tSample TABLE
(
    Product VARCHAR(10)
    ,Price  DECIMAL(18,2)
    ,Initial_Date DATE
    ,Final_Date DATE)
INSERT INTO  @tSample (Product,price,initial_date,final_date)
VALUES  ('A',20.50,'2014-08-10','2014-10-01')
    ,('B',50,'2015-01-15','2015-02-20');

DECLARE
@dStart AS DATE  ='20140101'
,@dEnd AS DATE ='20160101'

-- building a calendar table  - 
;WITH cteSeries  
AS
(SELECT @dStart AS cteDate
    UNION ALL
 SELECT DATEADD(MONTH,1,cteDate)
 FROM cteSeries
 WHERE
    cteDate < @dEnd
 )


--SELECT    * FROM  cteSeries OPTION (MAXRECURSION 0)

, cteSample
AS
(SELECT Product ,Price ,Initial_Date ,Final_Date 
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Initial_Date), 0) AS  Initial_Date_M
        , DATEADD(MONTH , DATEDIFF(MONTH , 0,Final_Date), 0) Final_Date_M
    FROM @tSample
)

SELECT
  S.Product
  ,S.Price
  ,cteDate AS Date
  --,*
FROM
   cteSample AS S
   INNER JOIN cteSeries AS C
   ON S.Initial_Date_M<=C.cteDate
   AND S.Final_Date_M >= C.cteDate

and the output :

 Product    Price   Date
 A  20.50   2014-08-01
 A  20.50   2014-09-01
 A  20.50   2014-10-01
 B  50.00   2015-01-01
 B  50.00   2015-02-01

SQL Server – How to Exclude Rows from SELECT Query

I think it can be expressed a little more succinctly:

;WITH x AS 
(
  SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER() 
    OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
  FROM dbo.MassiveTable
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE Status = 'Executed' AND rn = 1
AND NOT EXISTS 
(
  SELECT 1 FROM x AS t
  WHERE t.Tid = x.Tid 
    AND t.Bid = x.Bid
    AND t.[Action] = 'Remove'
);

Note that this assumes that a Remove value anywhere in the timeline makes that Tid, Bid combination invalid. If a Remove is always guaranteed to be last, and the only one in the timeline for that combination (or you don't care about earlier removes as long as they weren't the last entry), you can cheat and get a slightly more efficient plan:

;WITH x AS 
(
  SELECT Tid, Bid, [Action], [Status], Value, [Time], rn = ROW_NUMBER() 
    OVER (PARTITION BY Tid, Bid, [Status] ORDER BY [time] DESC)
  FROM dbo.MassiveTable
  WHERE [Status] = 'Executed'
)
SELECT Tid, Bid, [Action], [Status], Value, [Time]
FROM x WHERE [Action] <> 'Remove' AND rn = 1;

But those assumptions are important. Test all queries with this additional row of sample data:

(3, 'B', 'Remove', 'Executed', 45, '15:23'),

Best Answer

Related Solutions

Sql-server – Select multiple rows based on date interval from two fields

SQL Server – How to Exclude Rows from SELECT Query

Related Question