CAN YOU FIIP/FLOP VARIABLES AND DATA IN CF?
This works in the SQL Query Analyzer. Is there a way to switch it to CF? When I
run it I get the following error.
[SQLServer JDBC Driver][SQLServer]Invalid column name 'DateFrom'.
Because DateFrom is a passed variable.
Select * RENTALS r
Where NOT
Impressive use of your noggin. Hopefully I can explain my problem a little
better. I have changed my data import to create a date record for every night.
Instead of the arrival date and number of nights. I can easily change back it
this doesn't provide the results the best way. Below is some
This looks like a good use of a Tally table. A Tally table is a table that
contains just an ID field with records from 1 to 10,000 (you can use any upper
bound you need). You can then join this table to generate queries based on
ranges.
To test, I created the following table:
ID int
I have 2 tables, one lists properties and the other contains arrival dates and
number of nights for those properties. The common variable is 'unitCode'. The
search parameters provide an arrival date and departure date.
My question. How would you use the arrival date/number of nights to locate
Change it so it has an arrival date and a departure date? :D
On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote:
I have 2 tables, one lists properties and the other contains arrival dates
and number of nights for those properties. The common variable is 'unitCode'.
The search parameters
I would still have dates between the arrival date and newly created departure
date that would need to be handled somehow. Originally, I had looping set up to
create a record for every date but it seemed like a lot of extra processing
that shouldn't be required.
I don't think you can do it in a single SQL statement. At least not
an efficient one.
How about pull the list of reservations that overlap the desired
dates, and then creating an array of dates (from arrival to
departure), and then loop over the recordset and remove dates from
the array that are
Can you not use the SQL operator BETWEEN?
where thedate BETWEEN arrivaldate AND departuredate
?
-Original Message-
From: Doug Bedient [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 28, 2005 2:47 PM
To: CF-Talk
Subject: Re: Query Problem - Brain Cloud
I would still have dates between
Add a vacant field and check to see if vacant is 0 or 1 0 being not
vacant and 1 being vacant?
But the where clause would look something like
Select * from task t
WHERE TO_DATE('#arguments.new_to#','mm/dd/') = t.startDate
AND TO_DATE('#arguments.new_from#','mm/dd/') = t.finishDate
, July 28, 2005 11:47 AM
To: CF-Talk
Subject: Re: Query Problem - Brain Cloud
I would still have dates between the arrival date and newly created
departure date that would need to be handled somehow. Originally, I had
looping set up to create a record for every date
Something like this wouldn't handle them
where arrivaldate = #arrivaldate# and departuredate = #departuredate#
?
Maybe I don't understand what you are trying to do?
On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote:
I would still have dates between the arrival date and newly created departure
?
where thedate BETWEEN arrivaldate AND departuredate
?
-Original Message-
From: Doug Bedient [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 28, 2005 2:47 PM
To: CF-Talk
Subject: Re: Query Problem - Brain Cloud
I would still have dates between the arrival date and newly created
here's a timeline in days (if you don't have fixed-width font, CP
into something that does):
123456789
xxx reservation 1
xxx reservation 2
x reservation 3
q desired range
there are two possible days available: 4 and 6. That's the problem
he's trying to solve.
Then you need a reference table of dates, so you can query what IS NOT
there. You can hit this table like this:
select date
from datetable
where date BETWEEN x AND y
and date NOT IN (select distinct date from reservation where date
between x AND y)
right?
-jc
Barney Boisvert wrote:
Doug Bedient wrote:
I have 2 tables, one lists properties and the other contains arrival dates
and number of nights for those properties. The common variable is 'unitCode'.
The search parameters provide an arrival date and departure date.
My question. How would you use the arrival
15 matches
Mail list logo
