Greetings again experts.
This may be a tough one but I have a list of retired employees who get paid compensation checks twice a month, 15th and 31st of every month.
Let's assume that an employee retired in 2010 and has been receiving two checks per month as indicated above.
How do I calculate how many checks this employee may have received since 2010?
The way I am told to handle the calculation is to take the latest check the employee received and count down by 365 or 366 days depending on whether the year is leap year or not.
For instance, if the employee received his/her latest check in December 31st of 2013, we would calculate two checks a month for 365 days and determine how many checks received during this period and the total amount of those checks.
Then we do the same for 2012 beginning from the first check received in 2012, calculate 365 days or 366 days depending on whether that year is a leap year.
Example, if the first check was paid to the employee on January 15, 2013, then calculate how many checks paid to the employee 365 or 366 days from January 15, 2013 to the first check received in 2012.
This is a daunting task for me and I don't even know how to begin.
Any assistance from SQL gurus is greatly appreciated.