Calculate how many checks in 365 or 366 days?

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.
LVL 29
sammySeltzerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jmcgOwnerCommented:
Can we assume that you mean checks are cut on 15th and the <b>last day of the month</b> instead of only the 31st of months that have 31 days? Can't you then just count months?

The calculation should look like

 # of checks = 2 x (number of full months) + (start date adjustment) + (end date adjustment)

You have to adjust by 1 if the start date was before the 15th and if the end date is after the 15th.
sammySeltzerAuthor Commented:
Sure, we can make that assumption but what happens if someone started working on last pay period instead of the first pay period or retires after first period instead of last pay period?

Besides, how do we account for leap year given your example solution?
John EsraeloDatabase / SQL DeveloperCommented:
sammy,

Here is another approach;
you have the database table(s) that contain the payment information.
also, each person has an ID or identifier that is uniquely identifying the person
then, all you have to do is filter the records by person / employment status ( in this case retired) and come up with a script such as

select * from payrolltable where person_id in (1,2,3,4,5,....435) and person_status ='retired'  and year(paymentdate) = 2010

and you are done

just a thought.  unless there is more to this than meets the eye

JohnE
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sammySeltzerAuthor Commented:
Hey John,

No way that works.

Just a bit more history on this.

These retirees used to receive 2 checks a month, 15th and 31st.

This amounts to about 24 checks a year.

Then they decided to sue the company, arguing that there are instances, perhaps leap year(?) where they could have received more than 24 checks a year.

So, the best way (that I know of anyway) to solve this problem is to take the latest pay check date, count pay checks for the previous 365 (again or 366 depending on leap year) days and see how many checks and total amount of checks.

So, just to repeat example of earlier, if the retirees last check date is June 31st 2013, then we calculate pay checks (for every two weeks) for the previous 365 days from Jun 31st 2013.

Then we do the same thing for the pay check dates that followed after the last one of 2013 and count 365 or 366 days.

We just keep doing it till we calculate all the pay checks and their total amounts that a retiree has received from latest check down to the date since s/he retired.

That's what we are trying to do and I do not believe it is as easy as it seems.
jmcgOwnerCommented:
There must be some esoteric principle being argued about here that isn't clear from your explanation of the problem. If checks are cut on 15th and last day of the month, it's not "about" 24 checks per year, it's exactly 24 checks per year.

There's definitely a discrepancy if some part of the agreement with retirees can be construed as "every two weeks", since that should amount to 26 checks per year.

Since this is supposedly about SQL, the actual checks cut are perhaps in the database, so you just need to SELECT in a date range.

For the theoretical calculation of checks on some other basis than what was used, I don't see SQL as being able to help.
John EsraeloDatabase / SQL DeveloperCommented:
Sammy,

Perhaps the question is deeper than I thought.  Let me see if I understood your question correctly.
First of all, you are interested in the past and not the future.  Secondly, you need to know how many checks were issued in 2010.  

I am just wondering, why, is it that the sql tsql / statement does not bring you the data-set you are looking for?
Get your count from the returned set.

select count(employee_id), Employee_id, Employee_name
   from payrolltable
      where  person_status ='retired'  and year(paymentdate) = 2010
         group by Employee_id, Employee_name

good luck

JohnE
sammySeltzerAuthor Commented:
Displaying how many checks a retiree gets or can get in a 12 month period based on what exists on the db is easy.

I think that's what you are referring to.

If that was the problem, I would not have posted the problem here.

The issue is trying to either prove that the retirees are correct in arguing that they be getting more checks in a 365 day calendar period or to disprove them.

They cannot be proven by just selecting the check they have already been paid from the db.

The issue is to trying to determine in 365 or 365 day period whether they could have gotten more than the 24 they get based on 12 month calendar period.

I will like to get more expert opinion on this to help me go back to our leaders and make the case that this calculation is  or is not possible.
sammySeltzerAuthor Commented:
John,

Yes, that's my point. This problem is deep.

And yes, we are interested in the past, not the future, nicely put!.

If we are interested in how many checks they get a year and total amount, the code below does that nicely.

SELECT r.ERS_EMPNUM,
              r.firstName,r.lastname,
              YEAR(d.CheckDate) Year,
              CheckCount = COUNT(d.Amount),
              CheckAmount = SUM(d.Amount)
FROM retiree r inner join DFACS_EMPLOYEES d on r.ers_EmpNum = d.ERS_EMPNUM
GROUP BY GROUPING SETS((r.ERS_EMPNUM,r.ERS_EMPNUM,r.firstName,r.lastname,YEAR(d.CheckDate)),(r.ERS_EMPNUM,r.ERS_EMPNUM,r.firstName,r.lastname))

Open in new window


Infact, the code does more than that. It also shows total amount of checks, the year of each check total broken down by all retirees.

However, that is not the issue as I stated in my last post.

The post is the retirees saying they should be getting more than the database shows they get.

The only way we can do that as far I know is rather than do in 12 month calendar year, do it in 365 or 366 calendar year.
jmcgOwnerCommented:
If the retirees payments are based on months, I don't see what the argument is about. If there is some other basis, you need to be more explicit about what that alternate basis is supposed to be.
sammySeltzerAuthor Commented:
What part of my explanation are you not getting?????

The retirees are currently paid on bi-weekly basis.

So, they get 2 checks a month.

The code I posted above does an excellent job of breaking their payments in total for 12 month calendar year, the total amount of those checks.

This comes down to between 24 and 26 checks a year.

THEY, the retirees are the ones SUING the company claiming they should be getting more checks than the 24-26 checks they are getting per year.

The company is trying to determine whether they have a valid point or not.

You CANNOT do that by querying the database and saying to them, "see you are getting between 24 to 26 checks a year. They already know this but they are saying they should be getting more.

The only logical to try to do is calculate by 365 day calendar instead of 12 month calendar.

This may not prove anything but we have no other options of resolving this at the moment.
jmcgOwnerCommented:
On what alternative schedule do the suing retirees claim they should be getting checks?
jmcgOwnerCommented:
What part of my explanation are you not getting?????

The retirees are currently paid on bi-weekly basis.

So, they get 2 checks a month.

That's what I'm not getting: bi-weekly is not the same as 2 checks per month. You're being imprecise about an issue which clearly turns on a precise definition of the obligation to retirees.
John EsraeloDatabase / SQL DeveloperCommented:
Does not matter what "they" say or ask, it is what it is.. There are 2 checks a month and that is it.
They can take that to court all day and that's what the judge is going to say too.
There are 12 months my friend times 2 ..
sammySeltzerAuthor Commented:
Ha ha ha.

That's not up to me or you.

If it is up to me, that's what I would say too but when management asks you to try something, you try it the best you can.

If no one has any answer, I understand but we can't play judge.
John EsraeloDatabase / SQL DeveloperCommented:
Between JMCG and myself;  I believe that there is a misunderstanding on your scenario's facts.
Your topic opening statement / paragraphs contradict themselves.  At one point you have referred to bi-monthly and toward the end of your statement you are bringing in the number of days in a year and moving backward in time.   The latter brings in to the table that you would like to go backward in time considering the number of possible days in a year.  This gives us the impression that you may be taking out the original thought process of bi-monthly and "might be" thinking of bi-weekly.
If this is true then I may be able to assist you with the bi-weekly.
 
let us know otherwise I am sure that there are other experts that may be able to assist you, I am sure.
sammySeltzerAuthor Commented:
Guys, guys, just for your information, I am an expert too.

Look me up if you like.

What I see here is a situation where you have no solution to my problem but rather than admit it and move on, you are accusing me of contradicting myself.

It is probably best that I delete this question.
John EsraeloDatabase / SQL DeveloperCommented:
You are right!
John EsraeloDatabase / SQL DeveloperCommented:
bi-weekly, backward 2015-07-07-21-38-04.png
jmcgOwnerCommented:
We're trying to help you, but we're still in the first stage of clarifying the question so that it's possible to try to come up with a proposed solution. I don't think we were accusing you; we were pleading with you to explain the problem better.
Doug BishopDatabase DeveloperCommented:
Opening sentence: 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.
Subsequently: The retirees are currently paid on bi-weekly basis.
In same post: So, they get 2 checks a month.

That is about as contradictory as a statement can be. The experts are trying to get clarity and you are becoming argumentative. I'm going to stay out of providing a solution, but you need to see it from other's points of view. If they are getting paid twice/month, there is no way there could be an issue. As was said, twice a month = 24 checks/year. There is no way around that. There are no leap months. If it is bi-weekly and the issue is whether or not it is possible there could have been a year (365/366 day period) where there would have been 27 pay periods rather than 26, that is what needs to be determined.

If the latter is the case, provide a known pay date and work back from there every two weeks (assuming pay day is always on the same day of the week) and count how many pay periods there are). However, I think it is a mute point. Like twice/month every two weeks is every two weeks, regardless of whether there was a leap year or not. Sounds like a frivolous lawsuit by some retirees who have too much time on their hands and a lawyer looking for some easy money.
Eric ShermanAccountant/DeveloperCommented:
Just my $.02  on the subject .... In this case, shouldn't the retirees be doing this???  The burden of proof is on their shoulders ... not the company being sued.

Like the other experts ... I'm a bit confused as well.  This sounds like a separate project and not a specific SQL question.  You will probably need a separate db and something like Visual Basic to loop through and decipher the various scenarios that you want to test for.

I've done stuff like this before for clients having to pay commissions based on various criteria and conditions that were established by management.  These things can get really involved and requires Recordset processing loops with long conditional IF statements, etc.

Just my opinion.

ET
sammySeltzerAuthor Commented:
Hi guys,

Ok, I am going to make another attempt at explaining the issue.

Hopefully, I can be clearer this time.

Currently, our retired employees receive two checks twice a month, on the 15th and on the 31st of every month.

This gives a total of 24 checks a year.

I know that one the experts who responded to this question suggested that they do the calculation themselves. I hope this guy is joking because these retires are not technical people.

Anyway, they are suing our company suggesting that there is high possibility that they could be getting more than 24 checks a year.

So, management has asked us to take a different approach to try to see if we can come up with more than 24 checks a year.

What they suggested that we do is take the latest check a retired employee received, count down 365 days or 366 days depending on whether that year is regular year or leap year.

Example, assume that an employee received his/her latest check on June 31st 2015 and his or her first check is June 30, 2014, we need to count down 365 days from the latest check date of June 31, 2015 where the first check is less than or equal June 30, 2014.

Then the next latest check of 2014 is June 15,  2014 and repeat this process till the year 1992.

Sorry, in my previous posts, I did not indicate what year they started receiving checks. That's because this information was not originally provided to us.

I had to go back to ask more questions.

So, from June 31, 2015 till June 30, 2014, we need to count how many checks they received bearing in mind that the checks are issued every 15th and 31st of every month.

Again, normally, we would do this yearly, every 15th and 31st of every year but now we want to try it using days; from latest check received substract 365 or 366 days and see how many checks are receive on the 15th and 31st within 365 or 366 days.

I hope this is a bit clearer. I will be more than happy to answer any questions you may have regarding this new information I have provided.

If it turns out that this is still not clear, then I will delete the question but I hope one of you experts can assist.

Thanks a lot.
jmcgOwnerCommented:
Again, as long as checks are issued on 15th and last day of the month, there is no interval of 1 year, whether leap year or not, that contains anything other than 24 checks.
Eric ShermanAccountant/DeveloperCommented:
>>>>>I know that one the experts who responded to this question suggested that they do the calculation themselves. I hope this guy is joking because these retires are not technical people.
Anyway, they are suing our company suggesting that there is high possibility that they could be getting more than 24 checks a year.
So, management has asked us to take a different approach to try to see if we can come up with more than 24 checks a year.<<<<<

That was me ... etsherman and no I'm not kidding.  The retirees are suing you based on a theory, fact or concept.  They should be made to provide and prove that theory, fact or concept.   If someone says your product does not work then they should provide you with the basis for their assessment.


As jmcg stated ...  >>>>>Again, as long as checks are issued on 15th and last day of the month, there is no interval of 1 year, whether leap year or not, that contains anything other than 24 checks.<<<<<<<

I totally agree with this statement  ... Please explain why this is not correct in your situation.

There is no June 31st!!!

>>>>>Again, normally, we would do this yearly, every 15th and 31st of every year <<<<<

This statement is totally confusing to everyone!!!

ET
John EsraeloDatabase / SQL DeveloperCommented:
Sammy

If you saw my attached screenshot in above it seems like that there were more than  24 checked in 2013

If I understood your question correctly


JohnE
Eric ShermanAccountant/DeveloperCommented:
Are you trying to do a comparison between ...

Semi-Monthly = Twice a month, usually 15th and 31st

AND

Bi-Weekly = Every Two Weeks


ET
John EsraeloDatabase / SQL DeveloperCommented:
I was confused too
There were 2 different directions given
And I went ahead and wrote a script to demonstrate that if we walk backward from the last check for a 365+ until 0 then we may get more that 24 checks a year and that's why I attached the previous screen shot
FYI as far as we all know , leap or no leap year ; bimonthly will produce 24 checks

JohnE
jmcgOwnerCommented:
@John - your script issued checks every other Tuesday, so, yes, that's a schedule that could have 25, 26, or 27 checks in a 1-year interval, varying based on how the 365- or 366-day interval fits against the 364-day period (7 days x 52 weeks). There's always an overhang at one or both ends of the year, so, for instance, if the calendar year and the 1-year pay period year start on the same day, the calendar year will pick up an extra paydate from the following 364-day cycle, so you get 27 checks in the 1-calendar-year period.

No similar opportunity exists for the twice-per month cycle unless you mistakenly apply a 366-day year length to an interval that does not include a 29th of February.
sammySeltzerAuthor Commented:
John,

Is there a chance that I see the code that produced the values in that screenshot?

I guess my question is, is that you attempt at solving my problem is it for something you had done previously?
John EsraeloDatabase / SQL DeveloperCommented:
Hi Sammy,

No actually I did not have that done in the past, and, I was trying to see if that is a solution that you are looking for.
I can post that for you after I return home from work.

JohnE
John EsraeloDatabase / SQL DeveloperCommented:
Sammy,

While during lunch,  just thinking out-loud, did a test with Excel and actually it is not a bad idea since we don't care what's in the DB.
see attached for that..
7-14-2015-12-51-09-PM.png
Doug BishopDatabase DeveloperCommented:
John,

Your chart is for bi-weekly (every two weeks), not semi-monthly (twice/month).
John EsraeloDatabase / SQL DeveloperCommented:
I know that, please read the previous postings.
Especially Sammy's first couple of postings.
sammySeltzerAuthor Commented:
Hi John,

The reason I have not responded to your last post is because I am still trying to figure out some things with management who asked me to do this in the first place.

The biggest thing I am trying to figure out with management is how to subtract the previous checks beginning with the latest check down to the first where the first check is equal to or less than June 30, 2014 counting down 365 days to that June 30, 2014.

I think I see what everyone means as this being confusing.

Take these data below as examples. This data is the actual amount a retiree received in the year 2001.

Fname Lname CheckDate Amount
Joe	Doe	2001-06-15	98.00
Joe	Doe	2001-06-15	1224.41
Joe	Doe	2001-05-31	98.00
Joe	Doe	2001-05-31	1224.41
Joe	Doe	2001-05-15	98.00
Joe	Doe	2001-05-15	1224.41
Joe	Doe	2001-04-30	98.00
Joe	Doe	2001-04-30	1224.41
Joe	Doe	2001-04-13	98.00
Joe	Doe	2001-04-13	1224.41
Joe	Doe	2001-03-30	98.00
Joe	Doe	2001-03-30	1224.41
Joe	Doe	2001-03-15	98.00
Joe	Doe	2001-03-15	1224.41
Joe	Doe	2001-02-28	98.00
Joe	Doe	2001-02-28	1224.41
Joe	Doe	2001-02-15	98.00
Joe	Doe	2001-02-15	1224.41
Joe	Doe	2001-01-31	98.00
Joe	Doe	2001-01-31	1224.41
Joe	Doe	2001-01-12	98.00
Joe	Doe	2001-01-12	1224.41

Open in new window


These data show a retiree by the firstname Joe, last name Doe, CheckDate, and Amount of each checkdate.

Given that the latest check date is 2001-06-15, and using 365 days, subtracting each check date and their amount, is there a possibility that after subtracting the first check of 2001-01-12, is there any days left to account for another check?

It is confusing and that explains why I am having problem solving it because I am not bad at all when it comes to writing sql queries.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmcgOwnerCommented:
The payment schedule shows that checks arrive twice per month, either on, or on the last business day before, the 15th and last day of the month.

The checks are all for the same amounts, despite the fact that the second payment each month may be covering a period of 14 to 17 days (or other variances - for example Mon 15 Jan 2001 was a holiday). There's no appearance of adjustments based on a daily rate; payments occur strictly according to the schedule of twice per month. Because the checks do not vary in their amount even as there are variations in the number of days, there is no single daily rate that can be consistently applied.

Everything you've shown us is consistent with a payment schedule of twice-per-month. Using that schedule, you are effectively ignoring the differences in the number of business days that vary from month to month. It cannot be brought precisely into line with a view that payments should be based on hours, days, or weeks. But there is no discrepancy if the view is based on months or years, since there are always exactly 12 calendar months in a calendar year.
Eric ShermanAccountant/DeveloperCommented:
sammySeltzer ...  Why not do this ...

Take one retiree and manually layout in a spreadsheet the EXACT method that your management is asking you to perform.  It is very difficult to program something for which the exact methods have not been identified.   Sounds like someone has a concept but all of the pieces are not making it here to this discussion.  Sit down with your management and a spreadsheet and one retiree and show us exactly what they want to happen.

ET
sammySeltzerAuthor Commented:
I will like to close this question.

It appears that all the pieces of the puzzle are missing.

Once we can figure out what exactly is required to solve this problem, I will post another thread.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.