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/