在现有WIP交易统计SQL中新增已开票、未开票汇总列的实现方法
需补充的两段代码
- 逐行属性判断代码(用于给每条WIP交易标记对应金额归属):
case when WIPTransaction.Invoicenumber is not null then WIPTransaction.Amount else 0 end 'INVOICED', case when WIPTransaction.Invoicenumber is null then WIPTransaction.Amount else 0 end 'UNINVOICED' - 分组汇总代码(用于按FeeEarnerID聚合计算两类金额的总和):
sum(aa.INVOICED) Invoiced, sum(aa.UNINVOICED) Uninvoiced
代码放置的精确位置
- 逐行判断代码:放在内层嵌套子查询(最终别名为
aa的SELECT块)的字段列表中,紧跟原有CREDITORS字段的CASE计算语句后面,修改原有语句末尾的标点为逗号后插入即可。这里是逐行遍历每条WIP交易数据的环节,刚好可以直接读取Invoicenumber的空值状态给金额打标,和现有FEES、DISB、CREDITORS的拆分计算逻辑完全统一,不需要调整原有JOIN关联、日期过滤的逻辑。 - 分组汇总代码:放在最外层主查询的SELECT字段列表最末尾,紧跟原有
sum(aa.CREDITORS) Creditors语句的后面,作为结果集的最后两列。因为这部分是对aa子查询结果按FeeEarnerID分组后的聚合计算,不需要修改原有GROUP BY的规则,不会影响现有字段的计算逻辑。
修正后的完整SQL参考
select FeeEarnerID, ( select (select [name] from [User] AS u where u.userid=f.userid) from feeearner AS f where f.FeeEarnerID=aa.FeeEarnerID ) FeeEarner, sum(aa.FEES) Fees, sum(aa.DISB) Disbursements, sum(aa.CREDITORS) Creditors, -- 新增的两列聚合结果 sum(aa.INVOICED) Invoiced, sum(aa.UNINVOICED) Uninvoiced from ( SELECT FeeEarner.FeeEarnerID, case when WIPTransaction.WIPTransactionTypeID IN (1,17,18,20,21,25) then WIPTransaction.Amount else 0 end 'FEES', case when WIPTransaction.WIPTransactionTypeID IN (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29) then WIPTransaction.Amount else 0 end 'DISB', case when WIPTransaction.WIPTransactionTypeID IN (24) then WIPTransaction.Amount else 0 end 'CREDITORS', -- 新增的逐行判断字段 case when WIPTransaction.Invoicenumber is not null then WIPTransaction.Amount else 0 end 'INVOICED', case when WIPTransaction.Invoicenumber is null then WIPTransaction.Amount else 0 end 'UNINVOICED' FROM ( ( FeeEarner JOIN WIPTransaction ON FeeEarner.FeeEarnerID = WIPTransaction.FeeEarnerID ) JOIN WIPTransactionType ON WIPTransactionType.WIPTransactionTypeID = WIPTransaction.WIPTransactionTypeID ) WHERE (WIPTransaction.TransactionDate BETWEEN '2020-10-01' AND '2020-12-31') ) AS aa group by FeeEarnerID
内容的提问来源于stack exchange,提问作者CrackkiiCrazy
相关产品推荐
相关产品推荐

