Solved

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

Posted on 2001-07-10
12
150 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now