多年来,我一直在寻找答案,但对我的特殊问题却没有任何运气。
我被要求创建一个脚本来提取邮件合并的数据。我们的计费团队需要确定谁的用户,我们已经发送了最后警告通知,但仍然没有支付他们的账单。
我无法克服的最大挑战是,一些客户可能做了以下工作之一:
用于生成最后警告通知的数据出现在一个表中,未清金额显示为正数,任何已支付的款项都以负数的形式出现在不同的表格中。
在我的查询中,从场景1和场景2中识别客户是相当容易的,但是3和4会让我头疼。我想把未付的款项加到任何付款的金额上。如果总金额大于0,则客户支付的金额不足以支付未付金额。我开发了一个子查询,以获得在某一日期后支付的所有款项的总数,但我无法让查询将其添加到未清金额中,然后检查余额是否大于0。我得到的错误是“不能对包含聚合或子查询的表达式执行聚合函数”。
有人能推荐一种不同的方法来进行这个查询吗?
SELECT
finalwarning.table_no AS [Property No],
finalwarning.value_1 AS [Arrears Balance],
finalwarning.date_1 AS [Notice Date]
FROM
finalwarning
WHERE
finalwarning.table_no = 172030
AND finalwarning.ref_3 = 'Final'
AND ((SELECT
CASE
WHEN EXISTS (SELECT 1
FROM paymentsmade
WHERE PROPERTY_NO = 1234
AND TRANSACTION_DESC = 'Receipt'
AND TRANSACTION_DATE > '2017-05-01 00:00:00.000'
GROUP BY TRANSACTION_DESC)
THEN (SELECT SUM(amount)
FROM paymentsmade
WHERE paymentsmade.PROPERTY_NO = 1234
AND paymentsmade.TRANSACTION_DESC = 'Receipt'
AND paymentsmade.TRANSACTION_DATE > '2017-05-01 00:00:00.000'
GROUP BY paymentsmade.TRANSACTION_DESC)
ELSE '0'
END) + finalwarning.value_1) < 0发布于 2017-07-19 01:04:33
CTE是我们的朋友,确实帮助我们完成了艰苦的工作,并允许更容易地编写核心SQL。下面的示例采用您的结构,并应用支付CTE的概念,该概念将按属性no汇总付款,并提供您在评估中需要执行的所有计算。我不确定日期是如何起作用的,而且连接可能是错误的,因为在示例中为property_no提供了两个不同的数字,但我假设它们是相同的。
WITH paymentCTE (property_no, amount, transaction_desc, cntpayments)
as
(select property_no, sum(amount) as amount, transaction_desc, count(*)
from paymentsmade
where TRANSACTION_DATE > > '2017-05-01 00:00:00.000'
group by property_no, transaction_desc
)
SELECT fw.table_no AS [Property No]
, fw.value_1 AS [Arrears Balance]
, fw.date_1 AS [Notice Date]
, coalesce(p1.amount,0) as [Payments Made]
, fw.value_1+coalesce(p1.amount,0) as [Outstanding Balance]
, coalesce(p1.cntpayments, 0) as [Number of Payments]
FROM finalwarning fw
left outer join paymentCTE p1
on fw.table_no = p1.property_no
and p1.Transaction_desc = 'Receipt'
WHERE p1.cntpayments is null
or (coalesce(p1.cntpayments, 0) = 1 and fw.value_1 + coalesce(p1.amount,0) = 0)
or (coalesce(p1.cntpayments, 0) > 1 and fw.value_1 + coalesce(p1.amount,0) = 0)
or (coalesce(p1.cntpayments, 0) > 0 and fw.value_1 + coalesce(p1.amount,0)<> 0)https://stackoverflow.com/questions/45179081
复制相似问题