Hi there,
I need to have a table to keep employees salaries, in which each employee
may have many different salaries on different ranges of dates, with fields of
EmployeeID , StartDate, EndDate, Salary Amount. For example employee A can
have a 2000 $ monthly salary from Jan 1st to Apr 30th , but 2500$ from May
1st to Dec 31st.
Data entry could be though a form or directly from table, but there must be
a control or constrain over validity of date ranges (the span between
StartDate and EndDate) in a way that no two records overlaps each other for
the same employee and also no gap between entries. For example if Employee A
receive a salary from Jan 1st to Mar 3oth , then the same employee can not
have a record of salary for Feb 1st to Apr 30th (overlapping) or Jun 1st to
Dec 31st (leave a gap).
How can I impose such a control for date validation??
I appreciate your help.
--
Thans & Best regards
Leo, InfoSeeker

hi Leo,

On 19.12.2009 07:58, Leo wrote:
> Data entry could be though a form or directly from table, but there must be
> a control or constrain over validity of date ranges (the span between
> StartDate and EndDate) in a way that no two records overlaps each other for
> the same employee and also no gap between entries.
If the constraint 'no gap' is a necessary, significant business rule
then the solution is quite simple:

Use no EndDate. Store only the employee's ID, the salary and the start
date. The end date is determined by either the next records start date,
or by an external date reflecting the employee's state - fired at.

mfG
--> stefan <--

you can find sql query for the same at www.sqlsuperfast.com


> On Saturday, December 19, 2009 1:58 AM Leo wrote:

> Hi there,
> I need to have a table to keep employees salaries, in which each employee
> may have many different salaries on different ranges of dates, with fields of
> EmployeeID , StartDate, EndDate, Salary Amount. For example employee A can
> have a 2000 $ monthly salary from Jan 1st to Apr 30th , but 2500$ from May
> 1st to Dec 31st.
> Data entry could be though a form or directly from table, but there must be
> a control or constrain over validity of date ranges (the span between
> StartDate and EndDate) in a way that no two records overlaps each other for
> the same employee and also no gap between entries. For example if Employee A
> receive a salary from Jan 1st to Mar 3oth , then the same employee can not
> have a record of salary for Feb 1st to Apr 30th (overlapping) or Jun 1st to
> Dec 31st (leave a gap).
> How can I impose such a control for date validation??
> I appreciate your help.
> --
> Thans & Best regards
> Leo, InfoSeeker


>> On Saturday, December 19, 2009 5:00 AM Stefan Hoffmann wrote:

>> hi Leo,
>>
>> On 19.12.2009 07:58, Leo wrote:
>> If the constraint 'no gap' is a necessary, significant business rule
>> then the solution is quite simple:
>>
>> Use no EndDate. Store only the employee's ID, the salary and the start
>> date. The end date is determined by either the next records start date,
>> or by an external date reflecting the employee's state - fired at.
>>
>> mfG
>> --> stefan <--


>> Submitted via EggHeadCafe
>> New Features Of Microsoft Outlook 2010
>> http://www.eggheadcafe.com/tutorials...look-2010.aspx