Creating a Date Comparing SQL! Pls Help 1

[Forri]

Hi all

Ok i have no idea where to start so here it goes:

I have a table for Leave Applications where i store the First_Day of application and the duration in form of two fields days and hours.

Now when a user adds a new record i have to check if the new date and its duration will overlap with any other record!


Eg: if i insert First_day = '15/04/2004' with a duration of 4 days thus making an end date of '19/04/2004' will crash if :

1) i have a record with a First_day '17/04/2004'
2) i have a record with First_day '14/04/2004' having a duration of 2 days
3) have a record with first_day '13/04/2004' and duration of 1 day and 2 hrs where a day is 8 hrs !

From this logic i need to create an SQL ??!! any help pls! even pointer will be helpful!

Hope i made myself clear!

Thanks
Nick

 

[NVSbe]

Use UNIX_TIMESTAMP. Check this page:

http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html

If I'm following your logic, I think this might set you on your way:

select * from table where timestamp(begin_date_in_table)+ >= timestamp (new_begindate) AND timestamp(end_date_in_table)+ duration_in_days*86400 + duration_in_hours*3600 <= timestamp (new_begindate)

--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[NVSbe]

Edit: end_date_in_table is of course begin_date_in_table. The added seconds in what follows make it the enddate.

--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[Forri]

Thanks for your response!

But though the logic seems correct its still not working! could it be that the timestamop works only on Unix! i'm working on windows but will be later transfered on a Linux system!

Thanks
ncik

 

[KarveR]

If you store the start date and the duration you will need to do an on-the-fly calculation with regard to start dates overlapping end dates. (clear so far?)

Probably better initially when you submit the data to the table to add the end date instead of the duration.

*heres where I'd store dates as mysql likes em (2004-04-22)

because when you insert, you can do

 $sql="insert into yourtable (person,start,end) values('$who','$start_date',date_add('$start_date', interval '$duration' day))";

more to follow, beermonster got me halfway though answering
** assuming php .. (from php forum)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

You could try the following, which should return all the records which clash with the values newdate and newduration:
[tt]
SELECT *
FROM
tblname
WHERE
first_day
BETWEEN newdate
AND DATE_ADD(newdate,INTERVAL newduration-1 DAY)
OR
DATE_ADD(first_day,INTERVAL days-1+(hours>0) DAY)
BETWEEN newdate
AND DATE_ADD(newdate,INTERVAL newduration-1 DAY)
[/tt]
In your example, case no. 3 has an end date of '2004-04-14', so would not clash, right?

-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

One more thing before I go to bed:

The previous code assumes that duration means the number of days involved, so that a duration value of 1 means that the start-date and end-date are the same. That's my understanding of the term duration.

-----
ALTER world DROP injustice, ADD peace;