Re: Query Problem - Brain Cloud
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 EXISTS ( Select 1 From PROPERTYCALENDAR p Where unitCode = r.selectUnit And (@DateFrom BETWEEN arrivalDate AND arrivalDate + nights Or @DateTo BETWEEN arrivalDate AND arrivalDate + nights)) ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213881 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 sample data. This seems like it should be so easy compared to some of the stuff I work on but I'm completely blocked. unitCode Date mc 2006-08-11 mc 2006-08-12 mc 2006-08-13 mc 2006-08-14 mc 2006-08-15 mc 2006-08-16 mc 2006-08-17 NS19 2005-08-14 NS19 2005-08-15 NS19 2005-08-16 NS19 2005-08-17 NS19 2005-08-18 If my search dates are (2005-8-12) to (2005-08-13) the result should be just NS19. Obviously the between does not work since both have dates outside the search and therefore they both match. Is there a way to group them so if there is one matched date it does not return that property? Thank you again for any help. Doug ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213778 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 arrivalDate smalldatetime numDays int Then populated the table with this data: ID arrivalDate numDays --- 1 7/28/2005 2 2 7/20/2005 4 3 7/26/2005 1 Finally, I used the following query to determine the available dates: declare @startDate smalldatetime,@endDate smalldatetime select @startDate = '7/15/2005', @endDate = '7/30/2005' SELECT d.ID, dateAdd(day,(t.id -1),@startDate) as tallyDate, (CASE WHEN d.ID IS NULL THEN 1 ELSE 0 END) as isAvailable, d.arrivalDate, dateAdd(day,d.numDays,d.arrivalDate) as depDate, d.numDays FROMTally as t left outer join testDateRange as d on (dateAdd(day,(t.id-1),@startDate) = d.arrivalDate and dateAdd(day,(t.id-1),@startDate) dateAdd(day,numDays,arrivalDate)) WHERE t.ID = dateDiff(day,@startDate,@endDate) ORDER BY tallyDate Which returned the following: ID tallyDate isAvailable arrivalDate depDatenumDays --- -- --- --- -- --- NULL07/15/05 1 NULLNULL NULL NULL07/16/05 1 NULLNULL NULL NULL07/17/05 1 NULLNULL NULL NULL07/18/05 1 NULLNULL NULL NULL07/19/05 1 NULLNULL NULL 2 07/20/05 0 07/20/0507/24/05 4 2 07/21/05 0 07/20/0507/24/05 4 2 07/22/05 0 07/20/0507/24/05 4 2 07/23/05 0 07/20/0507/24/05 4 NULL07/24/05 1 NULLNULL NULL NULL07/25/05 1 NULLNULL NULL 3 07/26/05 0 07/26/0507/27/05 1 NULL07/27/05 1 NULLNULL NULL 1 07/28/05 0 07/28/0507/30/05 2 1 07/29/05 0 07/28/0507/30/05 2 Any row with a NULL id is an available date. The isAvailable col takes this into account and gives a 1 for available and 0 for not. HTH, Chris [EMAIL PROTECTED] 07/28/05 02:27PM 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. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213264 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 provide an arrival date and departure date. My question. How would you use the arrival date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY -- You can't destroy EVERYthing. Where would you sit? The Tick Now blogging http://www.blivit.org/blog/index.cfm http://www.blivit.org/mr_urc/index.cfm ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213194 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
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 but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213196 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 already taken. When you're done, whatever dates are left are available. cheers, barneyb 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 provide an arrival date and departure date. My question. How would you use the arrival date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213202 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem - Brain Cloud
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 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. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213206 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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
On 7/28/05, Doug Bedient [EMAIL PROTECTED] wrote:
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.
~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213209
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Query Problem - Brain Cloud
WHERE date BETWEEN startdate AND enddate HTH -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA C code. C code run. Run code run. Please! - Cynthia Dunning -Original Message- From: Doug Bedient [mailto:[EMAIL PROTECTED] Sent: Thursday, 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 but it seemed like a lot of extra processing that shouldn't be required. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213205 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 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. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213210 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
I've seen issues with support for the between operator. I don't recall offhand which database I've used that didn't support it. If between doesn't work, the paired = = is basically equivalent. On 7/28/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: 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 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. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213212 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213215 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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: 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. cheers, barneyb On 7/28/05, Jennifer Larkin [EMAIL PROTECTED] wrote: Something like this wouldn't handle them where arrivaldate = #arrivaldate# and departuredate = #departuredate# ? Maybe I don't understand what you are trying to do? ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213229 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Query Problem - Brain Cloud
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 date/number of nights to locate availability between two search dates. CFQUERY NAME=Check DATASOURCE=#db# SELECT DISTINCT property_ID FROM Property P LEFT OUTER JOIN propertyCalendar PC ON P.unitCode = PC.unitCode WHERE ??? /CFQUERY I assume you are looking for a property that is available the entire period? In that case: SELECT * FROM Property WHERE unitCode NOT IN ( SELECT unitCode FROM propertyCalendar WHERE (#arrivalDate#, #departureDate#) NOT OVERLAPS (arrivalDate, nights) ) Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:213234 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
