Date Range

[andysk]

Hi All,

i plan to create a table to hold Employee Leave information.
The field are as follows :
- Employee ID
- Date From
- Date To
- Reason

What i concern is related to date range such as :
How to set its primary key? Since the data for date can be overlapped.

Anybody can help?

Thanks,
Andy

 

[r937]

PRIMARY KEY ( EmployeeID, DateFrom )

this will prevent two leaves for the same employee starting on the same day

it will not prevent overlaps -- you can't prevent overlaps with just the PK or even with UNIQUE keys

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[andysk]

hi r937,

Thanks for your info, do you have other way to prevent double/overlap data?

 

[r937]

              proposed      proposed
                from           to            
                  |             |               
1      from---to  |             |               
                  |             |               
2           from--|--to         |               
                  |             |               
3                 |  from---to  |               
                  |             |               
4          from---|-------------|---to       
                  |             |               
5                 |       from--|--to 
                  |             |               
6                 |             |  from---to

in this diagram, the from---to lines indicate existing leaves, while the proposed from and to are the values you're trying to insert

what you want is none of the cases 2 through 5 to exist, and you can do this with

CREATE TABLE leaves 
( ...
 CHECK ( NOT EXISTS
         ( SELECT 1 
             FROM leaves AS L1
            WHERE L1.to >= leaves.from   /* case 1 */
              AND L1.from <= leaves.end  /* case 6 */ )
       )

this might be tricky to understand at first because it involves a double negative

the WHERE clause in the subquery will find all cases 2 through 5, and then the NOT EXISTS will allow the CHECK to work as you wanted




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[andysk]

hi r937,

sorry for late reply. i have tried to add constraint to check the table as shown in your code, however, the sql server reply "Subqueries are not supported in CHECK constraints, table 'testtable'.". my sql server version is 2000.
Any other way ?

Thanks,
Andy

 

[r937]

not that i can think of -- at least not for preventing overlap

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon