【工作中的常用SQL】应收账款账龄分析

以下是一个简单的应收账款账龄分析的 SQL 查询语句，假设有一个名为 `receivables` 的表，其中包含以下字段：

- `id`：应收账款记录的唯一标识符- `customer_id`：客户的唯一标识符- `invoice_date`：发票日期- `due_date`：应付款日期- `amount`：发票金额- `paid_amount`：已付金额

查询语句如下（基于Sqlserver）：

SELECTcustomer_id,SUM(CASE WHEN DATEDIFF(day, due_date, GETDATE()) <= 30 THEN amount - paid_amount ELSE 0 END) AS current,SUM(CASE WHEN DATEDIFF(day, due_date, GETDATE()) > 30 AND DATEDIFF(day, due_date, GETDATE()) <= 60 THEN amount - paid_amount ELSE 0 END) AS thirty_to_sixty,SUM(CASE WHEN DATEDIFF(day, due_date, GETDATE()) > 60 AND DATEDIFF(day, due_date, GETDATE()) <= 90 THEN amount - paid_amount ELSE 0 END) AS sixty_to_ninety,SUM(CASE WHEN DATEDIFF(day, due_date, GETDATE()) > 90 THEN amount - paid_amount ELSE 0 END) AS over_ninetyFROMreceivablesGROUP BYcustomer_id;

这个查询语句将应收账款按照客户进行分组，并计算了当前账龄、30-60天账龄、60-90天账龄和超过90天账龄的应收账款总额。其中，`DATEDIFF(day, due_date, GETDATE())` 函数用于计算发票的逾期天数。