If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL help requested
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. -- cinnie |
#2
|
|||
|
|||
SQL help requested
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 wrote: 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. |
#3
|
|||
|
|||
SQL help requested
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 "John Spencer" wrote: 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 wrote: 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. . |
Thread Tools | |
Display Modes | |
|
|