Begin Eind datum lijst met data
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:
Quote:
<
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
)
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?
Code (php)
1
2
3
4
2
3
4
SELECT
MIN(date) AS start_date,
MAX(date) AS end_date
FROM test
MIN(date) AS start_date,
MAX(date) AS end_date
FROM test
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
Gewijzigd op 02/12/2014 09:08:36 door Matthijs Vos
Code (php)
1
SELECT MIN(adate), MIN(adate) INTO @checkdate, @pointer FROM date_test;
Dan daaroverheen:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
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
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
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?
We gaan de subquery uitbreiden met een controle op de user_id
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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
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
Toevoeging op 28/12/2014 20:45:10:
Ik had lokaal al een testje gedaan, voor een werkend voorbeeld zie SQLFiddle