Prabowo itu Hanggar Prabowo itu Hanggar - 2 months ago 6
SQL Question

SQL Server - create value one field from other field in same View table

I need your help, I don't know exactly name the question. I want create record from other field into one field in the same table of view. but when I create it the value show but from same record.

I have 1 table with 4 field
there are YEAR, PEOPLE, REMAINING, CF

the logic like this :


  1. IF people A in 2014 have remaining value 25 and CF values 0 THEN people A in 2015 will have CF values 6
    .

  2. 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

  3. 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



Here my code :

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



This the result from my code:

year people remain cf
----------- ------- ----------- -----------
2014 Jackson 14 6
2014 Eva 5 5
2014 Akson 0 0
2015 Jackson 10 6
2015 Eva 10 6
2015 Akson 13 6

I want the result like this:

year people remain cf
----------- ------- ----------- -----------
2014 Jackson 14 0
2014 Eva 5 0
2014 Akson 0 0
2015 Jackson 10 6
2015 Eva 10 5
2015 Akson 13 0
2016 Jackson 10 6
2016 Eva 10 6
2016 Akson 13 6

Answer

You only have value of the current year available in each row. And your calculation needs access to the value of the previous year.

You could fetch the values of the previous year with a join like FiscalYear-1

Now you have both the current (B_1) and it's previous (B_2) year available on row level and you can use it in your calculation.

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