mysql - MySQL 中老化试算表报表的 SQL 查询

Question

到目前为止，我的查询会生成客户账户的当前余额以及在报告生成后的以下几天内开具发票的总金额:0-30、30-60、60-90、90+。

我对如何应用客户已经支付的款项以准确反射(reflect)每个发票期仍欠的金额感到困惑。因此，如果某个帐户具有以下余额和在上述期间开具发票的金额:

 Balance: $200     (current balance left owing on someones account)
    0-30: $20      ($20 invoice during a period of 0-30 days prior to generation of report)
   30-60: $100     
   60-90: $100     ($100 invoiced during 60-90 days prior)
     90+: $2500

我要找的结果是:

 Balance: $200
    0-30: $20
   30-60: $100
   60-90: $80
     90+: $0

这将是在应用所有已支付给该帐户的付款之后。如果他们开具发票的总金额从 0-30、30-60、60-90 和 中加起来达到 2720 美元>90+ 字段，但他们只剩下 200 美元要支付，我可以通过从发票总额中减去当前余额得到客户到目前为止支付的金额:2720 美元 - 200 美元 = 2520 美元

我想做的是先将这些付款应用于最早的发票。如果他们支付了 2720 美元并且在 90 多天前只收到了 2500 美元的发票，那么他们在那段时间基本上不再欠任何东西。这让我剩下 20 美元可用于下一个最早的发票期，将 60-90 天字段从 100 美元减少到 80 美元。

我不确定我一开始是否采取了正确的方法。目前，我正在生成第一个示例(给定期间的余额和发票总额)，没有问题，作为下一个查询的子查询，我试图在该查询中应用对发票期间的付款。

我想出了一个 CASE 表达式，用于将付款应用到 90+ 字段，但我不确定如何“遍历”其余字段——跟踪支付了多少我已经留下来申请了。

select account_number,

    Balance,
    case
        when Balance = 0
            or (total_invoices - Balance - `90+`) >= 0
            then 0
        else (total_invoices - Balance - `90+`)*-1
    end as `90+`,

    case
        when Balance = 0 then 0 else `60-90` end as `60-90`,

    case when Balance = 0 then 0 else `30-60` end as `30-60`,

    case
        when Balance = 0 then 0 else `0-30`
    end as `0-30`

from (
select account_number,

    round(sum(if(charge_type = 0, total*-1, total)),2) as "Balance",

    round(sum(
        if(charge_type != 0 
            and DATEDIFF(curdate(),trans_date) < 30, total, 0)
    ),2) as "0-30",

    round(sum(
        if(charge_type != 0 
            and DATEDIFF(curdate(),trans_date) between 30 and 60, total, 0)
    ),2) as "30-60",

    round(sum(
        if(charge_type != 0 
            and DATEDIFF(curdate(),trans_date) between 60 and 90, total, 0)
    ),2) as "60-90",

    round(sum(
        if(charge_type != 0 
            and DATEDIFF(curdate(),trans_date) >= 90, total, 0)
    ),2) as "90+",

    round(sum(
        if(charge_type != 0, total, 0)
    ),2) as "total_invoices"

from
    (select  #UNION current & archive
        account_number,
        trans_date,
        charge_type,
        total
    from
        invoices_and_transactions_archive
    UNION ALL
    select
        account_number,
        trans_date,
        charge_type,
        total
    from
        invoices_and_transactions
    where
        is_carry_forward = 0

    ) q1
group by account_number
) x

在这种情况下，似乎 CASE 表达式和 IF 函数可能会受到限制。另外，即使我确实找到了一些公式来获得正确的余额，理想情况下列的顺序也不是我想要的，我将不得不执行另一个子查询来反转它们。

编辑:这种方法有效。

select account_number,

    Balance,
    case
        when Balance = 0 then 0
        when (total_invoices - Balance - `30-60` - `60-90` - `90+`) > 0
            # leave negative result since their balance should be in credit.
            # The same situation for older invoices would require setting balance
            # to these invoices to zero, since it means there's still payments leftover
            # to apply to new invoices
            then `0-30` - (total_invoices - Balance - `30-60` - `60-90` - `90+`)
        else `0-30`
    end as `0-30`,

    case
        when Balance = 0 then 0
        when (total_invoices - Balance - `60-90` - `90+`) > 0
            then
                if((`30-60` - (total_invoices - Balance - `60-90` - `90+`) >= 0),
                    `30-60` - (total_invoices - Balance - `60-90` - `90+`), 0
                )
        else `30-60`
    end as `30-60`,

    case
        when Balance = 0 then 0
        when (total_invoices - Balance - `90+`) > 0
            then
                if((`60-90` - (total_invoices - Balance - `90+`) >= 0),
                    `60-90` - (total_invoices - Balance - `90+`), 0
                )
        else `60-90`
    end as `60-90`,

    if(Balance != 0 and (`90+` - (total_invoices - Balance) >= 0),
        `90+` - (total_invoices - Balance), 0
    )as `90+`

from (...)

Answer 1

SELECT account_number,
       Balance,
       `0-30` - (total_invoices - Balance - LEAST(`90+` + `60-90` + `30-60`, total_invoices - Balance)) AS `0-30`,
       `30-60` - (total_invoices - Balance - LEAST(`90+` + `60-90`, total_invoices - Balance)) AS `30-60`,
       `60-90` - (total_invoices - Balance - LEAST(`90+`, total_invoices - Balance)) AS `60-90`,
       GREATEST(0, `90+` - (total_invoices - Balance)) AS `90+`
FROM (...)

DEMO