postgresql 计算日期并求和金额

Question

我想将数据排列成帐户报表格式

表 A

Creditors name       Invoice No         Due date          Outstanding Amt
Company A            A001              1/8/2014            1500
Company B            A002              1/10/2014            300
Company B            A003              5/11/2014            900
Company B            A004              6/11/2014            50        
Company c            A005              31/12/2014           200

报表日期：31/12/14（根据当前月份始终变化），预期输出

Creditors name    0-30 Days    31-60 Days   61-90 Days   Over 90 Days   Amt Due        
Company A             0           0           0              1500         1500
Company B             0           950         0              300          1250
Company c            200          0           0               0           200

请帮忙和谢谢

Answer 1

select "Creditors name",
       SUM(case when "Due date" >= current_date - 30 then "Outstanding Amt" end) as "0-30 Days",
       SUM(case when "Due date" between current_date - 31 and current_date - 60 then "Outstanding Amt" end) as "31-60 Days",
       SUM(case when "Due date" between current_date - 61 and current_date - 90 then "Outstanding Amt" end) as "61-90 Days",
       SUM(case when "Due date" < current_date - 90 then "Outstanding Amt" end) as "Over 90 Days",
       SUM("Outstanding Amt") as "Amt Due"
from "Table A"
group by "Creditors name"

日期和时间总是很难处理，因为大多数 dbms 产品的行为不同，而且我不知道您的 dbms 是如何工作的。所以你需要修改我的代码！

Answer 2

如下所示的内容可能有效

Select 
creditor_name,
sum(case when datediff(day,due_date,getdate()) between 0 and 30 then [Outstanding Amt] else 0 end) as [0-30 days],
sum(case when datediff(day,due_date,getdate()) between 31 and 60 then [Outstanding Amt] else 0 end) as [0-30 days],
.
.
.
from table
group by creditor_name,