MySQL query vraagstukje

[Y0ur1]

Ik ben bezig met een MySQL query, onderdeel hiervan is de beschikbaarheid van een bepaald object. Ik moet het aantal dagen dat het object niet beschikbaar is in een bepaalde tijdsperiode weten.

Ik werk met een al bestaande database waar ik niks aan mag/kan passen, hoe onlogisch de dingen misschien zijn.

Er is een availability tabel (waarin tijdsperiodes staan wanneer een object niet beschikbaar is, misleidende tabelnaam dus!) met de volgende velden:

Tabel availability

code:

object_id (#)
begin_date (YYYY-MM-DD)
end_date (YYYY-MM-DD)

Een concreet voorbeeld van wat ik wil:
query: geef mij het aantal dagen dat 1 (object_id) niet beschikbaar is tussen 2007-02-15 en 2007-03-20
met het volgende records in de tabel availability:
object_id: 1 | begin_date: 2007-01-25 | end_date: 2007-02-25 (10 dagen)
object_id: 1 | begin_date: 2007-02-26 | end_date: 2007-02-28 (2 dagen)
object_id: 1 | begin_date: 2007-03-19 | end_date: 2007-03-21 (2 dagen)

Dit zou dus 14 dagen moeten opleveren.

Een periode kan er dus gedeeltelijk of geheel in vallen. Als de opgegeven tijdsperiode er gedeeltelijk in valt dan moeten ook alleen maar de dagen worden opgeteld die er in vallen, als de opgegeven tijdsperiode er geheel in valt dan moeten alle dagen worden opgeteld.


Het is me wel al gelukt om het totaal aantal dagen te krijgen, alleen nu nog met een tijdsperiode:

code:

SELECT objects.object_id , SUM(DATEDIFF(availability.end_date, availability.start_date)) AS aantal_dagen
FROM objects
LEFT JOIN availability ON objects.object_id = availability.object_id
GROUP BY object_id

Iemand een suggestie of iemand die me in de goede richting kan sturen?

[Paul]

Kun je high/low op een datum (of überhaupt in SQL) gebruiken?

SQL:

select high(availability.start_date, 2007-02-15), low(availability.end_date, 2007-03-20) from bla

[Verwijderd]

Hmmm jouw voorganger heeft er niet echt lekker over nagedacht, het was handiger geweest om ipv die vaste datums er gewoon een Unix timestamp in te pleuren.

Is veel handiger met rekenen en vergelijken enzo.

Edit: misschien dat je iets met UNIX_TIMESTAMP(availability.end_date) kan.

[ Voor 15% gewijzigd door Verwijderd op 07-03-2007 18:53 ]

[Verwijderd]

Verwijderd schreef op woensdag 07 maart 2007 @ 18:51:
Hmmm jouw voorganger heeft er niet echt lekker over nagedacht, het was handiger geweest om ipv die vaste datums er gewoon een Unix timestamp in te pleuren.

Is veel handiger met rekenen en vergelijken enzo.

Grote onzin. Ga alsjeblieft niet lopen blaten als je ergens geen verstand van hebt. Natuurlijk is het best formaat om gegevens op te slaan het meest geschikte native formaat van de DBMS, zéker als je vergelijkingen moet gaan doen in queries.

Met GREATEST en LEAST kun je in dit geval waarschijnlijk genoeg doen om je functie werkend te krijgen. Als je de hoogste waarde van de begindatum/begin_date en de laagste waarde van de einddatum/end_date neemt om mee te vergelijken, dan gaat het vast goed.

[Y0ur1]

Verwijderd schreef op woensdag 07 maart 2007 @ 19:07:
[...]

Grote onzin. Ga alsjeblieft niet lopen blaten als je ergens geen verstand van hebt. Natuurlijk is het best formaat om gegevens op te slaan het meest geschikte native formaat van de DBMS, zéker als je vergelijkingen moet gaan doen in queries.

Met GREATEST en LEAST kun je in dit geval waarschijnlijk genoeg doen om je functie werkend te krijgen. Als je de hoogste waarde van de begindatum/begin_date en de laagste waarde van de einddatum/end_date neemt om mee te vergelijken, dan gaat het vast goed.

Ik ben iig al een aardig eind op weg tnx!
Enige waar ik nu mee zit dat ik nu negatieve waardes krijg bij de DATEDIFF omdat veel einddata eerder zijn dan de begin zoekdatum waardoor ook de SUM functie niet goed gaat.

Als de uitkomst van DATEDIFF dus negatief is dan mag hij hem niet optellen, enig idee hoe ik dat voor elkaar krijg?

Deze berekent het aantal beschikbare dagen per availability record van bestaande objecten:

code:

SELECT 
objects.object_id, 
DATEDIFF(LEAST(date_end , ADDDATE(CURDATE(), INTERVAL 31 DAY )), GREATEST(date_start, CURDATE())) AS aantal_dagen
FROM objects
LEFT JOIN availability ON objects.object_id = availability.object_id

[Paul]

Wat is dan je where?

code:

where (begin_date between $begin en $eind) or (end_date between $begin en $eind)

geeft iig alleen resultaten waarvan je dus positieve datediffs krijgt. Ik ga er overigens vanuit dat begin_date kleiner is als end_date

[Fish]

je moet gewoon je formule omdraaien

DATEDIFF(GREATEST(date_start, CURDATE()),LEAST(date_end , ADDDATE(CURDATE(), INTERVAL 31 DAY ))) AS aantal_dagen

[Y0ur1]

fish schreef op woensdag 07 maart 2007 @ 22:12:
je moet gewoon je formule omdraaien

DATEDIFF(GREATEST(date_start, CURDATE()),LEAST(date_end , ADDDATE(CURDATE(), INTERVAL 31 DAY ))) AS aantal_dagen

Dan haal ik de startdatum van de eind datum af, das niet de bedoeling... Dan krijg ik bijna altijd iets negatiefs

[ Voor 6% gewijzigd door Y0ur1 op 07-03-2007 22:21 ]

[Y0ur1]

Paul Nieuwkamp schreef op woensdag 07 maart 2007 @ 22:02:
Wat is dan je where?

code:

where (begin_date between $begin en $eind) or (end_date between $begin en $eind)

geeft iig alleen resultaten waarvan je dus positieve datediffs krijgt. Ik ga er overigens vanuit dat begin_date kleiner is als end_date

Werkt ja maar dan krijg ik niet alle objecten meer omdat er nu die WHERE in zit, als er dan geen record in de availability tabel zit wordt het object niet meer getoond.

[Fish]

Y0ur1 schreef op woensdag 07 maart 2007 @ 22:20:
[...]

Dan haal ik de startdatum van de eind datum af, das niet de bedoeling... Dan krijg ik bijna altijd iets negatiefs

gooi er dan een ' abs( ... ) ' omheen

abs(DATEDIFF(GREATEST(date_start, CURDATE()),LEAST(date_end , ADDDATE(CURDATE(), INTERVAL 31 DAY )))) AS aantal_dagen

[ Voor 18% gewijzigd door Fish op 07-03-2007 22:25 ]

[Y0ur1]

fish schreef op woensdag 07 maart 2007 @ 22:24:
[...]


gooi er dan een ' abs( ... ) ' omheen

abs(DATEDIFF(GREATEST(date_start, CURDATE()),LEAST(date_end , ADDDATE(CURDATE(), INTERVAL 31 DAY )))) AS aantal_dagen

Dat lost het ook niet op... Ik heb opzich een goede query, ik krijg een heleboel positief terug, maar ook negatieve waardes omdat bij de availability records de einddatum vóór de opgevraagde tijdsperiode ligt.

[Fish]

abs .... werkt niet ????????????????????????????????????

je het het niet geprobeert of ?

[Y0ur1]

fish schreef op woensdag 07 maart 2007 @ 22:30:
abs .... werkt niet ????????????????????????????????????

je het het niet geprobeert of ?

Tuurlijk werkt die functie wel, alleen het is niet logisch aangezien wat negatief is niet zomaar positief moet worden omdat een negatief getal in mijn geval niet meeegeteld moet worden.

Ik zal even mijn query en query resultaten posten, denk dat het dan duidelijker wordt, momentje

De query:

SELECT objects.object_id, DATEDIFF(LEAST(availability.end_date,ADDDATE(CURDATE(), INTERVAL 31
DAY)),GREATEST(availability.start_date, CURDATE())) AS aantal_dagen
FROM apartments
LEFT JOIN availability ON objects.object_id = availability.object_id

uitkomst:

object_id       aantal_dagen 	start_date 	end_date
500  		11  		2007-03-05  	2007-03-18
500 		-238 		2007-12-01 	2007-12-29
500 		-51 		2007-01-01 	2007-01-15
500 		7 		2007-03-23 	2007-03-30
224 		31 		2007-02-22 	2007-05-15
224 		-25 		2007-01-10 	2007-02-10
217 		-100 		2006-11-20 	2006-11-27
217 		-93 		2006-11-20 	2006-12-04
217 		-86 		2006-12-04 	2006-12-11

Bij object_id 500 kloppen er 2, en bij 224 1.

[ Voor 54% gewijzigd door Y0ur1 op 07-03-2007 22:54 ]

[Fish]

ik begin hem te snappen ..
werkt ' where begindate => eindate ' niet ?

[ Voor 23% gewijzigd door Fish op 07-03-2007 22:56 ]

[Paul]

Zet er een "greatest( $ingewikkeldebestaandediffdate, 0) omheen?

[Y0ur1]

Paul Nieuwkamp schreef op woensdag 07 maart 2007 @ 23:01:
Zet er een "greatest( $ingewikkeldebestaandediffdate, 0) omheen?

doooo natuurlijk, tnx!

Dit is em geworden, hij werkt!:

SELECT objects.object_id,
SUM(GREATEST(DATEDIFF(LEAST( date_end, ADDDATE(CURDATE(), INTERVAL 31 DAY)), GREATEST(date_start, CURDATE())), 0))
AS 
	aantal_dagen
FROM 
	objects
LEFT JOIN 
	availability ON objects.object_id = availability.object_id
GROUP BY
availability.object_id

tnx iedereen