RE: Date Range Query Issues
The situation is a little eccentric, as these are not work shifts, they are on-call shifts. So, a person could have a shift that lasted a whole month. Another thing to note is that while Dr. X's shift ENDS at 8AM 12/5/03, DR. X's BEGINS at 8AM 12/5/03. :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/3/2003 12:51:27 PM >>> To answer your first question, ok, my brain isn't working properly yet. I was including them because I was just automatically thinking that the shift would end at the end of the day not the beginning and therefore if someone chose that day to start there would be an overlap. Using times in the test would have worked with the > and <. I stand corrected again. As for the second question. I really need to get over the flu before I hit reply. You are right. I completely forgot to deal with date ranges that are larger than my select range. Now I am going to go take some Dayquil and admit defeat. Steve -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 10:08 AM To: CF-Talk Subject: Re: Date Range Query Issues DURETTE, STEVEN J (AIT) wrote: > > BEGIN > > declare @yourTable table( > EntryNo int, > startDateField datetime, > endDateField datetime > ) > > insert into @yourTable(entryNo, startDateField, endDateField) values(1, '11-25-2003', '12-15-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(2, '12-31-2003', '01-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(3, '11-25-2003', '12-01-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(4, '1-25-2004', '2-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(5, '12-25-2003', '12-30-2003') > > select * > from @yourTable > where startDateField < '12-31-2003' > and endDateField > '12-01-2003' > > delete from @yourTable > > end > > This worked but missed entries 2 and 3, mine picked them up immediately. It is supposed to miss them. If a persons shift ends at X, why shouldn't the next shift of that person be allowed to start at X either? > I will say this though, for Candace's problem your solution with the added = > is probably better. Even without the added =. Your solution still does not find any that completely envelope other ranges. PostgreSQL shows: jochemd=> BEGIN; jochemd=> CREATE TABLE yourTable ( jochemd(> startDateField TIMESTAMP, jochemd(> endDateField TIMESTAMP); jochemd=> INSERT INTO yourTable jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59'); jochemd=> jochemd=> jochemd=> SELECT * jochemd-> FROM yourtable jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59') jochemd(> OR jochemd(> (endDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59')); startdatefield | enddatefield +-- (0 rows) jochemd=> jochemd=> ROLLBACK; Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
To answer your first question, ok, my brain isn't working properly yet. I was including them because I was just automatically thinking that the shift would end at the end of the day not the beginning and therefore if someone chose that day to start there would be an overlap. Using times in the test would have worked with the > and <. I stand corrected again. As for the second question. I really need to get over the flu before I hit reply. You are right. I completely forgot to deal with date ranges that are larger than my select range. Now I am going to go take some Dayquil and admit defeat. Steve -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 10:08 AM To: CF-Talk Subject: Re: Date Range Query Issues DURETTE, STEVEN J (AIT) wrote: > > BEGIN > > declare @yourTable table( > EntryNo int, > startDateField datetime, > endDateField datetime > ) > > insert into @yourTable(entryNo, startDateField, endDateField) values(1, '11-25-2003', '12-15-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(2, '12-31-2003', '01-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(3, '11-25-2003', '12-01-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(4, '1-25-2004', '2-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(5, '12-25-2003', '12-30-2003') > > select * > from @yourTable > where startDateField < '12-31-2003' > and endDateField > '12-01-2003' > > delete from @yourTable > > end > > This worked but missed entries 2 and 3, mine picked them up immediately. It is supposed to miss them. If a persons shift ends at X, why shouldn't the next shift of that person be allowed to start at X either? > I will say this though, for Candace's problem your solution with the added = > is probably better. Even without the added =. Your solution still does not find any that completely envelope other ranges. PostgreSQL shows: jochemd=> BEGIN; jochemd=> CREATE TABLE yourTable ( jochemd(> startDateField TIMESTAMP, jochemd(> endDateField TIMESTAMP); jochemd=> INSERT INTO yourTable jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59'); jochemd=> jochemd=> jochemd=> SELECT * jochemd-> FROM yourtable jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59') jochemd(> OR jochemd(> (endDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59')); startdatefield | enddatefield +-- (0 rows) jochemd=> jochemd=> ROLLBACK; Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote: > > BEGIN > > declare @yourTable table( > EntryNo int, > startDateField datetime, > endDateField datetime > ) > > insert into @yourTable(entryNo, startDateField, endDateField) values(1, '11-25-2003', '12-15-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(2, '12-31-2003', '01-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(3, '11-25-2003', '12-01-2003') > insert into @yourTable(entryNo, startDateField, endDateField) values(4, '1-25-2004', '2-15-2004') > insert into @yourTable(entryNo, startDateField, endDateField) values(5, '12-25-2003', '12-30-2003') > > select * > from @yourTable > where startDateField < '12-31-2003' > and endDateField > '12-01-2003' > > delete from @yourTable > > end > > This worked but missed entries 2 and 3, mine picked them up immediately. It is supposed to miss them. If a persons shift ends at X, why shouldn't the next shift of that person be allowed to start at X either? > I will say this though, for Candace's problem your solution with the added = > is probably better. Even without the added =. Your solution still does not find any that completely envelope other ranges. PostgreSQL shows: jochemd=> BEGIN; jochemd=> CREATE TABLE yourTable ( jochemd(> startDateField TIMESTAMP, jochemd(> endDateField TIMESTAMP); jochemd=> INSERT INTO yourTable jochemd-> VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59'); jochemd=> jochemd=> jochemd=> SELECT * jochemd-> FROM yourtable jochemd-> WHERE ((startDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59') jochemd(> OR jochemd(> (endDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59')); startdatefield | enddatefield +-- (0 rows) jochemd=> jochemd=> ROLLBACK; Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
Jochem, Running your code on MSSql2K returns: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ';'. Removing the begin; then returns: Server: Msg 2715, Level 16, State 7, Line 1 Column or parameter #1: Cannot find data type DATE. MSSql2K doesn't have the date type, only smalldatetime and datetime both of which include a time in the data stored. I modified the code to run: BEGIN declare @yourTable table( EntryNo int, startDateField datetime, endDateField datetime ) insert into @yourTable(entryNo, startDateField, endDateField) values(1, '11-25-2003', '12-15-2003') insert into @yourTable(entryNo, startDateField, endDateField) values(2, '12-31-2003', '01-15-2004') insert into @yourTable(entryNo, startDateField, endDateField) values(3, '11-25-2003', '12-01-2003') insert into @yourTable(entryNo, startDateField, endDateField) values(4, '1-25-2004', '2-15-2004') insert into @yourTable(entryNo, startDateField, endDateField) values(5, '12-25-2003', '12-30-2003') select * from @yourTable where startDateField < '12-31-2003' and endDateField > '12-01-2003' delete from @yourTable end This worked but missed entries 2 and 3, mine picked them up immediately. Granted just adding = to the > and < in yours would fix this. I will say this though, for Candace's problem your solution with the added = is probably better. When I wrote what I sent out earlier, a co-worker had called me with a similar problem BUT he needed to see overlaps where someone screwed up and had the endDateField before the startDateField. (He was working on a database that someone else had screwed up!) I tried solving both problems at the same time instead of trying to find the best solution for each. So, I stand humbled at your streamlining expertise. Steve -----Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:22 PM To: CF-Talk Subject: Re: Date Range Query Issues DURETTE, STEVEN J (AIT) wrote: > > The problem with your change is that it won't pick up partial overlays. Just run it. We actually need to test it twice, proving that the negator is the same as the operator is too much work for now: BEGIN; CREATE TABLE yourTable ( startDateField DATE, endDateField DATE); INSERT INTO yourTable VALUES ('2003-11-25', '2003-12-15'); SELECT * FROM yourtable WHERE startDateField < '2003-12-31' AND endDateField > '2003-12-01'; TRUNCATE TABLE yourTable; INSERT INTO yourTable VALUES ('2003-12-01', '2003-12-31'); SELECT * FROM yourtable WHERE startDateField < '2003-12-15' AND endDateField > '2003-11-25'; DROP TABLE yourTable; COMMIT; > Mine works if you consider a few things. > 1) the startdate and enddate have to be fully qualified datetimes. > 2) there was one error an extra set of () needed to be included. > > So this works: > > select count(idField) > from yourTable > where type = 'yourtype' > and ((startDateField between '12/01/2003 00:00:00' and '12/31/2003 > 23:59:59') > OR > (endDateField between '12/01/2003 00:00:00' and '12/31/2003 > 23:59:59')) Just run it. One test is enough here :-) BEGIN; CREATE TABLE yourTable ( startDateField TIMESTAMP, endDateField TIMESTAMP); INSERT INTO yourTable VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59'); SELECT * FROM yourtable WHERE ((startDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59') OR (endDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59')); SELECT * FROM yourtable ROLLBACK; > The modified version that you had below had an and in it between the 2 date > tests which would mean that it wouldn't pick up overlays like ("11-25-2003", > "12-15-2003") & ("12-01-2003", "12-31-2003") Just test it :-) > If this doesn't work for you, let me know because it works for me. It doesn't work for me. Please run the above queryies through a command line tool and show us the output. Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Jochem van Dieten wrote: > DURETTE, STEVEN J (AIT) wrote: > >>The problem with your change is that it won't pick up partial overlays. > > Just run it. We actually need to test it twice, proving that the > negator is the same as the operator is too much work for now: On second thought, the proof that I am not selecting too few records is pretty easy. Let A be an interval from Ai to Aj: Ai < Aj. Let B be an interval from Bi to Bj: Bi < Bj. For A and B to overlap, there must be a point in time X that is in both the interval A and the interval B. Ergo: Ai < X < Aj ^ Bi < X < Bj From which follows: Ai < X < Bj ^ Bi < X < Aj We can leave out X: Ai < Bj ^ Bi < Aj And this can be written as: Ai < Bj ^ Aj > Bi This is the exact condition I was testing for in the query. I might be getting too many results back, but not too few. Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Tony Weeg wrote: > sure...where? http://register.microsoft.com/mswish/suggestion.asp?from=cu&fu=%2Fisapi%2Fgomscom%2Easp%3Ftarget%3D%2Fmswish%2Fthanks%2Ehtm > www.microsoft.com/likeTheyCare.cfm You mean you buy software from a company that doesn't care about its customers? Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
sure...where? www.microsoft.com/likeTheyCare.cfm ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 4:50 PM To: CF-Talk Subject: Re: Date Range Query Issues Tony Weeg wrote: > not going to work in sql server 2000, not from what I can see in the > docs File an enhancement request :-) Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Tony Weeg wrote: > not going to work in sql server 2000, not from what I can see in the > docs File an enhancement request :-) Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote: > > The problem with your change is that it won't pick up partial overlays. Just run it. We actually need to test it twice, proving that the negator is the same as the operator is too much work for now: BEGIN; CREATE TABLE yourTable ( startDateField DATE, endDateField DATE); INSERT INTO yourTable VALUES ('2003-11-25', '2003-12-15'); SELECT * FROM yourtable WHERE startDateField < '2003-12-31' AND endDateField > '2003-12-01'; TRUNCATE TABLE yourTable; INSERT INTO yourTable VALUES ('2003-12-01', '2003-12-31'); SELECT * FROM yourtable WHERE startDateField < '2003-12-15' AND endDateField > '2003-11-25'; DROP TABLE yourTable; COMMIT; > Mine works if you consider a few things. > 1) the startdate and enddate have to be fully qualified datetimes. > 2) there was one error an extra set of () needed to be included. > > So this works: > > select count(idField) > from yourTable > where type = 'yourtype' > and ((startDateField between '12/01/2003 00:00:00' and '12/31/2003 > 23:59:59') > OR > (endDateField between '12/01/2003 00:00:00' and '12/31/2003 > 23:59:59')) Just run it. One test is enough here :-) BEGIN; CREATE TABLE yourTable ( startDateField TIMESTAMP, endDateField TIMESTAMP); INSERT INTO yourTable VALUES ('2003-11-25 00:00:00', '2004-12-15 23:59:59'); SELECT * FROM yourtable WHERE ((startDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59') OR (endDateField between '2003-12-01 00:00:00' and '2003-12-31 23:59:59')); SELECT * FROM yourtable ROLLBACK; > The modified version that you had below had an and in it between the 2 date > tests which would mean that it wouldn't pick up overlays like ("11-25-2003", > "12-15-2003") & ("12-01-2003", "12-31-2003") Just test it :-) > If this doesn't work for you, let me know because it works for me. It doesn't work for me. Please run the above queryies through a command line tool and show us the output. Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
Jochem, OVERLAPS isn't a valid MSSql2K TSQL command. I looked it up in the MSSQL Books online. Steve -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 3:38 PM To: CF-Talk Subject: Re: Date Range Query Issues Mickael wrote: > > Could you show me a statement where that is used, it sounds interesting just don't know how it is used. Instead of SELECT x FROM yourtable WHERE startDateField < '#formEndDate#' AND endDateField > #formStartDate#' one would use: SELECT x FROM type = 'checktype' AND (startDateField, endDateField) OVERLAPS ('#formEndDate#', #formStartDate#') Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
Jochem, The problem with your change is that it won't pick up partial overlays. Mine works if you consider a few things. 1) the startdate and enddate have to be fully qualified datetimes. 2) there was one error an extra set of () needed to be included. So this works: select count(idField) from yourTable where type = 'yourtype' and ((startDateField between '12/01/2003 00:00:00' and '12/31/2003 23:59:59') OR (endDateField between '12/01/2003 00:00:00' and '12/31/2003 23:59:59')) The modified version that you had below had an and in it between the 2 date tests which would mean that it wouldn't pick up overlays like ("11-25-2003", "12-15-2003") & ("12-01-2003", "12-31-2003") If this doesn't work for you, let me know because it works for me. Steve -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 3:31 PM To: CF-Talk Subject: Re: Date Range Query Issues DURETTE, STEVEN J (AIT) wrote: > > But you can use between > > select count(idField) > from yourtable > where type = 'checktype' > and (startDateField between 'startdate' and 'enddate' > OR endDateField between 'startdate and 'enddate') That won't work, it won't detect the following overlap: ('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30') You need a statement that is actually even simpler: SELECT COUNT(idField) FROM yourtable WHERE type = 'checktype' AND startDateField < value="#formEndDate#"> AND endDateField > value="#formStartDate#"> Just make sure you verify that the user submitted startdate is less than the user submitted enddate. Jochem -- Who needs virtual reality if you can just dream? - Loesje _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Mickael wrote: > > Could you show me a statement where that is used, it sounds interesting just don't know how it is used. Instead of SELECT x FROM yourtable WHERE startDateField < '#formEndDate#' AND endDateField > #formStartDate#' one would use: SELECT x FROM type = 'checktype' AND (startDateField, endDateField) OVERLAPS ('#formEndDate#', #formStartDate#') Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
DURETTE, STEVEN J (AIT) wrote: > > But you can use between > > select count(idField) > from yourtable > where type = 'checktype' > and (startDateField between 'startdate' and 'enddate' > OR endDateField between 'startdate and 'enddate') That won't work, it won't detect the following overlap: ('2003-12-01', '2003-12-31') & ('2003-12-02', '2003-12-30') You need a statement that is actually even simpler: SELECT COUNT(idField) FROM yourtable WHERE type = 'checktype' AND startDateField < value="#formEndDate#"> AND endDateField > value="#formStartDate#"> Just make sure you verify that the user submitted startdate is less than the user submitted enddate. Jochem -- Who needs virtual reality if you can just dream? - Loesje [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
Thanks guys ;) Now I just have to figure out how screwed (or unscrewed) I am for breaking up the dates. I've got a bit of work ahead of me and I'll let you know how it turns out. Thanks again! Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 12:49:01 PM >>> No, I just tried it and I couldn't get it to work. But you can use between select count(idField) from yourtable where type = 'checktype' and (startDateField between 'startdate' and 'enddate' OR endDateField between 'startdate and 'enddate') Of course change all of the 'variables' to either cfqueryparams in cf or @variables in TSQL. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 12:05 PM To: CF-Talk Subject: RE: Date Range Query Issues not going to work in sql server 2000, not from what I can see in the docs ..tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:22 AM To: CF-Talk Subject: Re: Date Range Query Issues Oh wow... I didnt know about that... I'm using MSSQL 2000... I'll try it out when I get back from my meeting. :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>> Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Hi Jochem, Could you show me a statement where that is used, it sounds interesting just don't know how it is used. Thanks Mike - Original Message - From: Jochem van Dieten To: CF-Talk Sent: Tuesday, December 02, 2003 10:12 AM Subject: Re: Date Range Query Issues Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
No, I just tried it and I couldn't get it to work. But you can use between select count(idField) from yourtable where type = 'checktype' and (startDateField between 'startdate' and 'enddate' OR endDateField between 'startdate and 'enddate') Of course change all of the 'variables' to either cfqueryparams in cf or @variables in TSQL. Steve -Original Message- From: Tony Weeg [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 12:05 PM To: CF-Talk Subject: RE: Date Range Query Issues not going to work in sql server 2000, not from what I can see in the docs ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:22 AM To: CF-Talk Subject: Re: Date Range Query Issues Oh wow... I didnt know about that... I'm using MSSQL 2000... I'll try it out when I get back from my meeting. :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>> Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
not going to work in sql server 2000, not from what I can see in the docs ...tony tony weeg senior web applications architect navtrak, inc. www.navtrak.net [EMAIL PROTECTED] 410.548.2337 -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:22 AM To: CF-Talk Subject: Re: Date Range Query Issues Oh wow... I didnt know about that... I'm using MSSQL 2000... I'll try it out when I get back from my meeting. :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>> Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Oh wow... I didnt know about that... I'm using MSSQL 2000... I'll try it out when I get back from my meeting. :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 10:12:16 AM >>> Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Date Range Query Issues
Candace, If I were you, I would store your dates and times in the db as a datetime field. Then it will become much easier to do the where statement. If you just want to find out if there is an overlap then you could use. select count(identityField) as overlap from yourTable where shiftType = value="#formType#"> and ((startDate >= value="#formStartDate#"> and startDate <= value="#formEndDate#">) or (endDate >= value="#formStartDate#"> and endDate <= value="#formEndDate#">) Of course you will have to make sure that your form dates and times conform to your database. You wouldn't want the user to select 5 (meaning 5pm) and have come in as 5 am. Let me know if you need more clarification. Steve -Original Message- From: Candace Cottrell [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 02, 2003 10:03 AM To: CF-Talk Subject: Re: Date Range Query Issues Hey Jochem :) 1 - Do you mean natively or the way I have the db set up? If it's the latter, I don't have any constraints for overlaps in the db design. Each "shift" or date range is its own record. The id is an identity field. Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 9:57:43 AM >>> > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Candace Cottrell said: > > 1 - Do you mean natively or the way I have the db set up? Natively, OVERLAPS is a SQL predicate: ('2003-12-01', '2003-12-31') OVERLAPS ('2003-12-16', '2004-01-15') Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Hey Jochem :) 1 - Do you mean natively or the way I have the db set up? If it's the latter, I don't have any constraints for overlaps in the db design. Each "shift" or date range is its own record. The id is an identity field. Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] >>> [EMAIL PROTECTED] 12/2/2003 9:57:43 AM >>> > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Date Range Query Issues
Candace Cottrell said: > > Start_Month 12 > Start_Day 1 > Start_Year 2003 > Start_Time 8 > > End_Month 12 > End_Day 15 > End_Year 2003 > End_Time 8 > I know I need to pull a query that brings back the records that > would be overlapped. And if that recordset is empty, proceed with > the insert. Otherwise, give the user a message that there is an > overlap and take them back to the form. > > The problems are: > > 1) I can't figure out what should be in the WHERE clause. Does your database support OVERLAPS? > 2) Have I screwed myself by chopping up the dates? Screwed is not a nice word, but you did make things difficult for yourself. Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Date Range Query Issues
Hello list ;) I have a db table in which I store Start and End Dates called ONCALL_SHIFTS. These are ranges. For example Start_Month 12 Start_Day 1 Start_Year 2003 Start_Time 8 End_Month 12 End_Day 15 End_Year 2003 End_Time 8 There are also two shift types: Backup and Primary So I have a form where the schedulers can add new shifts. It basically asks them for the start and end info, the doctor's name, and what the shift type is. Now, what I want to check for is that there are no overlaps in the shifts, but only if the shift_types are the same; meaning, it's ok to have 2 doctors scheduled for 12/1/2003-12/15/2003 as long as the shift_types are different (backup and primary). It's also ok if one doctor is scheduled for both shift types (ex. Dr. X is both backup and primary on this date range). I know I need to pull a query that brings back the records that would be overlapped. And if that recordset is empty, proceed with the insert. Otherwise, give the user a message that there is an overlap and take them back to the form. The problems are: 1) I can't figure out what should be in the WHERE clause. 2) Have I screwed myself by chopping up the dates? Here's the idea: SELECT Shift_ID Start_Day, Start_Month, Start_Hour, Start_Year FROM ONCALL_SHIFTS WHERE ? INSERT INTO ONCALL_SHIFTS (Start_Day, Start_Month, Start_Hour, Start_Year, Start_AMPM, Schedule_Type_ID, End_Day, End_Month, End_Hour, End_Year, End_AMPM, Doctor_ID, Division_ID ) VALUES ( #Form.Start_Day#, #Form.Start_Month#, #Form.Start_Hour#, #Form.Start_Year#, '#Form.Start_AMPM#', #Form.Schedule_Type_ID#, #Form.End_Day#, #Form.End_Month#, #Form.End_Hour#, #Form.End_Year#, '#Form.End_AMPM#', #Form.Doctor_ID#, #URL.DIVISIONID# ) _javascript_... go back.. blah blah For those of who who have encountered such a problem, what were your experiences?? Any help would be greatly appreciated :) Candace K. Cottrell, Web Developer The Children's Medical Center One Children's Plaza Dayton, OH 45404 937-641-4293 http://www.childrensdayton.org [EMAIL PROTECTED] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]