Begin Eind datum lijst met data

Door Matthijs Vos op 01-12-2014 15:49

1.978 views

Hallo Allemaal

Ik heb in een mysql table met daarin data.
Nu wil ik van een serie data de begin en eind datum hebben.

Het lukt me al redelijk met de volgende query:

<
SELECT t1.date as start_date, '' as end_date
FROM test as t1
WHERE t1.date NOT IN(
SELECT DATE_ADD(t2.date, INTERVAL 1 DAY)
FROM test as t2
)

UNION

SELECT '' as start_date, t3.date as end_date
FROM test as t3
WHERE t3.date NOT IN(
SELECT DATE_SUB(t4.date, INTERVAL 1 DAY)
FROM test as t4
)

Echter komt bij dit resultaat nu de begin en eind datum op een verschillende row te staan wat natuurlijk niet praktisch is.

Weet iemand hoe ik dit kan oplossen?

Jan de Laet

01-12-2014 17:54

Als je op zoek bent naar de eerste en laatste datum kan dat toch met


SELECT
 MIN(date) AS start_date,
 MAX(date) AS end_date
FROM test

Ger van Steenderen

01-12-2014 18:52

Mathijs geef eens aan welke gegevens je opslaat, en wat ja als resultaat zou willen hebben.

Matthijs Vos

02-12-2014 09:07 gewijzigd op 02-12-2014 09:08

Ik heb een lijst met data in mijn database. Voorbeeld:
2014-12-01
2014-12-02
2014-12-02
2014-12-03
2014-12-10
2014-12-11

Nu wil ik van de opvolgende data het begin en het einde weten. Het is zoals in het voorbeeld te zien is ook mogelijk dat een datum meerdere keren voorkomt, maar dat moet niet uitmaken. In het voorbeeld wil ik dan als resultaat:
start_datum | eind_datum
2014-12-01 | 2014-12-03
2014-12-10 | 2014-12-11

Ger van Steenderen

02-12-2014 10:54

Een truukje met uservars:


SELECT MIN(adate), MIN(adate) INTO @checkdate, @pointer FROM date_test;

Dan daaroverheen:


SELECT
	start_date,
	MAX(edate) end_date
FROM
	(SELECT
	
	@pointer:= CASE WHEN adate - INTERVAL 1 DAY > @checkdate THEN adate ELSE @pointer END start_date,
    	@checkdate := adate edate
   
	FROM
	date_test ORDER BY adate) d
GROUP BY start_date

Matthijs Vos

28-12-2014 11:03

Bedankt voor je reactie. Ik ben er net weer even mee bezig geweest en het werkt inderdaad. Wat echter het geval is is dat er meerdere gebruikers zijn. Dit conflicteert met elkaar bij deze oplossing. Voorbeeldje:

gebruiker | datum |
-------------------
1 | 2014-12-20
1 | 2014-12-20
1 | 2014-12-21
2 | 2014-12-21
2 | 2014-12-22

Nu krijg ik als result start_date 2014-12-20 en als end_date 2014-12-22. Terwijl ik graag 2 results wil, 1 voor user 1 en eentje voor user 2. Hoe kan ik dit oplossen?

Ger van Steenderen

28-12-2014 18:53

Met een uservar kan je de waarde van een veld uit de vorige rij bij houden.
We gaan de subquery uitbreiden met een controle op de user_id


SELECT
	user_id,
	start_date,
	MAX(end_date) end_date
FROM
	(SELECT
		@pointer:= CASE 
			WHEN user_id <> @user THEN adate
			ELSE
				CASE 
					WHEN adate - INTERVAL 1 DAY > @checkdate THEN adate 
					ELSE @pointer 
				END
			END start_date,
		@checkdate := adate end_date,
		@user := user_id user_id
	FROM
		date_test
	CROSS JOIN (SELECT @user := 0) x
	ORDER BY user_id, adate
	) d
GROUP BY user_id, start_date

[size=xsmall]Toevoeging op 28/12/2014 20:45:10:[/size]

Ik had lokaal al een testje gedaan, voor een werkend voorbeeld zie SQLFiddle

Reageren

Inloggen om te reageren