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
FROM Tally 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 depDate numDays
----------- ---------- ----------- ----------- ---------- -----------
NULL 07/15/05 1 NULL NULL NULL
NULL 07/16/05 1 NULL NULL NULL
NULL 07/17/05 1 NULL NULL NULL
NULL 07/18/05 1 NULL NULL NULL
NULL 07/19/05 1 NULL NULL NULL
2 07/20/05 0 07/20/05 07/24/05 4
2 07/21/05 0 07/20/05 07/24/05 4
2 07/22/05 0 07/20/05 07/24/05 4
2 07/23/05 0 07/20/05 07/24/05 4
NULL 07/24/05 1 NULL NULL NULL
NULL 07/25/05 1 NULL NULL NULL
3 07/26/05 0 07/26/05 07/27/05 1
NULL 07/27/05 1 NULL NULL NULL
1 07/28/05 0 07/28/05 07/30/05 2
1 07/29/05 0 07/28/05 07/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, C&P
into something that does):
123456789
xxx reservation 1
xxx reservation 2
x reservation 3
qqqqq 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
