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/