bringing consecutive days on the same row as start date and end date

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)

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

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.