Calculate annual leave in SQL.

Question

1.00/5 (1 vote)

See more:

Hi All,

So here is my situation . Each Employee has an annual leave of 30 days every year which is avail only after 6 months of probation . That is whenever an employee joins , after completion of 6 months ie after 180 days his annual leave will be 12 days and from 7th month onwards it will increment 2days every month and after 11 th month it will increment by 6 days .So by 12 th month his total annual leave will be 30 days .

Once he completes 1 year , again the annual leave will be reset to zero and increment 2 days each month and 11 th month by 6 days . So each year it should be reset to zero and increment 2 days each month except for 1st year as the leave will be available only after probation period . Could someone help me please . Thanks in Advance .

What I have tried:

select Autoid,name, DateofJoining
,DATEADD(DAY,365,DateofJoining) AS oneyearcompletion
,GETDATE() AS todaydate
,DATEDIFF(day, DateofJoining, GETDATE()) AS totdays
,DATEDIFF(day, DateofJoining, GETDATE())/365 asyearcount
,case when (DATEDIFF(day, DateofJoining, GETDATE()) < 180 ) then 0 when (DATEDIFF(day, DateofJoining, GETDATE()) >180 )  then '2' end

from PPMS_TBL_EmployeeMaster where Autoid = 1

Posted 24-May-23 18:52pm

Member 12926744

Updated 24-May-23 19:09pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-05-24T19:09:00

Help you with what? Finding a better employer? (Most don't work like that: your leave entitlement starts at the beginning of the new year and any overuse is deducted from your final salary if you leave before the year is up.) The only companies that behave like this are imaginary ones for homework assignments ... and for an assignment, you haven't shown any work that starts to meet what you need to hand in.

While we are more than willing to help those that are stuck, that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]