SQL Server Return only the row with positive amount if all other columns are the same

368yc8dk 于 2023-06-04 发布在其他

关注(0)|答案(3)|浏览(210)

I have a query that returns several columns, Date, settle, acctNum, name, TCode, Amount.
The problem is that, there are records that are identical except for the Amount, which has the same absolute value on both records except that it is positive in one and negative in the other. I need to return only the row with the positive value for Amount. Would anyone have any suggestion? Please, see the below example. I need only the row with the positive amount 23450. I can't just filter out negatives because only negative that meet the conditions of the provided example are of interest. SQL Server 15.0.41

Example:
| Date | Settle | AcctNum | Name | TCode | Amount |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| Jan 1 | Y | 123456 | Mark | tabcd | 23450 |
| Jan 1 | Y | 123456 | Mark | tabcd | -23450 |
| Jan 7 | Y | 111111 | Vic | tabcd | 446623 |
| Mar 20 | Y | 999999 | Yas | tvfeh | 7778 |
| Jan 1 | Y | 123456 | Mark | tabcd | 987123 |
| Aug 9 | Y | 123456 | Mark | tabcd | 678599 |
| Dec 2 | Y | 564432 | Ali | tgghy | -987699 |

Expected result:

Date	Settle	AcctNum	Name	TCode	Amount
Jan 1	Y	123456	Mark	tabcd	23450
Jan 7	Y	111111	Vic	tccwe	446623
Mar 20	Y	999999	Yas	tvfeh	7778
Jan 1	Y	123456	Mark	tabcd	987123
Aug 9	Y	123456	Mark	tabcd	678599
Dec 2	Y	564432	Ali	tgghy	-987699

sql-server

来源：https://stackoverflow.com/questions/76348521/return-only-the-row-with-positive-amount-if-all-other-columns-are-the-same

3条答案

按热度按时间

lbsnaicq1#

Something like :

select * from <tableName> A where A.Amount >= 0 
or not exists(select * from <tableName> B where B.Amount = -A.Amount
    and B.Date = A.Date and B.settle = A.settle and B.acctNum = A.acctNum 
    and B.name = A.name and B.TCode = A.TCode )

赞(0）回复(0）举报 2023-06-04

zte4gxcn2#

You can use NOT EXISTS to skip the "duplicate" rows:

select *
from tablename t1
where not exists
  (select 1 from tablename t2
   where t2.Date = t1.Date
     and t2.Settle = t1.Settle
     and t2.AcctNum = t1.AcctNum
     and t2.Name = t1.Name
     and t2.TCode = t1.Tcode
     and t2.Amount = -t1.Amount and t1.Amount > 0)

Or use EXCEPT :

select Date, Settle, AcctNum, Name, TCode, Amount
from tablename
except
select Date, Settle, AcctNum, Name, TCode, -Amount
from tablename
where Amount > 0

赞(0）回复(0）举报 2023-06-04

cgvd09ve3#

You could make use of a window count, such as:

select *
from (
  select *, 
    Iif(Amount > 0, 1, 
      Count(*) over(partition by Settle, AcctNum, Name, TCode, Abs(amount))
    ) Valid
  from t
)t
where Valid = 1;

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Return only the row with positive amount if all other columns are the same

3条答案

相关问题

热门标签

最新问答