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.