sql - 使用sql查询老化结构

Question

我有一个类似下面的表结构

TNO DATE        NETAMT  CHQ CASH    PARTY
---------------------------------------------
T1  01/04/2016  100     10  0       TEST1
T2  15/04/2016  50      0   0       TEST2
T3  18/04/2016  100     100 0       TEST3

现在我想做一个 SQL 查询，它会给我一份老化报告，说明哪些事务在哪些 slab 中处于未完成状态。

我想看下面格式的结果

PARTY   0-3 DAYS    4-6 DAYS    7-15 DAYS
---------------------------------------------   
TEST1       0           0           90
TEST2       0           50          0
TEST3       0           0           0

Answer 1

试试这个

SELECT  PARTY,
        SUM(CASE WHEN DATEDIFF(DAY,[DATE],GETDATE()) BETWEEN 0 AND 3  THEN (NETAMT - CHQ) ELSE 0 END) AS [0_3_DAYS],
        SUM(CASE WHEN DATEDIFF(DAY,[DATE],GETDATE()) BETWEEN 4 AND 6  THEN (NETAMT - CHQ) ELSE 0 END) AS [4_6_DAYS],
        SUM(CASE WHEN DATEDIFF(DAY,[DATE],GETDATE()) BETWEEN 7 AND 15 THEN (NETAMT - CHQ) ELSE 0 END) AS [7_15_DAYS],
FROM    TABLE1
GROUP BY PARTY