[SQL/ACCESS]Reservering checken tussen 2 data en 2 tijden

[Verwijderd]

Ik heb al in verschillende topics gekeken, maar naar mijn idee vond ik daar nergens een oplossing (ergens kwam ik een stukje tegen over OVERLAPS in een query, maar deze werkt niet in Access 2003).

Het zit zo. Ik moet in mijn applicatie die in C# geprogrammeerd is, een form maken met een reserveringsmogelijkheid voor een sportaccommodatie (bijv. een voetbalveld of tennisbaan). Deze moet voor dat een reservering in de database gezet wordt, eerst checken of de ingevoerde begin- en einddatum i.c.m. de ingevoerde begin- en eindtijd niet vallen binnen een reserveringsperiode van een accommodatie die al gereserveerd is.

Een record uit mijn tabel ziet er als volgt uit:

PersoonId	AccommodatieId	Begindatum	Begintijd	Einddatum	Eindtijd
2	2	2-12-2005	14:00	3-12-2005	15:00

Nu is het dus de bedoeling dat d.m.v. een COUNT() gekeken wordt of een ingevoerde combinatie van begin- eindtijd en begin- einddatum bijv ( 2-12-2005, 13:00 of 14:30, 2-12-2005, 14:30 of 15:30) wel of niet een bestaande reservering overlapt. In deze query blijft PersoonId buiten beschouwing en wordt AccommodatieId gebruikt om te counten.

De volgende query werkt deels, maar niet helemaal:

SELECT COUNT(AccommodatieId) FROM GastAccommodatie WHERE GastAccommodatie.Begindatum >= [Begindatum] AND GastAccommodatie.Einddatum <= [Einddatum] AND
(GastAccommodatie.Begintijd >= [Begintijd] OR GastAccommodatie.Eindtijd >= [Eindtijd]) AND GastAccommodatie.AccommodatieId = [AccommodatieId]

BETWEEN leek me geen goede oplossing aangezien ik verschillende kolomen van een datum en verschillende kolommen van een tijd heb.

Heeft iemand ervaringen met dit soort SQL vraagstukken?

Bij voorbaat dank.

[edeboeck]

Mag ik vragen waarom je datum en tijd opsplitst? Persoonlijk kan ik hier geen enkele goede reden voor bedenken. Tenzij je dit echt _moet_ doen, lijkt de meest geschikte oplossing om 1 enkel begindatumtijd en 1 enkel einddatumtijd te hebben en toch je between te gebruiken.

Als dat niet mogelijk is, bereken dan de correcte begindatumtijd op basis van de beginvelden, analoog voor de eindvelden en dan kan je alsnog een between gebruiken.

[Verwijderd]

edeboeck schreef op zondag 19 februari 2006 @ 19:47:
Mag ik vragen waarom je datum en tijd opsplitst? Persoonlijk kan ik hier geen enkele goede reden voor bedenken. Tenzij je dit echt _moet_ doen, lijkt de meest geschikte oplossing om 1 enkel begindatumtijd en 1 enkel einddatumtijd te hebben en toch je between te gebruiken.

Als dat niet mogelijk is, bereken dan de correcte begindatumtijd op basis van de beginvelden, analoog voor de eindvelden en dan kan je alsnog een between gebruiken.

Een ander persoon die het database ontwerp heeft gemaakt, heeft deze keuze gemaakt.
Het probleem is dat aan dit datamodel nu gehouden dient te worden.

Ik zal eens kijken wat mogelijk is door jouw oplossing toe te passen.
Andere suggesties zijn nog steeds meer dan welkom.

In ieder geval bedankt edeboeck, deze oplossing was nog niet in mij opgekomen.

[Verwijderd]

Ik heb het nogmaals geprobeer middels deze query:

SELECT * FROM GastAccommodatie WHERE ([Begindatum] AND [Einddatum] BETWEEN #12/2/2005# AND #12/2/2005#) AND ([Begintijd] OR [Eindtijd] BETWEEN #13:00# AND #15:00#)

Probleem: Ik zeg dat de begin of eindtijd in het tijdsblok 13:00 - 15:00 moet zitten. Toch krijg ik records terug met als begin-/eindtijd: 19:00 - 21:00.

[Verwijderd]

Ik ben er uit gekomen met de volgende code:

SELECT * FROM GastAccommodatie WHERE
(([Begindatum] BETWEEN #12/2/2005# AND #12/2/2005#)
AND ([Einddatum] BETWEEN #12/2/2005# AND #12/2/2005#)
OR ([Begindatum] > #12/2/2005# AND [Einddatum] < #12/2/2005#)
OR ([Begindatum] < #12/2/2005# AND [Einddatum] > #12/2/2005#)
OR ([Begindatum] = #12/2/2005# AND [Einddatum] = #12/2/2005#))
AND (([Begintijd] > #13:30# AND [Eindtijd] < #14:40#)
OR ([Begintijd] < #13:30# AND [Eindtijd] > #14:30#)
OR ([Begintijd] = #13:30# AND [Eindtijd] = #14:30#))

[_Thanatos_]

Een ander persoon die het database ontwerp heeft gemaakt, heeft deze keuze gemaakt.

En die persoon heb je natuurlijk alláng een schop onder zijn hol gegeven?

[Boss]

Eventueel kan je toch ook in je query nog datum & tijd aan elkaar plakken en dan als 1 veld behandelen? Maakt het geheel een stuk overzichtelijker nog.

[shades]

ik heb dit gemaakt voor een applicatie om te kijken of ik een bestaande reservering kan verplaatsen
het is een function voor sql2000 maar je kan hem vast aanpassen

@Zid = id van mijn zaal (jouw accommodatie)
@Rid = id van de reservering
@Start = starttijd van je nieuwe reservering
@Eind = eindtijd van je nieuwe reservering

Wat gebeurt er:
Je doet een count voor alle reserveringen (behalve de te checken want die willen we gaan verplaatsen) die voor die zaal gelden. Komt daat > 0 uit dat kan je de reservering niet verplaatsen.

Jouw stukje is na de tweede AND.

Tis klote dat je geen SQL2000 (msde ofzo) gebruikt want daar kan je veel leuker mee goochelen. Ik zou het wel overwegen. Converteren moet toch niet zo moeilijk zijn en de msde is gewoon gratis dacht ik.

Het is dus wel van belang dat je de datum en tijd als 1 gaan zien.

SQL:

CREATE  FUNCTION DBO.fn_ReserveringOverlappingen
(
@zid    INT
,@rid   INT
,@start SMALLDATETIME
,@eind  SMALLDATETIME
)
RETURNS INT
AS
BEGIN
    DECLARE @overlappingen INT

    SELECT  @overlappingen = COUNT(*)
    FROM    tblReserveringen
    WHERE   (fk_ZaalId = @zid)
        AND (ReserveringId <> @rid)
        AND (
            ( @eind > ReserveringStartTijd AND @eind <= ReserveringEindTijd)
            OR  ( @start >= ReserveringStartTijd AND @start < ReserveringEindTijd)
            OR  ( @start < ReserveringStartTijd AND @eind > ReserveringEindTijd)
            )
        
    RETURN @overlappingen
END

Ombouwen van de 2 velden naar 1 (datum-) veld:

SQL:

declare @tijd1 varchar(5)
set @tijd1 = '10:00'

declare @tijd2 varchar(5)
set @tijd2 = '11:00'

declare @datum smalldatetime
set @datum = '2006-02-20'

print cast((convert(varchar(10), @datum, 112) + ' ' + @tijd1) as smalldatetime)
print cast((convert(varchar(10), @datum, 112) + ' ' + @tijd2) as smalldatetime)

[Verwijderd]

De begin- en eindtijd hoef je alleen te controleren als de begin- en einddatum gelijk zijn. Nu krijg je alleen resultaten als begin <= eindtijd _ook_ op dagen waar begin < einddatum is.

[Lustucru]

Het is efficienter om begintijd met eindtijd ahw gekruist te vergelijken. Twee perioden (begin,eind) en (begin2,eind2) overlappen elkaar als (begin<=eind2) AND (eind>=begin2). Meer vergelijkingen zijn niet nodig.

Omdat je in access datum en tijd gewoon bij elkaar kunt optellen zou je nog iets kunnen fabrieken als (begindatum+begintijd)<=eindereservering etc. Zitten er invoerfouten in waardoor begindatum geen geheel getal is of tijd per ongeluk ook een datum heeft meegekregen dan laat je er eerst een integer resp modulus bewerking op los.