SQL help requested

[cinnie]

hello gurus

I'm a volunteer with a sports club that hires many students on a part-time
basis. I'm really struggling with some SQL used in their payroll
calculations. Here's the situation, simplified to remove extraneous info:

a) In 2010, Employees are paid every 2 weeks starting on January 12.

b) tblEmpl has fields EmplID (pk), and DateOfBirth.

c) We are obliged to adjust the way employees are paid on the FIRST PAYDATE
of the month FOLLOWING the month in which the Employee turns 18 years old.
(eg: let's say Employee 1234 turns 18 on Feb 18, 2010. I need to identify
the first payday in the NEXT MONTH, namely March 9, 2010.

For each employee who turns 18 in 2010, the SQL needs to show two entries
like... 1234 Mar 9, 2010

Thanks in advance.

 

[John Spencer]

TO calculate the last day of the month when the employee turns 18 use the
DateSerialFunction

DateSerial(Year([DateofBirth])+18,Month([DateOfBirth])+1,0)

If the paydate is greater than the above calculation then the paydate uses the
adjusted method of calculation. If it is less than or equal to the above
date, use the under 18 method of calculation.

If you need just the one date and you have a table of paydates (simplest
solution) then all you need to do is the the minimum date greater than the
calculated date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[cinnie]

Thank you John

I used your ideas in a subquery and they worked well. The DateSerial
function really fits the bill. Also, I was using the difference of each date
and a known payday, mod 14, to determine whether a given date is a payday.
Your suggestion to use a simple table is better.
--
cinnie

TO calculate the last day of the month when the employee turns 18 use the
DateSerialFunction

DateSerial(Year([DateofBirth])+18,Month([DateOfBirth])+1,0)

If the paydate is greater than the above calculation then the paydate uses the
adjusted method of calculation. If it is less than or equal to the above
date, use the under 18 method of calculation.

If you need just the one date and you have a table of paydates (simplest
solution) then all you need to do is the the minimum date greater than the
calculated date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

hello gurus

I'm a volunteer with a sports club that hires many students on a part-time
basis. I'm really struggling with some SQL used in their payroll
calculations. Here's the situation, simplified to remove extraneous info:

a) In 2010, Employees are paid every 2 weeks starting on January 12.

b) tblEmpl has fields EmplID (pk), and DateOfBirth.

c) We are obliged to adjust the way employees are paid on the FIRST PAYDATE
of the month FOLLOWING the month in which the Employee turns 18 years old.
(eg: let's say Employee 1234 turns 18 on Feb 18, 2010. I need to identify
the first payday in the NEXT MONTH, namely March 9, 2010.

For each employee who turns 18 in 2010, the SQL needs to show two entries
like... 1234 Mar 9, 2010

Thanks in advance.

.