dcsimg
Results 1 to 3 of 3

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

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    46

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

    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,109

    Re: Check If Date is between period and do calculations

    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:
    Code:
    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)

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    46

    Re: Check If Date is between period and do calculations

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width