时间戳

首页 > 解决方案 > SQL 用于每个客户每年的总销售额

sql - SQL 用于每个客户每年的总销售额

问题描述

使用 Advantage Database Server 11 我试图逐年查找每个客户从两种类型的总购买量进行比较，但出现错误：

[iAnywhere 解决方案][Adv​​antage SQL 引擎]未找到预期的词法元素：THEN

这是桌子。

customernr | Date_in | Status | InvType | Qty | Total
1111          9/1/2018            D         5    25.00
1111         12/1/2018   V        D         3    15.00
1111         5/12/2019            L         1     2.00
1111         7/11/2019            D         5    35.00
1112         6/21/2018            L         7    18.00
1112         9/14/2019            L         3     7.00


Select 
customernr,
SUM(CASE WHEN date_in >= '2018-01-01' and date_in <= '2018-12-31' total 
and InvType='D' ELSE 0 END) LastYD,
SUM(CASE WHEN date_in >= '2018-01-01' and date_in <= '2018-12-31' total 
and InvType='L' ELSE 0 END) LastYL,
SUM(CASE WHEN date_in >= '2019-01-01' and date_in <= '2019-12-31' total 
and InvType='D' ELSE 0 END) ThisYD,
SUM(CASE WHEN date_in >= '2019-01-01' and date_in <= '2019-12-31' total 
and InvType='L' ELSE 0 END) ThisYL

from invoice
where (Status <> 'V' or Status IS NULL)

Group by Customernr

谢谢你的帮助，

KHJ

标签： sqlsumadvantage-database-server

解决方案

THEN你的CASE陈述中有遗漏

Select 
customernr,
SUM(CASE WHEN date_in >= '2018-01-01' and date_in <= '2018-12-31' 
and InvType='D' THEN total ELSE 0 END) LastYD,
SUM(CASE WHEN date_in >= '2018-01-01' and date_in <= '2018-12-31' 
and InvType='L' THEN total ELSE 0 END) LastYL,
SUM(CASE WHEN date_in >= '2019-01-01' and date_in <= '2019-12-31' 
and InvType='D' THEN total ELSE 0 END) ThisYD,
SUM(CASE WHEN date_in >= '2019-01-01' and date_in <= '2019-12-31' 
and InvType='L' THEN total ELSE 0 END) ThisYL

from invoice
where (Status <> 'V' or Status IS NULL)

Group by Customernr

推荐阅读