时间戳

首页 > 解决方案 > 更改 Bool 时 SQL Server 调整计算

sql-server - 更改 Bool 时 SQL Server 调整计算

问题描述

在 Sql Server 中，我试图添加一个列来根据员工的开始日期计算员工的额外休假，而学徒没有得到任何休假。但我收到一个错误。Incorrect syntax near the keyword 'AS'.

还有一种方法可以在将其Apprentice设置为 False 后开始计算额外休假。

ALTER TABLE [dbo].[Employee]
ADD AwardedLeave as CAST 
(


CASE
When [Apprentice] = 'True'Then 0
When [Apprentice] = 'False' Then 
CASE 

WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate]),01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+1,12,31)  THEN 0
WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+2,01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+2,12,31) THEN 8
WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+3,01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+3,12,31) THEN 16
WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+4,01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+4,12,31) THEN 24 
WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+5,01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+5,12,31) THEN 32
WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+6,01,01) AND 
DATEFROMPARTS(YEAR([StartDate])+1000,12,31) THEN 40 

END 

+

CASE 
WHEN DATEFROMPARTS(YEAR(StartDate), 12,31) >= GETDATE() THEN datediff(day, 
[StartDate],datefromparts(datepart(year,[StartDate]),(12), 
(31)))/(30.42)*13.33
ELSE [ALCategory]
END AS int 

)

标签： sql-serverbit

解决方案

很好地使用空白和换行符非常重要，我强烈建议同时使用这两种方法来使您的代码可读（不仅对其他人，而且对您自己），从而更容易调试。如果你格式化你的代码，那么原因就变得更加明显了：

ALTER TABLE [dbo].[Employee]
ADD AwardedLeave AS CAST (CASE WHEN [Apprentice] = 'True'THEN 0
                                WHEN [Apprentice] = 'False' THEN CASE WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate]),01,01) AND DATEFROMPARTS(YEAR([StartDate])+1,12,31)  THEN 0
                                                                        WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+2,01,01) AND DATEFROMPARTS(YEAR([StartDate])+2,12,31) THEN 8
                                                                        WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+3,01,01) AND DATEFROMPARTS(YEAR([StartDate])+3,12,31) THEN 16
                                                                        WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+4,01,01) AND DATEFROMPARTS(YEAR([StartDate])+4,12,31) THEN 24 
                                                                        WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+5,01,01) AND DATEFROMPARTS(YEAR([StartDate])+5,12,31) THEN 32
                                                                        WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+6,01,01) AND DATEFROMPARTS(YEAR([StartDate])+1000,12,31) THEN 40 
                                                                END +
                                                                CASE WHEN DATEFROMPARTS(YEAR(StartDate), 12,31) >= GETDATE() THEN DATEDIFF(DAY, [StartDate],DATEFROMPARTS(DATEPART(YEAR,[StartDate]),(12), (31)))/(30.42)*13.33
                                                                        ELSE [ALCategory]
                                                                END AS int);

请注意，您的第一个CASE表达式没有END; 因此（预期AS关键字）处的错误：END

ALTER TABLE [dbo].[Employee]
ADD AwardedLeave AS CAST (CASE WHEN [Apprentice] = 'True'THEN 0
                               WHEN [Apprentice] = 'False' THEN CASE WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate]),01,01) AND DATEFROMPARTS(YEAR([StartDate])+1,12,31)  THEN 0
                                                                     WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+2,01,01) AND DATEFROMPARTS(YEAR([StartDate])+2,12,31) THEN 8
                                                                     WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+3,01,01) AND DATEFROMPARTS(YEAR([StartDate])+3,12,31) THEN 16
                                                                     WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+4,01,01) AND DATEFROMPARTS(YEAR([StartDate])+4,12,31) THEN 24 
                                                                     WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+5,01,01) AND DATEFROMPARTS(YEAR([StartDate])+5,12,31) THEN 32
                                                                     WHEN GETDATE() BETWEEN DATEFROMPARTS(YEAR([StartDate])+6,01,01) AND DATEFROMPARTS(YEAR([StartDate])+1000,12,31) THEN 40 
                                                                END +
                                                                CASE WHEN DATEFROMPARTS(YEAR(StartDate), 12,31) >= GETDATE() THEN DATEDIFF(DAY, [StartDate],DATEFROMPARTS(DATEPART(YEAR,[StartDate]),(12), (31)))/(30.42)*13.33
                                                                     ELSE [ALCategory]
                                                                END
                          END AS int);

推荐阅读