bringing consecutive days on the same row as start date and end date
-
I want group data so that the consecutive days for same Location and Contac tName come on one row as Start date To End date.
If they are non consecutive days for the same location and contact, then they should be on separate rows.
Pls see my sample data and desired output.
How do I do this using Sql.
Thank You.
Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/15/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
I want the result as:
LocationName ContactName AppointmentDate
Business1 C1 Nov 12, 2012 To Nov 15, 2012
Business1 C1 Dec 15, 2012 To Dec 16, 2012
Business1 C2 Nov 15, 2012
Business2 C2 Dec 16, 2012 To Dec 17, 2012
Business2 C2 Jan 17, 2013
Business2 C3 Dec 16, 2012
Business3 C3 Dec 16, 2012
Business3 C3 Jan 18, 2012 To Jan 19, 2012
Thank You for helping.
-
select LocationName,ContactName,CASE CAST (MIN(AppointmentDate) as VARCHAR(16))
WHEN CAST (MAX(AppointmentDate) as VARCHAR(16)) THEN CAST (MIN(AppointmentDate) as VARCHAR(16))
ELSE
CAST (MIN(AppointmentDate) as VARCHAR(16))+' TO '+CAST (MAX(AppointmentDate) as varchar(16))
end AppointmentDate
from #Input
group by LocationName,ContactName, YEAR(AppointmentDate),MONTH(AppointmentDate)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
This is what you are looking for:
Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/15/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
go
with BaseData as (
select
LocationName,
ContactName,
AppointmentDate,
rn = row_number() over (partition by LocationName, ContactName order by AppointmentDate)
from
#Input
), GroupData as (
select
LocationName,
ContactName,
AppointmentDate,
GroupDate = dateadd(dd, -rn, AppointmentDate)
from
BaseData
)
select
LocationName,
ContactName,
AppointmentDate = convert(varchar(24),min(AppointmentDate), 107) + isnull(' to ' + convert(varchar(24),nullif(max(AppointmentDate),min(AppointmentDate)), 107),'')
from
GroupData
group by
LocationNAme,
ContactName,
GroupDate
;
go
drop table #Input;
go
For more information about the code above, read this article: http://www.sqlservercentral.com/articles/T-SQL/71550/
-
Lynn Pettis (11/27/2012)
This is what you are looking for:Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/15/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
go
with BaseData as (
select
LocationName,
ContactName,
AppointmentDate,
rn = row_number() over (partition by LocationName, ContactName order by AppointmentDate)
from
#Input
), GroupData as (
select
LocationName,
ContactName,
AppointmentDate,
GroupDate = dateadd(dd, -rn, AppointmentDate)
from
BaseData
)
select
LocationName,
ContactName,
AppointmentDate = convert(varchar(24),min(AppointmentDate), 107) + isnull(' to ' + convert(varchar(24),nullif(max(AppointmentDate),min(AppointmentDate)), 107),'')
from
GroupData
group by
LocationNAme,
ContactName,
GroupDate
;
go
drop table #Input;
go
For more information about the code above, read this article: http://www.sqlservercentral.com/articles/T-SQL/71550/
Wonderful Lynn!
An intersting way to do that. But I observed that this method will work only when the dates are in sequence. Try your query on the following set of insert statment:
Create table #Input(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/16/2012'), -- ('Business1', 'C1', '11/15/2012') <<-- Old set of value
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
I have hilighted the changed values.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (11/27/2012)
Lynn Pettis (11/27/2012)
This is what you are looking for:Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/15/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
go
with BaseData as (
select
LocationName,
ContactName,
AppointmentDate,
rn = row_number() over (partition by LocationName, ContactName order by AppointmentDate)
from
#Input
), GroupData as (
select
LocationName,
ContactName,
AppointmentDate,
GroupDate = dateadd(dd, -rn, AppointmentDate)
from
BaseData
)
select
LocationName,
ContactName,
AppointmentDate = convert(varchar(24),min(AppointmentDate), 107) + isnull(' to ' + convert(varchar(24),nullif(max(AppointmentDate),min(AppointmentDate)), 107),'')
from
GroupData
group by
LocationNAme,
ContactName,
GroupDate
;
go
drop table #Input;
go
For more information about the code above, read this article: http://www.sqlservercentral.com/articles/T-SQL/71550/
Wonderful Lynn!
An intersting way to do that. But I observed that this method will work only when the dates are in sequence. Try your query on the following set of insert statment:
Create table #Input(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/16/2012'), -- ('Business1', 'C1', '11/15/2012') <<-- Old set of value
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
I have hilighted the changed values.
Then it is working as requested.
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/16/2012'), -- ('Business1', 'C1', '11/15/2012') <<-- Old set of value
Looking at the data above, only the 12th, 13th, and 14th are consecutive days. The 16th isn't and should be on a line of its own as my code returns. Without the 15th, the 16th isn't a consecutive date.
-
Here's an article that explains the base method that Lynn is using (sans the formatting).
http://www.sqlservercentral.com/articles/T-SQL/71550/
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Lynn and Jeff for clarifying 🙂
Undoubtably this method seems to be the best fit for consecutive dates!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?
Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh Vij (11/27/2012)
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?
Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.
First, you can't compare the cost of the two plans and say that one is better because of a lower cost. To really know if one is better or not you have to do testing. Comparing the outputs of your query to mine, I would say mine is better because it doesn't erronously report the 16th as the end of a consecutive sequence of dates where yours does. Sorry, but 12, 13, 14, 16 are not consecutive since the 15th is missing.
-
deep3.kaur 98681 (11/26/2012)
I want group data so that the consecutive days for same Location and Contac tName come on one row as Start date To End date.If they are non consecutive days for the same location and contact, then they should be on separate rows.
Pls see my sample data and desired output.
How do I do this using Sql.
Thank You.
Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/15/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
I want the result as:
LocationName ContactName AppointmentDate
Business1 C1 Nov 12, 2012 To Nov 15, 2012
Business1 C1 Dec 15, 2012 To Dec 16, 2012
Business1 C2 Nov 15, 2012
Business2 C2 Dec 16, 2012 To Dec 17, 2012
Business2 C2 Jan 17, 2013
Business2 C3 Dec 16, 2012
Business3 C3 Dec 16, 2012
Business3 C3 Jan 18, 2012 To Jan 19, 2012
Thank You for helping.
Just wanted to say thank you and good job for your first post on ssc. It made it very easy to help work your problem.
-
Lokesh Vij (11/27/2012)
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?
Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.
Run the following, you will see even more of a difference between our two solutions.
Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/16/2012'), -- ('Business1', 'C1', '11/15/2012') <<-- Old set of value
('Business1', 'C1', '11/29/2012'), -- This date and the following 3 should be on there own line,
('Business1', 'C1', '11/30/2012'), -- not split between the dates above and below
('Business1', 'C1', '12/01/2012'),
('Business1', 'C1', '12/02/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
go
with BaseData as (
select
LocationName,
ContactName,
AppointmentDate,
rn = row_number() over (partition by LocationName, ContactName order by AppointmentDate)
from
#Input
), GroupData as (
select
LocationName,
ContactName,
AppointmentDate,
GroupDate = dateadd(dd, -rn, AppointmentDate)
from
BaseData
)
select
LocationName,
ContactName,
AppointmentDate = convert(varchar(24),min(AppointmentDate), 107) + isnull(' to ' + convert(varchar(24),nullif(max(AppointmentDate),min(AppointmentDate)), 107),'')
from
GroupData
group by
LocationNAme,
ContactName,
GroupDate
;
go
select LocationName,ContactName,
CASE CAST (MIN(AppointmentDate) as VARCHAR(16))
WHEN CAST (MAX(AppointmentDate) as VARCHAR(16)) THEN CAST (MIN(AppointmentDate) as VARCHAR(16))
ELSE
CAST (MIN(AppointmentDate) as VARCHAR(16))+' TO '+CAST (MAX(AppointmentDate) as varchar(16))
end AppointmentDate
from #Input
group by LocationName,ContactName, YEAR(AppointmentDate),MONTH(AppointmentDate)
go
drop table #Input;
go
-
Lynn Pettis (11/27/2012)
Lokesh Vij (11/27/2012)
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?
Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.
First, you can't compare the cost of the two plans and say that one is better because of a lower cost. To really know if one is better or not you have to do testing. Comparing the outputs of your query to mine, I would say mine is better because it doesn't erronously report the 16th as the end of a consecutive sequence of dates where yours does. Sorry, but 12, 13, 14, 16 are not consecutive since the 15th is missing.
Thanks Lynn. It was a good learning for me. I was challenging you so as to learn 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lynn Pettis (11/27/2012)
Lokesh Vij (11/27/2012)
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?
Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.
Run the following, you will see even more of a difference between our two solutions.
Create table #Input
(LocationName varchar(20), ContactName varchar(20), AppointmentDate date)
Insert into #Input
values
('Business1', 'C1', '11/12/2012'),
('Business1', 'C1', '11/13/2012'),
('Business1', 'C1', '11/14/2012'),
('Business1', 'C1', '11/16/2012'), -- ('Business1', 'C1', '11/15/2012') <<-- Old set of value
('Business1', 'C1', '11/29/2012'), -- This date and the following 3 should be on there own line,
('Business1', 'C1', '11/30/2012'), -- not split between the dates above and below
('Business1', 'C1', '12/01/2012'),
('Business1', 'C1', '12/02/2012'),
('Business1', 'C1', '12/15/2012'),
('Business1', 'C1', '12/16/2012'),
('Business1', 'C2', '11/15/2012'),
('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),
('Business2', 'C2', '01/17/2013'),
('Business2', 'C3', '12/16/2012'),
('Business3', 'C3', '12/16/2012'),
('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012')
go
with BaseData as (
select
LocationName,
ContactName,
AppointmentDate,
rn = row_number() over (partition by LocationName, ContactName order by AppointmentDate)
from
#Input
), GroupData as (
select
LocationName,
ContactName,
AppointmentDate,
GroupDate = dateadd(dd, -rn, AppointmentDate)
from
BaseData
)
select
LocationName,
ContactName,
AppointmentDate = convert(varchar(24),min(AppointmentDate), 107) + isnull(' to ' + convert(varchar(24),nullif(max(AppointmentDate),min(AppointmentDate)), 107),'')
from
GroupData
group by
LocationNAme,
ContactName,
GroupDate
;
go
select LocationName,ContactName,
CASE CAST (MIN(AppointmentDate) as VARCHAR(16))
WHEN CAST (MAX(AppointmentDate) as VARCHAR(16)) THEN CAST (MIN(AppointmentDate) as VARCHAR(16))
ELSE
CAST (MIN(AppointmentDate) as VARCHAR(16))+' TO '+CAST (MAX(AppointmentDate) as varchar(16))
end AppointmentDate
from #Input
group by LocationName,ContactName, YEAR(AppointmentDate),MONTH(AppointmentDate)
go
drop table #Input;
go
This example has brought more clarity now.
Thanks 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply