需要您的帮助，我不知道确切的名字。我想将记录从另一个字段创建到同一视图的一个字段中。但是当我创建它时，值显示却来自同一条记录。
我有4个字段的1个表，分别是YEAR，PEOPLE，REMAINING，CF
像这样的逻辑：
IF people A in 2014 have remaining value 25 and CF values 0 THEN people A in 2015 will have CF values 6。
IF Remaining > 6 THEN CF Values only 6, IF Remaining between 0 to 6 THEN CF Values = Remaining, IF Remaining < 0 THEN CF Values 0
IF values in Current Year will always take from Remaining in Last Year so if People A didn't have remaining in last year, the CF in current year become 0
这是我的代码：
SELECT
A.FiscalYear,
C.EmployeeName,
CASE
WHEN A.FiscalYear < 2015 THEN A.Remaining
WHEN B_1.daystaken < 0 THEN A.Remaining + ISNULL(B_1.DaysTaken, 0)
ELSE A.Entitlement
END AS Remaining,
CASE
WHEN A.remaining > 6 THEN 6
WHEN A.remaining BETWEEN 0 AND 6 THEN A.remaining
WHEN A.remaining < 0 THEN 0
ELSE ISNULL(A.remaining,0)
END AS CF
FROM dbo.DataLeaveBalance AS A
LEFT OUTER JOIN dbo.VWLeave_takens AS B_1
ON A.FiscalYear = B_1.AffectFY
AND A.EmpCode = B_1.EmpCode
LEFT OUTER JOIN dbo.MasterEmployee AS C
ON A.EmpCode = C.EmpCode
这是我的代码的结果： 年人保持cf ----------- ------- ----------- ---------- - 2014杰克逊14 6 2014伊娃5 2014 Akson 0 0 2015年10杰克逊6 2015年10伊娃6 2015年13 Akson 6 我想是这样的结果： 今年人们仍然比照 ----------- --- ---- ----------- ----------- 2014杰克逊14 0 2014伊娃5 0 2014阿克森0 0 2015杰克逊10 6 2015伊娃10 5 2015阿克森13 0 2016杰克逊10 6 2016 eva 10 6 2016阿克森13 6
您在每一行中仅具有当前年份的值。并且您的计算需要访问上一年的值。
您可以通过FiscalYear-1这样的联接获取上一年的值
现在，您可以在行级别同时获得当前（B_1）和前一年（B_2），并且可以在计算中使用它。
SELECT
*
FROM dbo.DataLeaveBalance AS A
LEFT OUTER JOIN dbo.VWLeave_takens AS B_1
ON A.FiscalYear = B_1.AffectFY
AND A.EmpCode = B_1.EmpCode
LEFT OUTER JOIN dbo.VWLeave_takens AS B_2
ON (A.FiscalYear-1) = B_1.AffectFY
AND A.EmpCode = B_1.EmpCode
LEFT OUTER JOIN dbo.MasterEmployee AS C
ON A.EmpCode = C.EmpCode
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句