我想使用mysql查询以Matrix格式创建当前的30,60,90天的老化报告
例如,
ClientName当前1-30 31-60> 90 总计
AAA 3000 1500 4500
BBB 2000 200 2200
TOTAL 3000 3500 200 6700
我想得到这样的报告。当用户输入日期时,应该使用duedate进行检查。当输入日期是> due_date时,获取老化天数。
如果今天老化agin天然后得到netAnmount并显示在当前列中,
如果差异是1-30天,那么下一栏...等...
如何获得此查询?
发布于 2019-06-11 16:23:39
假设你的表看起来像这样
CREATE TABLE invoices (
-- ...some stuff ...
client_id ...,
due_date date ,
amount_due ...,
-- ... some more stuff ...
)
试试这个:
SELECT client_id,
DATEDIFF(CURDATE(), due_date) AS days_past_due,
SUM(IF(days_past_due = 0, amount_due, 0)),
SUM(IF(days_past_due BETWEEN 1 AND 30, amount_due, 0)),
SUM(IF(days_past_due BETWEEN 31 AND 60, amount_due, 0)),
SUM(IF(days_past_due BETWEEN 61 AND 90, amount_due, 0)),
SUM(IF(days_past_due > 90, amount_due, 0))
FROM invoices
GROUP BY client_id
https://stackoverflow.com/questions/-100009092
复制相似问题