periods of dates

Question

Post reply

periods of dates

Tomislav-124883

Grasshopper

Points: 18
More actions
June 28, 2005 at 4:58 am

#66714

have table that holds dates when some accommodation is occupied
I have problem to check if that accommodation is occupied between
'2005-07-01' and '2005-07-08'
also when I ask if that accommodation is occupied between
'2005-07-07' and '2005-07-14' select should return that is avaliable, because we have 3 free days...
How to do that select?
--table
CREATE TABLE [Periods] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[fromdate] [smalldatetime] NOT NULL ,
[until] [smalldatetime] NOT NULL ,
CONSTRAINT [PK_Periods] PRIMARY KEY CLUSTERED
(
[id]
&nbsp ON [PRIMARY]
) ON [PRIMARY]
GO
--data
SET IDENTITY_INSERT Periods ON
GO
INSERT INTO Periods (id, fromdate, until)
VALUES(1, '2005-07-01', '2005-07-04')
GO
INSERT INTO Periods (id, fromdate, until)
VALUES(2, '2005-07-05', '2005-07-08')
GO
INSERT INTO Periods (id, fromdate, until)
VALUES(2, '2005-07-13', '2005-07-16')
GO
SET IDENTITY_INSERT Periods OFF
GO

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Can you post the expected output... I'm not sure I understand the question?

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

I think the first query is something like:

select * from periods

where fromdate >= '2005-07-01' and until = 1) -

(select (DATEDIFF(d, '2005-07-07', until) + 1) from periods

where until > '2005-07-07' and fromdate < '2005-07-07') as available

Remi - maybe you can come up with something better ?!

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

...except that the # of available days is 4 and not 3 as poster says...so maybe I don't understand either ?!

**ASCII stupid question, get a stupid ANSI !!!**

Sean Fackrell SSC-Addicted Points: 438 More actions · Answer 4

Think you may need to post some more information on exactly what you need but..

DECLARE @StartDate smalldatetime

DECLARE @EndDate smalldatetime

SET @StartDate = '2005-07-01'

SET @EndDate = '2005-07-04'

Date smalldatetime

IF NOT EXISTS (SELECT * FROM dbo.Periods

WHERE (@StartDate <= fromdate AND @EndDate >= fromdate)
OR (@StartDate >= fromdate AND @StartDate <= until)
)
SELECT 'Available'

ELSE

SELECT 'Not Available'

David Burrows SSC Guru Points: 65142 More actions · Answer 5

DECLARE @fromdate datetime, @until datetime

SET @fromdate = '2005-07-07'

SET @until = '2005-07-14'

SELECT (DATEDIFF(day,@fromdate,@until) + 1) - SUM(DATEDIFF(day,

CASE WHEN @fromdate < fromdate THEN fromdate ELSE @fromdate END,

CASE WHEN @until > until THEN until ELSE @until END

) + 1) AS [Days Available]

FROM #Periods

WHERE (@fromdate >= fromdate and @fromdate <= until)

OR (@until >= fromdate and @until <= until)

Far away is close at hand in the images of elsewhere.
Anon.

Tomislav-124883 Grasshopper Points: 18 More actions · Answer 6

1.for this period

DECLARE @fromdate datetime, @until datetime

SET @fromdate = '2005-07-01'

SET @until = '2005-07-08'

output should be 'Occupied'

2.for this period

DECLARE @fromdate datetime, @until datetime

SET @fromdate = '2005-07-01'

SET @until = '2005-07-10'

output should be 'Available' because there are 2 avaliable days

3.for this period

DECLARE @fromdate datetime, @until datetime

SET @fromdate = '2005-07-01'

SET @until = '2005-07-16'

output should be 'Available' because there are 3 avaliable days

So, if there are only one avaliable day in declared period output should be 'avaliable'

DDL again:

CREATE TABLE [Periodi] (

[id] [int] IDENTITY (1, 1) NOT NULL ,

[od] [smalldatetime] NOT NULL ,

[do] [smalldatetime] NOT NULL ,

CONSTRAINT [PK_Periodi] PRIMARY KEY CLUSTERED

(

[id]

&nbsp ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT Periodi ON

GO

INSERT INTO Periodi (id, od, do)

VALUES(1, '2005-07-01', '2005-07-04')

GO

INSERT INTO Periodi (id, od, do)

VALUES(2, '2005-07-05', '2005-07-08')

GO

INSERT INTO Periodi (id, od, do)

VALUES(3, '2005-07-13', '2005-07-16')

GO

SET IDENTITY_INSERT Periodi OFF

GO

David Burrows SSC Guru Points: 65142 More actions · Answer 7

SELECT CASE

WHEN (DATEDIFF(day,@fromdate,@until) + 1) - SUM(DATEDIFF(day,

CASE WHEN @fromdate < fromdate THEN fromdate ELSE @fromdate END,

CASE WHEN @until > until THEN until ELSE @until END

) + 1) = 0

THEN 'Occupied'

ELSE 'Available'

END

FROM Periodi

WHERE ((@fromdate >= fromdate and @fromdate <= until)

OR (@until >= fromdate and @until <= until))

OR ((fromdate >= @fromdate and fromdate <= @until)

OR (until >= @fromdate and until <= @until))

Far away is close at hand in the images of elsewhere.
Anon.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Here's another thread that discuss a problem very similar to this one, maybe the solution can be applied somehow here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=7&messageid=153524

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

For the lazy reader :

EndDate >= '8/1/04' AND StartDate =< '8/10/04'

noeld SSC Guru Points: 96590 More actions · Answer 10

noeld

SSC Guru

Points: 96590

June 28, 2005 at 1:32 pm

#569119

That's sleek

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Yup, so simple yet so powerfull . That's what sql is all about .

noeld SSC Guru Points: 96590 More actions · Answer 12

Isnt it great that the most simple things tend to be usually the most powerful ones

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

I've always been referred to as a very simple guy, maybe that means something.

sushila SSC-Dedicated Points: 35293 More actions · Answer 14

As long as you're not referred to as a "simpleton" everything's ok! <;-)

**ASCII stupid question, get a stupid ANSI !!!**