gpt4 book ai didi

sql - 如何在运行总计达到零的帐户上应用状态

转载 作者:搜寻专家 更新时间:2023-10-30 23:26:16 24 4
gpt4 key购买 nike

下面是我正在尝试操作的示例数据。

    +----------------+------------------+---------+------+--------------+-------------+--+--+
| ACCOUNT_NUMBER | TRANSACTION_DATE | bal | Row# | RunningTotal | status | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 82.61 | 4 | 82.61 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 85.25 | 5 | 167.86 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 93.61 | 6 | 261.47 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 30/04/2015 | 78.95 | 7 | 340.42 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 22/05/2015 | -62.04 | 8 | 278.38 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/05/2015 | 98.95 | 9 | 377.33 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 30/06/2015 | 79.5 | 10 | 456.83 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 15/07/2015 | -345.76 | 11 | 111.07 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 12/05/2016 | -111.07 | 12 | 0 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 13 | 2.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 14 | 5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 15 | 7.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/04/2015 | 2.5 | 16 | 10 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2015 | 2.5 | 17 | 12.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/06/2015 | 0.67 | 18 | 13.17 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/07/2015 | -0.81 | 19 | 12.36 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2018 | 5.08 | 20 | 17.44 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/11/2018 | 1.02 | 21 | 18.46 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2019 | 1.48 | 22 | 19.94 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 8.38 | 23 | 8.38 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 10.65 | 24 | 19.03 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 25.07 | 25 | 44.1 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/04/2015 | 12.21 | 26 | 56.31 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/04/2015 | -20 | 27 | 36.31 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 20/05/2015 | -36.31 | 28 | 0 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/05/2015 | -3.69 | 29 | -3.69 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/05/2015 | 13.17 | 30 | 9.48 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/06/2015 | 9 | 31 | 18.48 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 25/07/2015 | -18.48 | 32 | 0 | CLEARED

下面是用于应用发票状态的脚本。本质上,我希望能够确定某个帐户是否已取消其所有发票。我有两个条件:

  1. 如果余额总和为零,则应用 CLEARED
  2. 我想弄清楚的第二个选项是说。如果不知何故,运行总计的最终总和不为零,但在它清除的时候让所有上述发票都清除标记。


  select *,
(CASE WHEN sum(bal) OVER (PARTITION BY ACCOUNT_NUMBER ) = 0 THEN 'CLEARED'
WHEN sum(bal) OVER (PARTITION BY ACCOUNT_NUMBER order by Row#,TRANSACTION_DATE ) = 0 THEN 'CLEARED'
else 'NOT_CLEARED'
end) as status
from #running_totals
order by Row#, TRANSACTION_DATE

有人可以帮助我如何应用它吗预期结果

   +----------------+------------------+---------+------+--------------+-------------+--+--+
| ACCOUNT_NUMBER | TRANSACTION_DATE | bal | Row# | RunningTotal | status | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 82.61 | 4 | 82.61 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 85.25 | 5 | 167.86 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/03/2015 | 93.61 | 6 | 261.47 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 30/04/2015 | 78.95 | 7 | 340.42 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 22/05/2015 | -62.04 | 8 | 278.38 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 31/05/2015 | 98.95 | 9 | 377.33 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 30/06/2015 | 79.5 | 10 | 456.83 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 15/07/2015 | -345.76 | 11 | 111.07 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 155 | 12/05/2016 | -111.07 | 12 | 0 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 13 | 2.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 14 | 5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/03/2015 | 2.5 | 15 | 7.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/04/2015 | 2.5 | 16 | 10 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2015 | 2.5 | 17 | 12.5 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/06/2015 | 0.67 | 18 | 13.17 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/07/2015 | -0.81 | 19 | 12.36 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2018 | 5.08 | 20 | 17.44 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 30/11/2018 | 1.02 | 21 | 18.46 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 953 | 31/05/2019 | 1.48 | 22 | 19.94 | NOT_CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 8.38 | 23 | 8.38 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 10.65 | 24 | 19.03 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/03/2015 | 25.07 | 25 | 44.1 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/04/2015 | 12.21 | 26 | 56.31 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/04/2015 | -20 | 27 | 36.31 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 20/05/2015 | -36.31 | 28 | 0 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/05/2015 | -3.69 | 29 | -3.69 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 31/05/2015 | 13.17 | 30 | 9.48 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 30/06/2015 | 9 | 31 | 18.48 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| 961 | 25/07/2015 | -18.48 | 32 | 0 | CLEARED | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| | | | | | | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| | | | | | | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+
| | | | | | | | |
+----------------+------------------+---------+------+--------------+-------------+--+--+

最佳答案

试一试。鉴于您提供的数据表,我假设您已经有了 RowNum 行。您或许可以对此进行改进,但这是一个开始。

;WITH CTE AS(
select t1.ACCOUNT_NUMBER AN3, t1.RowNum RN3
from #temp t1
CROSS APPLY(select ACCOUNT_NUMBER AN2,RowNum RN2
from #temp where RunningTotal=0) t2 where t1.ACCOUNT_NUMBER = t2.AN2 and t1.RowNum <= t2.RN2
)
select ACCOUNT_NUMBER,TRANSACTION_DATE,bal,RowNum,RunningTotal,Status,
CASE WHEN t2.AN3 IS NOT NULL THEN 'CLEARED'
ELSE 'NOT CLEARED' END Status
from #temp t1
LEFT JOIN CTE t2 on t1.ACCOUNT_NUMBER = t2.AN3 and t1.RowNum = t2.RN3

关于sql - 如何在运行总计达到零的帐户上应用状态,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57390253/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com