[RESOLVED] Check If Date is between period and do calculations

[LeoJb]

Hello,

I have a Table in SQL DB called VNewEntries and the columns are: ID, EmpNo, AppointmentDate, VFrom, VTo, VBasicSalary, VAllowances, DeservedBalanceDays, DeservedBalanceKD.

What I want is to check if the AppointmentDate is between VFrom and VTo and do some calculation to get the DeservedBalanceDays then another calculation to calculate DeservedBalanceKD.

For Example:

Employee has EmpNo 3 and his AppointmentDate is 2018-02-15
VFrom is 2018-02-01
VTo is 2018-02-28
DeservedBalanceDays will be VTo - AppointmentDate +1
DeservedBalanceKD will be (DeservedBalanceDays / 26) * (VBasicSalary + VAllowances)

This will be if the AppointmentDate is between VFrom and VTo, if not he will get 2.5 in DeservedBalanceDays.

Thanks in advance.

[si_the_geek]

Do you want to do this in the database, or just displayed within the application?

Assuming you want it in the database, it will be something like this:

UPDATE VNewEntries
SET  DeservedBalanceDays = CASE WHEN AppointmentDate BETWEEN VFrom AND VTo 
                           THEN DATEDIFF (d, AppointmentDate, VTo)  +1 ELSE 2.5 END
   , DeservedBalanceKD = (CASE WHEN AppointmentDate BETWEEN VFrom AND VTo 
                          THEN DATEDIFF (d, AppointmentDate, VTo)  +1 ELSE 2.5 END
                         )/ 26) * (VBasicSalary + VAllowances)

[LeoJb]

Thanks a lot si_the_geek.

I have changed a little bit in the code which you gave me and its solved.

Thanks for giving me this idea.