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
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. Ot
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
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]
ottrell [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
&qu
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
, 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, O
27;)
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
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
Ca
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 P
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 overla
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'
-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
>
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
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,
endD
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]
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]
[Subscr
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 nee
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
d 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
Subj
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, startDateFi
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 @yourTabl
to:[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
> )
>
>
23 matches
Mail list logo