Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Sql query question

2 posts in General Discussion Last posting was on 2004-01-13 16:14:17.0Z
Erik Huisman Posted on 2004-01-13 13:55:28.0Z
Reply-To: "Erik Huisman" <erik.spam.huisman.spam@ishbv.com>
From: "Erik Huisman" <erik.spam.huisman.spam@ishbv.com>
Newsgroups: ianywhere.public.general
Subject: Sql query question
Lines: 28
Organization: ISH
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: p30113.net.upc.nl
Message-ID: <4003faf6$1@forums-2-dub>
X-Original-Trace: 13 Jan 2004 06:04:38 -0800, p30113.net.upc.nl
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 13 Jan 2004 05:40:22 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 13 Jan 2004 05:55:28 -0800
X-Trace: forums-1-dub 1074002128 10.22.108.75 (13 Jan 2004 05:55:28 -0800)
X-Original-Trace: 13 Jan 2004 05:55:28 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2279
Article PK: 4483

I have the following question:

We are developing a roomreservation module for our software.

De database contains a table Space which contains all the spaces (primary
key SpcId varchar(6) and SpcDescr (varchar 41)).

All the reservations are stored in a table SpaceReservation with fields
SpcrStartDate (datetime field) and SpcrEndDate (datetime field), (Primary
key SpcrSpcId varchar(6) foreing key to SpcId, SpcrSeqNo (autoincrement
field)).

I would like a suggestion for a sql query that answers the following
question.

Which rooms are available (between 1 januari 2004 and 10 januari 2004) for
more then 1 hour?

The resultset should be

SpcId + SpcDescr + start datetime space not occupied + end datetime space
not occupied.

Thanks

Erik


Paul Horan[TeamSybase] Posted on 2004-01-13 16:14:17.0Z
From: "Paul Horan[TeamSybase]" <paulhATvcisolutionsDOTcom>
Newsgroups: ianywhere.public.general
References: <4003faf6$1@forums-2-dub>
Subject: Re: Sql query question
Lines: 51
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 67-23-182-204.bflony.adelphia.net
X-Original-NNTP-Posting-Host: 67-23-182-204.bflony.adelphia.net
Message-ID: <40041959$1@forums-1-dub>
Date: 13 Jan 2004 08:14:17 -0800
X-Trace: forums-1-dub 1074010457 67.23.182.204 (13 Jan 2004 08:14:17 -0800)
X-Original-Trace: 13 Jan 2004 08:14:17 -0800, 67-23-182-204.bflony.adelphia.net
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2285
Article PK: 4490

// Show all rows in SPACE where the "next" startDate is more than one hour after its EndDate. If there is no "next"
reservation, then the current reservation must be within 1 hour of "close of business".

SELECT
S1.*,
(Select min( S2.spcrStartDate )
from Space S2
where S2.spcrSpcID = S1.spcrSpcID and
S2.spcrStartDate >= S1.spcrEndDate) as nextAvailableTime
FROM Space S1
WHERE
S1.spcrStartDate between '2004-01-01' and '2004-01-10' and
( HOURS( S1.spcrEndDate, nextAvailableTime ) >= 1
OR
( nextAvailableTime is NULL and CAST( S1.spcrEndDate as TIME ) <= '22:59:59'))
;

--
Paul Horan[TeamSybase]

"Erik Huisman" <erik.spam.huisman.spam@ishbv.com> wrote in message news:4003faf6$1@forums-2-dub...
> I have the following question:
>
> We are developing a roomreservation module for our software.
>
> De database contains a table Space which contains all the spaces (primary
> key SpcId varchar(6) and SpcDescr (varchar 41)).
>
> All the reservations are stored in a table SpaceReservation with fields
> SpcrStartDate (datetime field) and SpcrEndDate (datetime field), (Primary
> key SpcrSpcId varchar(6) foreing key to SpcId, SpcrSeqNo (autoincrement
> field)).
>
> I would like a suggestion for a sql query that answers the following
> question.
>
> Which rooms are available (between 1 januari 2004 and 10 januari 2004) for
> more then 1 hour?
>
> The resultset should be
>
> SpcId + SpcDescr + start datetime space not occupied + end datetime space
> not occupied.
>
> Thanks
>
> Erik
>
>