Hi guys I need your helpI have 1 table with 4 field there are YEAR, PEOPLE, REMAINING, CFthe logic like this : 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 CF 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 0here 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 62014 Eva 5 52014 Akson 0 02015 Jackson 10 62015 Eva 10 62015 Akson 13 6I want the result like this:year people remain cf----------- ------- ----------- -----------2014 Jackson 14 02014 Eva 5 02014 Akson 0 02015 Jackson 10 62015 Eva 10 52015 Akson 13 02016 Jackson 10 62016 Eva 10 62016 Akson 13 6
take values in current year from last year
Posted - 2015-04-14 : 05:33:34