SQL:根据财务报告的最后付款日期创建账龄桶

Question

我需要在客户级别创建信用余额的账龄报告。

笔记:

时效基于客户的最后付款日期。

一个客户可以有多个帐户，有时会出现错误，将付款应用到错误的帐户。例如，账户余额为 15 美元的客户支付了 15 美元。这笔 15 美元的付款可能会应用到错误的帐户中，从而使客户在一个帐户上的余额为 -15 美元，而在另一个帐户上的余额为 15 美元。此客户需要从报告中排除。

获取具有信用余额的客户的 SQL:

SELECT 
  ACCOUNT.CUST_ID
, sum(ACCOUNT.BALANCE)

FROM ACCOUNT

GROUP BY ACCOUNT.CUST_ID

HAVING sum(ACCOUNT.BALANCE) < 0

获取最新付款日期的 SQL:

SELECT

  TRANSACTIONS.CUST_ID
, MAX(TRANSACTIONS.POST_DATE)

FROM TRANSACTIONS

WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'

GROUP BY TRANSACTIONS.CUST_ID

我需要为老化的存储桶创建列，例如:

'0 - 30' 信用余额总和

'0 - 30' 信用余额客户计数

'31 - 60'...

我打算在 max(TRANSACTIONS.POST_DATE) 和“昨天”之间使用 DATEDIFF 函数使用 CASE 语句 - DATEADD(dd,-1,getdate()) 来创建存储桶。

但是，在执行存储桶总和和计数计算之前，使用变量或存储过程根据上次付款日期将客户分开，不是更有效吗？

关于如何准确有效地做到这一点的任何想法？到目前为止，我一直在通过一个简单的查询来获取具有信用余额的客户以及他们的上次付款日期，然后自己使用 Excel 创建老化桶。

Answer 1

你的方法是正确的方法。我不确定为什么您认为在存储过程中执行某些操作比在查询中执行更有效。

我想你想要一个类似的查询:

select (case when t.DaysAgo between 0 and 30 then '0-30'
             when t.DaysAgo between 31 and 60 then '31-60'
             else 'OLD'
        end),
       <whatever you want here>
from (SELECT ACCOUNT.CUST_ID, sum(ACCOUNT.BALANCE) as balance
      FROM ACCOUNT
      GROUP BY ACCOUNT.CUST_ID
     ) a left outer join
     (SELECT TRANSACTIONS.CUST_ID, MAX(TRANSACTIONS.POST_DATE) as LastPaymentDate,
             datediff(d, X(TRANSACTIONS.POST_DATE), getdate()) as DaysAgo, 
      FROM TRANSACTIONS
      WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'
      GROUP BY TRANSACTIONS.CUST_ID
     ) t
     on a.cust_id = t.cust_id 
group by  (case when t.DaysAgo between 0 and 30 then '0-30'
                when t.DaysAgo between 31 and 60 then '31-60'
                else 'OLD'
           end)

通常，您应该让 SQL 引擎优化查询。通常(但绝对不总是!)做好工作，让您思考您想要完成的工作。

您将有额外的逻辑来排除某些客户并引入其他字段。