使用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查询，它将给我一个关于哪些事务处于未完成状态的老化报告。

我希望看到以下格式的结果

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