You must Sign In to post a response.
  • Category: SQL Server

    Increment value depending on months count


    Employee annual leave limit per years is 30. Per month 2.5 is added as a annual leave balance based on his joining date.

    For example say employee completed 3 months his annual leave balance count should be 7.5.

    1st month - 2.5
    2nd month - 5
    3rd month - 7.5

    How to achieve this using sql / .net

  • #770091
    Hi Ayesha,

    You can get it by using below query.

    create table #student
    name varchar(50),
    doj date,
    leavebalance numeric(5,2)

    insert into #student(name,doj) values('Nirav', '2020-06-10')

    select name, convert(varchar(10),doj,103), convert(varchar(10),GETDATE(),103) 'today''s date',
    (datediff(MM,doj,EOMONTH(CAST(YEAR(getdate()) as varchar(4)) + '-' + CAST(MONTH(getdate()) as varchar(10)) + '-01')) * 2.5) 'leave balance'
    from #student

    Hope it will help you to get an understanding.

    Thank you.

    Nirav Lalan
    DNS Gold Member
    "If you can dream it, you can do it."

  • Sign In to post your comments