SQL Server 仅当值大于0时对分区求和

nsc4cvqm 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(149)

I only want to sum the applied amount when a ledger amount in another table is positive
Example
Table A

Statement #            ID
500                     1
500                     2
500                     3
500                     4

Table B

Ledger_Amount  Type        ID      
-389.41        Credit      1          
-1218.9        Credit      2          
-243.63        Credit      3          
3485.19        Invoice     4

Table C

Applied_Amount           ID 
389.41                    1
1218.9                    2
243.63                    3
1633.25                   4

The current code is

(sum(applied_amount) over (partition by statement_number),0)

It is coming up with a total of $3485.19 because it is summing by statement number only, and all IDs have the same statement number, the value I want it to come up with is $1633.25 because it should not sum anything where the ledger_amount in table B is less than 0, so ID 1,2,3 should not be summed only valid value is ID 4

sql-server

来源：https://stackoverflow.com/questions/74747370/sum-over-partition-by-only-when-value-is-greater-than-0

1条答案

按热度按时间

bq3bfh9z1#

There is one approach:
Assuming ID is a unique column, first we should get the IDs we'd work on based on the statementnumber and save them in a temp table:

select Id
into #Ids
from tableA
where StatementNumber=@yourStatementNumber

Then, eliminate the IDs where they have a negative number in table B

Select Id 
into #IdsWithPositiveLedger
From #Ids
Where Id in (
   Select ID
   From tableB
   Where Ledger_Amount>0 
)

Finally, use the ids left to get your sum:

Select sum(applied_amount)
from tableC
where Id in (select Id from #IdsWithPositiveLedger)

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 仅当值大于0时对分区求和

1条答案

相关问题

热门标签

最新问答