时间戳

首页 > 解决方案 > 在 sum 中嵌套 Case 语句 - SQL Server

sql-server - 在 sum 中嵌套 Case 语句 - SQL Server

问题描述

遇到问题：

我有以下查询，它为我提供了我需要的视图。

SELECT t.Week,
       SUM(t.Hours) AS AH,
       SUM(c.Input) AS input,
       SUM(c.[recruitedPos]) AS recruitedPos,
       (SUM(t.Hours) - SUM(c.Input)) AS Possible
FROM tempPOC AS t
     LEFT JOIN (SELECT Week,
                       SUM(input) AS Input,
                       SUM([Normal Weekly Hours]) AS recruitedPos,
                       Shop
                FROM colData
                GROUP BY week,
                         Shop) AS c ON t.Week = c.Week
                                   AND c.Shop = t.Store
GROUP BY t.Week,
         t.Store
ORDER BY t.Week;

我遇到的问题是编写一个 case 语句来将任何值从可能的别名设置为 0，如果它们低于 0。

我以为这很简单

(CASE(SUM(t.Hours - c.Hours)) < 0 then 0 else Possible end as Possible)

但这给了我错误

消息 156，级别 15，状态 1，第 9 行
关键字“as”附近的语法不正确。

输入 ：

Week, AH, Input, RecruitedPos, Possible
1,    15, 25,    13,            -10
1,    30, 15,    15,            15

预期输出：

Week, AH, Input, RecruitedPos, Possible
1,    15, 25,    13,            0  
1,    30, 15,    15,            15

标签： sql-server

解决方案

CASE
    WHEN SUM(t.Hours) - SUM(c.Input) < 0 THEN 0
    ELSE SUM(t.Hours) - SUM(c.Input)
END AS Possible

推荐阅读