periods of dates
-
Not a chance... unless someone here thinks otherwise
. -
Be careful, you may get burn
* Noel -
You tell him Noel - I've been carefully NOT saying what I really want to.....<;-)
Remi - this may be another moo point, so don't worry too much about it!
**ASCII stupid question, get a stupid ANSI !!!** -
It's the first time I see you hold something in. Don't worry for me, I've been burned before.
. -
Remi - is "see hold hold something in" another famous American quote or a typo ?! Now I don't know with you anymore!
<;-)
**ASCII stupid question, get a stupid ANSI !!!** -
Typo this time
. Corrected now. -
thanks, but what is "calendar table with the date range" ?
-
OK, so you did not like solution then
The Calendar table contains each date in the input range (built for each query) or a permanent table with all possible dates for all queries (possibly spanning many years)
So, first build and populate Periods table
CREATE TABLE [Periods]
([id] [int] NOT NULL PRIMARY KEY CLUSTERED,
[start_date] [smalldatetime] NOT NULL,
[end_date] [smalldatetime] NOT NULL)
INSERT INTO Periods (id, start_date, end_date) VALUES(1, '2005-07-01', '2005-07-04')
INSERT INTO Periods (id, start_date, end_date) VALUES(2, '2005-07-05', '2005-07-08')
INSERT INTO Periods (id, start_date, end_date) VALUES(3, '2005-07-13', '2005-07-16')
Next Create and populate Calendar table (just July for our example)
CREATE TABLE [Calendar] ([cal_date] [smalldatetime] NOT NULL)
INSERT INTO [Calendar] (cal_date)
SELECT DATEADD(day,number,'20050701') FROM master.dbo.spt_values WHERE type = 'P' AND number < 31
Now by matching the two tables with the date range we can get how many days in the range accommodation is occupied
SELECT COUNT(*)
FROM Calendar AS C1, Periods AS P1
WHERE C1.cal_date BETWEEN @start_date AND @end_date
AND C1.cal_date BETWEEN P1.start_date AND P1.end_date
Then by applying the total number of days in the date range we can find out the status of the accommodation
SELECT CASE WHEN (DATEDIFF(day,@start_date,@end_date)+1) = COUNT(*)
THEN 'Occupied'
ELSE 'Available'
END
FROM Calendar AS C1, Periods AS P1
WHERE C1.cal_date BETWEEN @start_date AND @end_date
AND C1.cal_date BETWEEN P1.start_date AND P1.end_date
Far away is close at hand in the images of elsewhere.
Anon. -
I was referring to the simple where condition. The calendar option can save you a lot of problems, but there are also other options to solve this problem.
-
Thank you David!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply