Solved

How to check the date range not repeate on the exist record?

Posted on 2001-07-10
12
156 Views
Last Modified: 2010-04-06
Hi all,
I have a employee holiday database, its structure is:
EmpNo     StartDay     EndDay     Part

(Part field option:  Morning, Afternoon, AllTheDay)

before post a new record, I need to check its range not repeate on the exist record.

e.g.
already have this record:
1   2001-4-16    2001-4-20    AllTheDay

the user can't add the following records.
1   2001-4-18    2001-4-22    AllTheDay
or
1   2001-4-20    2001-4-20    AllTheDay
or
1   2001-4-20    2001-4-20    Afternoon
??

How to do it?  Thanks.

Regards, supwang
0
Comment
Question by:SupWang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 2

Expert Comment

by:aubs
ID: 6272225
You could run something like  

SELECT * FROM EMPTABLE
WHERE ((:START BETWEEN STARTDAY AND ENDDAY)
OR (:FINISH BETWEEN STARTDAY AND ENDDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)


Fill in the parameters at run time with the proposed new holiday bits:
START
FINISH
PART
EMPNO

If you get any records back, then there must be a clash.

regards

Aubs
0
 
LVL 2

Author Comment

by:SupWang
ID: 6272374
Hi Aubs,
Where can I find out the help of "BETWEEN" function?  
and I want to create the TQuery at runtime. Could you give me more codes?

Thanks, supwang
0
 
LVL 2

Expert Comment

by:aubs
ID: 6272416
Check out

http://w3.one.net/~jhoffman/sqltut.htm

Depends how you allow users to select the dates. If you are using a couple of TDateTimPickers you could do
something like this:


with query1 do begin
  Active:= False;
  sql.Clear;
  sql.add('SELECT * FROM EMPTABLE');
  sql.add('WHERE ((:START BETWEEN STARTDAY AND ENDDAY)');
  sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
  sql.add('AND (PART=:PART)');
  sql.add('AND (EMPNO=:EMPNO)');
  ParamByName('START').AsDate:= DateTimePickerStart.Date;
  ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
  ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
  ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
  try
    Active:= True;
    if recordcount>0 then
      showmessage('Duplicate dates');

  finally
     Active:= False;
  end;
end;


Sorry, in a bit of a hurry. This is untested and just written from memory. You may need to do some debugging!

Aubs
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:aubs
ID: 6272546
Check out

http://w3.one.net/~jhoffman/sqltut.htm

Depends how you allow users to select the dates. If you are using a couple of TDateTimPickers you could do
something like this:


with query1 do begin
  Active:= False;
  sql.Clear;
  sql.add('SELECT * FROM EMPTABLE');
  sql.add('WHERE ((:START BETWEEN STARTDAY AND ENDDAY)');
  sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
  sql.add('AND (PART=:PART)');
  sql.add('AND (EMPNO=:EMPNO)');
  ParamByName('START').AsDate:= DateTimePickerStart.Date;
  ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
  ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
  ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
  try
    Active:= True;
    if recordcount>0 then
      showmessage('Duplicate dates');

  finally
     Active:= False;
  end;
end;


Sorry, in a bit of a hurry. This is untested and just written from memory. You may need to do some debugging!

Aubs
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273496
hi

aubs: what about next sample :)

already have this record:
1   2001-4-16    2001-4-20    AllTheDay

your query check out the folowing request and say OK
1   2001-4-10    2001-4-22    AllTheDay



SupWang: do some chages

SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY ENDDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)

Andrey

0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273499
oops!

SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)
0
 
LVL 2

Expert Comment

by:aubs
ID: 6273568
Hey I didn't say it was perfect :-)

You are right Andrey, but your SQL:



                     already have this record:
                     1   2001-4-16    2001-4-20    AllTheDay

                     your query check out the folowing request and say NOT OK!
                     1   2001-4-21    2001-4-22    AllTheDay

A compromise (and a bit messy):

with query1 do begin
                      Active:= False;
                      sql.Clear;
                      sql.add('SELECT * FROM EMPTABLE');
                      sql.add('WHERE ((((:START BETWEEN STARTDAY AND ENDDAY)');
                      sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
                      sql.add('AND (PART=:PART))');
                      sql.add('OR ((STARTDAY BETWEEN :START AND :FINISH)');
                      sql.add('OR (ENDDAY BETWEEN :START AND :FINISH)))');
                      sql.add('AND (EMPNO=:EMPNO)');
                      ParamByName('START').AsDate:= DateTimePickerStart.Date;
                      ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
                      ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
                      ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
                      try
                        Active:= True;
                        if recordcount>0 then
                          showmessage('Duplicate dates');

                      finally
                         Active:= False;
                      end;
                     end;
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6273655
yes

1   2001-4-16    2001-4-20    AllTheDay
1   2001-4-21    2001-4-22    AllTheDay

this records have an intersect...

check the range logic


Andrey
0
 
LVL 2

Expert Comment

by:aubs
ID: 6275971
A better method:

with query1 do begin
                                          Active:= False;
                                          sql.Clear;
                                          sql.add('SELECT * FROM EMPTABLE');
                                          sql.add('WHERE ((((:START BETWEEN STARTDAY AND ENDDAY)');
                                          sql.add('OR (:FINISH BETWEEN STARTDAY AND ENDDAY))');
                                          sql.add('AND (PART=:PART))');
                                          sql.add('OR (:START < STARTDAY AND :FINISH>ENDDAY))');
                                          sql.add('AND (EMPNO=:EMPNO)');
                                          ParamByName('START').AsDate:= DateTimePickerStart.Date;
                                          ParamByName('FINISH').AsDate:= DateTimePickerFinish.Date;
                                          ParamByName('PART').AsString:= edit1.text;//or use a TRadiogroup
                                          ParamByName('EMPNO').AsInteger:= strToInt(edit2.text);
                                          try
                                            Active:= True;
                                            if recordcount>0 then
                                              showmessage('Duplicate dates');

                                          finally
                                             Active:= False;
                                          end;
                                         end;
0
 
LVL 2

Expert Comment

by:Andrey_Kulik
ID: 6276038
SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND (PART=:PART)
AND (EMPNO=:EMPNO)

quite enough
0
 
LVL 2

Author Comment

by:SupWang
ID: 6280530
Hi,
Andrey_Kulik's sql is better. but still not enough.
it can't do the following:

exist record:
2001-4-20    2001-4-20    morning

the user can't insert
2001-4-20    2001-4-20    AllTheDay

but can insert
2001-4-20    2000-4-20    afternoon


more ideas?

Regards, supwang
0
 
LVL 2

Accepted Solution

by:
Andrey_Kulik earned 200 total points
ID: 6280987
SELECT * FROM EMPTABLE
WHERE ((:START > ENDDAY)
OR (:FINISH < STARTDAY))
AND ( (:PART = AllTheDay) OR ((:PART = Morning) AND (PART = Afternoon)) OR ((:PART = Afternoon) AND (PART = Morning)))
AND (EMPNO=:EMPNO)

may be so...

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question