How to group by name and running dates only in mysql
0
I would like to make mysql query to group the data by name and consecutive dates but i am not really sure how it can be done.
Date | Name
--------------------------------
2018-10-13 00:00:00 Charles
2018-10-14 00:00:00 Charles
2018-10-15 00:00:00 Charles
2018-10-16 00:00:00 Charles
2018-10-17 00:00:00 Charles
2018-10-18 00:00:00 Charles
2018-12-19 00:00:00 Charles
2018-12-20 00:00:00 Charles
2018-12-21 00:00:00 Charles
2018-12-22 00:00:00 Charles
2018-12-23 00:00:00 Charles
2018-12-24 00:00:00 Charles
2018-12-25 00:00:00 Charles
2018-12-26 00:00:00 Charles
2018-12-27 00:00:00 Charles
2018-12-28 00:00:00 Charles
2018-12-29 00:00:00 Charles
2018-12-30 00:00:00 Charles
2018-12-31 00:00:00 Charles
2019-01-01 00:00:00 Charles
2019-01-02 00:00:00 Charles
2019-01-03 00:00:00 Charles
The expect result like so
Name | Date From | Date To |
----------------------------
Charles | 2018-10-13 | 2018-10-18|
| 2018-12-19 | 2019-01-03|
mysql
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
add a comment |
0
I would like to make mysql query to group the data by name and consecutive dates but i am not really sure how it can be done.
Date | Name
--------------------------------
2018-10-13 00:00:00 Charles
2018-10-14 00:00:00 Charles
2018-10-15 00:00:00 Charles
2018-10-16 00:00:00 Charles
2018-10-17 00:00:00 Charles
2018-10-18 00:00:00 Charles
2018-12-19 00:00:00 Charles
2018-12-20 00:00:00 Charles
2018-12-21 00:00:00 Charles
2018-12-22 00:00:00 Charles
2018-12-23 00:00:00 Charles
2018-12-24 00:00:00 Charles
2018-12-25 00:00:00 Charles
2018-12-26 00:00:00 Charles
2018-12-27 00:00:00 Charles
2018-12-28 00:00:00 Charles
2018-12-29 00:00:00 Charles
2018-12-30 00:00:00 Charles
2018-12-31 00:00:00 Charles
2019-01-01 00:00:00 Charles
2019-01-02 00:00:00 Charles
2019-01-03 00:00:00 Charles
The expect result like so
Name | Date From | Date To |
----------------------------
Charles | 2018-10-13 | 2018-10-18|
| 2018-12-19 | 2019-01-03|
mysql
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27
add a comment |
0
0
0
I would like to make mysql query to group the data by name and consecutive dates but i am not really sure how it can be done.
Date | Name
--------------------------------
2018-10-13 00:00:00 Charles
2018-10-14 00:00:00 Charles
2018-10-15 00:00:00 Charles
2018-10-16 00:00:00 Charles
2018-10-17 00:00:00 Charles
2018-10-18 00:00:00 Charles
2018-12-19 00:00:00 Charles
2018-12-20 00:00:00 Charles
2018-12-21 00:00:00 Charles
2018-12-22 00:00:00 Charles
2018-12-23 00:00:00 Charles
2018-12-24 00:00:00 Charles
2018-12-25 00:00:00 Charles
2018-12-26 00:00:00 Charles
2018-12-27 00:00:00 Charles
2018-12-28 00:00:00 Charles
2018-12-29 00:00:00 Charles
2018-12-30 00:00:00 Charles
2018-12-31 00:00:00 Charles
2019-01-01 00:00:00 Charles
2019-01-02 00:00:00 Charles
2019-01-03 00:00:00 Charles
The expect result like so
Name | Date From | Date To |
----------------------------
Charles | 2018-10-13 | 2018-10-18|
| 2018-12-19 | 2019-01-03|
mysql
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
I would like to make mysql query to group the data by name and consecutive dates but i am not really sure how it can be done.
Date | Name
--------------------------------
2018-10-13 00:00:00 Charles
2018-10-14 00:00:00 Charles
2018-10-15 00:00:00 Charles
2018-10-16 00:00:00 Charles
2018-10-17 00:00:00 Charles
2018-10-18 00:00:00 Charles
2018-12-19 00:00:00 Charles
2018-12-20 00:00:00 Charles
2018-12-21 00:00:00 Charles
2018-12-22 00:00:00 Charles
2018-12-23 00:00:00 Charles
2018-12-24 00:00:00 Charles
2018-12-25 00:00:00 Charles
2018-12-26 00:00:00 Charles
2018-12-27 00:00:00 Charles
2018-12-28 00:00:00 Charles
2018-12-29 00:00:00 Charles
2018-12-30 00:00:00 Charles
2018-12-31 00:00:00 Charles
2019-01-01 00:00:00 Charles
2019-01-02 00:00:00 Charles
2019-01-03 00:00:00 Charles
The expect result like so
Name | Date From | Date To |
----------------------------
Charles | 2018-10-13 | 2018-10-18|
| 2018-12-19 | 2019-01-03|
mysql
mysql
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
asked Jan 3 at 9:05
Electronic CircuitElectronic Circuit
9111
9111
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27
add a comment |
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27
add a comment |
2 Answers
2
active
oldest
votes
0
I suspect that this process is considerably simplified by the introduction of common table expressions, but I haven't got to grips with those yet (a New Year's resolution, perhaps). In the meantime, here's one idea...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(dt DATE NOT NULL PRIMARY KEY);
INSERT INTO my_table VALUES
('2018-10-13'),
('2018-10-14'),
('2018-10-15'),
('2018-10-16'),
('2018-10-17'),
('2018-10-18'),
('2018-12-19'),
('2018-12-20'),
('2018-12-21'),
('2018-12-22'),
('2018-12-23'),
('2018-12-24'),
('2018-12-25'),
('2018-12-26'),
('2018-12-27'),
('2018-12-28'),
('2018-12-29'),
('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03');
SELECT MIN(dt) range_start
, MAX(dt) range_end
FROM
(
SELECT x.*
, CASE WHEN dt = @prev + INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
, @prev := dt
FROM my_table x
, (SELECT @prev := null,@i:=0) vars
ORDER
BY dt
) a
GROUP
BY i;
+-------------+------------+
| range_start | range_end |
+-------------+------------+
| 2018-10-13 | 2018-10-18 |
| 2018-12-19 | 2019-01-03 |
+-------------+------------+
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
add a comment |
0
You can modify this Solution:
select a.name, adate start_date,bdate enddate from
(select min(yourdate) adate,MIN(REPLACE(yourdate,'-','')),yourdate, name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) a
left join
(select max(yourdate) bdate,max(REPLACE(yourdate ,'-','')),yourdate ,name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) b
on SUBSTRING(b.yourdate FROM 1 FOR 4) = SUBSTRING(a.yourdate FROM 1 FOR 4)
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019131%2fhow-to-group-by-name-and-running-dates-only-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
I suspect that this process is considerably simplified by the introduction of common table expressions, but I haven't got to grips with those yet (a New Year's resolution, perhaps). In the meantime, here's one idea...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(dt DATE NOT NULL PRIMARY KEY);
INSERT INTO my_table VALUES
('2018-10-13'),
('2018-10-14'),
('2018-10-15'),
('2018-10-16'),
('2018-10-17'),
('2018-10-18'),
('2018-12-19'),
('2018-12-20'),
('2018-12-21'),
('2018-12-22'),
('2018-12-23'),
('2018-12-24'),
('2018-12-25'),
('2018-12-26'),
('2018-12-27'),
('2018-12-28'),
('2018-12-29'),
('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03');
SELECT MIN(dt) range_start
, MAX(dt) range_end
FROM
(
SELECT x.*
, CASE WHEN dt = @prev + INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
, @prev := dt
FROM my_table x
, (SELECT @prev := null,@i:=0) vars
ORDER
BY dt
) a
GROUP
BY i;
+-------------+------------+
| range_start | range_end |
+-------------+------------+
| 2018-10-13 | 2018-10-18 |
| 2018-12-19 | 2019-01-03 |
+-------------+------------+
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
add a comment |
0
I suspect that this process is considerably simplified by the introduction of common table expressions, but I haven't got to grips with those yet (a New Year's resolution, perhaps). In the meantime, here's one idea...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(dt DATE NOT NULL PRIMARY KEY);
INSERT INTO my_table VALUES
('2018-10-13'),
('2018-10-14'),
('2018-10-15'),
('2018-10-16'),
('2018-10-17'),
('2018-10-18'),
('2018-12-19'),
('2018-12-20'),
('2018-12-21'),
('2018-12-22'),
('2018-12-23'),
('2018-12-24'),
('2018-12-25'),
('2018-12-26'),
('2018-12-27'),
('2018-12-28'),
('2018-12-29'),
('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03');
SELECT MIN(dt) range_start
, MAX(dt) range_end
FROM
(
SELECT x.*
, CASE WHEN dt = @prev + INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
, @prev := dt
FROM my_table x
, (SELECT @prev := null,@i:=0) vars
ORDER
BY dt
) a
GROUP
BY i;
+-------------+------------+
| range_start | range_end |
+-------------+------------+
| 2018-10-13 | 2018-10-18 |
| 2018-12-19 | 2019-01-03 |
+-------------+------------+
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
add a comment |
0
0
0
I suspect that this process is considerably simplified by the introduction of common table expressions, but I haven't got to grips with those yet (a New Year's resolution, perhaps). In the meantime, here's one idea...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(dt DATE NOT NULL PRIMARY KEY);
INSERT INTO my_table VALUES
('2018-10-13'),
('2018-10-14'),
('2018-10-15'),
('2018-10-16'),
('2018-10-17'),
('2018-10-18'),
('2018-12-19'),
('2018-12-20'),
('2018-12-21'),
('2018-12-22'),
('2018-12-23'),
('2018-12-24'),
('2018-12-25'),
('2018-12-26'),
('2018-12-27'),
('2018-12-28'),
('2018-12-29'),
('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03');
SELECT MIN(dt) range_start
, MAX(dt) range_end
FROM
(
SELECT x.*
, CASE WHEN dt = @prev + INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
, @prev := dt
FROM my_table x
, (SELECT @prev := null,@i:=0) vars
ORDER
BY dt
) a
GROUP
BY i;
+-------------+------------+
| range_start | range_end |
+-------------+------------+
| 2018-10-13 | 2018-10-18 |
| 2018-12-19 | 2019-01-03 |
+-------------+------------+
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
I suspect that this process is considerably simplified by the introduction of common table expressions, but I haven't got to grips with those yet (a New Year's resolution, perhaps). In the meantime, here's one idea...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(dt DATE NOT NULL PRIMARY KEY);
INSERT INTO my_table VALUES
('2018-10-13'),
('2018-10-14'),
('2018-10-15'),
('2018-10-16'),
('2018-10-17'),
('2018-10-18'),
('2018-12-19'),
('2018-12-20'),
('2018-12-21'),
('2018-12-22'),
('2018-12-23'),
('2018-12-24'),
('2018-12-25'),
('2018-12-26'),
('2018-12-27'),
('2018-12-28'),
('2018-12-29'),
('2018-12-30'),
('2018-12-31'),
('2019-01-01'),
('2019-01-02'),
('2019-01-03');
SELECT MIN(dt) range_start
, MAX(dt) range_end
FROM
(
SELECT x.*
, CASE WHEN dt = @prev + INTERVAL 1 DAY THEN @i:=@i ELSE @i:=@i+1 END i
, @prev := dt
FROM my_table x
, (SELECT @prev := null,@i:=0) vars
ORDER
BY dt
) a
GROUP
BY i;
+-------------+------------+
| range_start | range_end |
+-------------+------------+
| 2018-10-13 | 2018-10-18 |
| 2018-12-19 | 2019-01-03 |
+-------------+------------+
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
answered Jan 3 at 9:36
StrawberryStrawberry
26.8k83250
26.8k83250
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
add a comment |
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
Is there anyway this can be done without using the mysql variable @ ....because the application that i am going to use to make the report does not accept such variable
– Electronic Circuit
Jan 3 at 9:40
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
As i say, if you're using MySQL 8.0+ then look at CTE
– Strawberry
Jan 3 at 9:44
add a comment |
0
You can modify this Solution:
select a.name, adate start_date,bdate enddate from
(select min(yourdate) adate,MIN(REPLACE(yourdate,'-','')),yourdate, name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) a
left join
(select max(yourdate) bdate,max(REPLACE(yourdate ,'-','')),yourdate ,name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) b
on SUBSTRING(b.yourdate FROM 1 FOR 4) = SUBSTRING(a.yourdate FROM 1 FOR 4)
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
add a comment |
0
You can modify this Solution:
select a.name, adate start_date,bdate enddate from
(select min(yourdate) adate,MIN(REPLACE(yourdate,'-','')),yourdate, name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) a
left join
(select max(yourdate) bdate,max(REPLACE(yourdate ,'-','')),yourdate ,name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) b
on SUBSTRING(b.yourdate FROM 1 FOR 4) = SUBSTRING(a.yourdate FROM 1 FOR 4)
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
add a comment |
0
0
0
You can modify this Solution:
select a.name, adate start_date,bdate enddate from
(select min(yourdate) adate,MIN(REPLACE(yourdate,'-','')),yourdate, name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) a
left join
(select max(yourdate) bdate,max(REPLACE(yourdate ,'-','')),yourdate ,name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) b
on SUBSTRING(b.yourdate FROM 1 FOR 4) = SUBSTRING(a.yourdate FROM 1 FOR 4)
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
You can modify this Solution:
select a.name, adate start_date,bdate enddate from
(select min(yourdate) adate,MIN(REPLACE(yourdate,'-','')),yourdate, name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) a
left join
(select max(yourdate) bdate,max(REPLACE(yourdate ,'-','')),yourdate ,name from your_table GROUP BY SUBSTRING(yourdate FROM 1 FOR 4)) b
on SUBSTRING(b.yourdate FROM 1 FOR 4) = SUBSTRING(a.yourdate FROM 1 FOR 4)
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
answered Jan 3 at 10:01
Vijunav VastivchVijunav Vastivch
3,3621723
3,3621723
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
add a comment |
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
Don't have a time for now. goodluck
– Vijunav Vastivch
Jan 3 at 10:02
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019131%2fhow-to-group-by-name-and-running-dates-only-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You have an error in the data collected, you cannot know whether a date is a start date or an end date.
– David Lemon
Jan 3 at 9:23
@DavidLemon Yes you can
– Strawberry
Jan 3 at 9:27