Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date transformation with consecutive days

Author  Topic 

learnsql123
Starting Member

23 Posts

Posted - 2012-11-15 : 20:51:06
I am using MS Sql Server 2008. I have appointments data with Business Name, Contact Name and Appointment Date. I want to transform this data so that the consecutive appointment dates come on the same row for the same business and contact. Please see sample data insert sql code for both input and expected output data. I am also attaching a sql to create Appointment Groups to let you know that all dates for the same AppointmentGrp need to come on the same line. Please advise how this transformation can be achieved using sql.

Thanks for your help.


Create table #Input
(BusinessName 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', 'C2', '11/15/2012'),

('Business2', 'C2', '12/16/2012'),
('Business2', 'C2', '12/17/2012'),

('Business2', 'C3', '12/16/2012'),

('Business3', 'C3', '12/16/2012')

Select * from #Input
order by BusinessName, ContactName


Create table #Output
(BusinessName varchar(20), ContactName varchar(20), AppointmentDate varchar(300))

Insert into #Output
Values
('Business1', 'C1', 'November 12, 13, 14, 15, 2012'),
('Business1', 'C2', 'November 15, 2012'),
('Business2', 'C2', 'December 16, 17, 2012'),
('Business2', 'C3', 'December 16, 2012'),
('Business3', 'C3', 'Decebmer 16, 2012')

Select * from #Output
order by BusinessName, ContactName



Select BusinessName, ContactName, AppointmentDate,
Dense_Rank() OVER (order by BusinessName,ContactName) AS AppointmentGrp
into #input1
From #Input
order by BusinessName, ContactName, AppointmentDate

Select * from #input1


khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-15 : 23:07:02
[code]; with cte as
(
Select BusinessName, ContactName, AppointmentDate,
ApptMth = dateadd(month, datediff(month, 0, AppointmentDate), 0)
From #Input
)
select BusinessName,
ContactName,
AppointmentDate = datename(month, ApptMth)
+ stuff(days, 1, 1, '')
+ ', ' + datename(year, ApptMth)
from (
select distinct BusinessName, ContactName, ApptMth
from cte
) c
cross apply
(
select ', ' + datename(day, AppointmentDate)
from cte x
where x.BusinessName = c.BusinessName
and x.ContactName = c.ContactName
and x.ApptMth = c.ApptMth
for xml path('')
) d (days)
order by BusinessName, ContactName, ApptMth
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-15 : 23:39:40
something like this?


;With CTE1
AS
(
Select BusinessName, ContactName, AppointmentDate,
Row_number() OVER (partition by BusinessName,ContactName ORDER BY AppointmentDate) AS Seq
From #Input
),
CTE2
AS
(
SELECT BusinessName, ContactName, AppointmentDate,Seq,CAST(CONVERT(varchar(11),AppointmentDate,100) AS varchar(max))AS ApptDateGrp,CAST(1 AS int) AS GrpNo
FROM CTE1
WHERE Seq=1

UNION ALL

SELECT c1.BusinessName,c1.ContactName,c1.AppointmentDate,c1.Seq,
CAST(CASE WHEN DATEDIFF(dd,c2.AppointmentDate,c1.AppointmentDate)=1 THEN CONVERT(varchar(11),c2.AppointmentDate,100) + ',' + CONVERT(varchar(11),c1.AppointmentDate,100) ELSE CONVERT(varchar(11),c1.AppointmentDate,100) END AS varchar(max)),
CAST(CASE WHEN DATEDIFF(dd,c2.AppointmentDate,c1.AppointmentDate)=1 THEN c1.GrpNo ELSE c1.GrpNo +1 END AS int)
FROM CTE1 c1
INNER JOIN CTE2 c2
ON c2.BusinessName = c1.BusinessName
AND c2.ContactName = c1.ContactName
AND c2.Seq+1 = c1.Seq
)

SELECT BusinessName, ContactName,AppDateGrp
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY BusinessName, ContactName,GrpNo ORDER BY Seq DESC) AS Rn,*
FROM CTE2
)t
WHERE Rn=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

learnsql123
Starting Member

23 Posts

Posted - 2012-11-16 : 00:17:54
quote:
Originally posted by khtan

; with cte as
(
Select BusinessName, ContactName, AppointmentDate,
ApptMth = dateadd(month, datediff(month, 0, AppointmentDate), 0)
From #Input
)
select BusinessName,
ContactName,
AppointmentDate = datename(month, ApptMth)
+ stuff(days, 1, 1, '')
+ ', ' + datename(year, ApptMth)
from (
select distinct BusinessName, ContactName, ApptMth
from cte
) c
cross apply
(
select ', ' + datename(day, AppointmentDate)
from cte x
where x.BusinessName = c.BusinessName
and x.ContactName = c.ContactName
and x.ApptMth = c.ApptMth
for xml path('')
) d (days)
order by BusinessName, ContactName, ApptMth



KH
[spoiler]Time is always against us[/spoiler]





Works wonderful!
Go to Top of Page

learnsql123
Starting Member

23 Posts

Posted - 2012-11-26 : 16:15:55
How do I convert the Appointment date in the result as start date to end date for consecutive days.

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 again.
Go to Top of Page

learnsql123
Starting Member

23 Posts

Posted - 2012-11-26 : 16:18:30
Getting error:
Invalid column name 'GrpNo'.

I replaced BusinessName with LocationName, but don't know how to resolve GrpNo.

I would appreciate a little more help with this.

Thanks.

quote:
Originally posted by visakh16

something like this?


;With CTE1
AS
(
Select BusinessName, ContactName, AppointmentDate,
Row_number() OVER (partition by BusinessName,ContactName ORDER BY AppointmentDate) AS Seq
From #Input
),
CTE2
AS
(
SELECT BusinessName, ContactName, AppointmentDate,Seq,CAST(CONVERT(varchar(11),AppointmentDate,100) AS varchar(max))AS ApptDateGrp,CAST(1 AS int) AS GrpNo
FROM CTE1
WHERE Seq=1

UNION ALL

SELECT c1.BusinessName,c1.ContactName,c1.AppointmentDate,c1.Seq,
CAST(CASE WHEN DATEDIFF(dd,c2.AppointmentDate,c1.AppointmentDate)=1 THEN CONVERT(varchar(11),c2.AppointmentDate,100) + ',' + CONVERT(varchar(11),c1.AppointmentDate,100) ELSE CONVERT(varchar(11),c1.AppointmentDate,100) END AS varchar(max)),
CAST(CASE WHEN DATEDIFF(dd,c2.AppointmentDate,c1.AppointmentDate)=1 THEN c1.GrpNo ELSE c1.GrpNo +1 END AS int)
FROM CTE1 c1
INNER JOIN CTE2 c2
ON c2.BusinessName = c1.BusinessName
AND c2.ContactName = c1.ContactName
AND c2.Seq+1 = c1.Seq
)

SELECT BusinessName, ContactName,AppDateGrp
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY BusinessName, ContactName,GrpNo ORDER BY Seq DESC) AS Rn,*
FROM CTE2
)t
WHERE Rn=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-26 : 20:08:02
For such case, how would you expect the result to be ?

('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012'),

('Business3', 'C3', '01/22/2012'),
('Business3', 'C3', '01/25/2012')



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

learnsql123
Starting Member

23 Posts

Posted - 2012-11-26 : 22:56:44
quote:
Originally posted by khtan

For such case, how would you expect the result to be ?

('Business3', 'C3', '01/18/2012'),
('Business3', 'C3', '01/19/2012'),

('Business3', 'C3', '01/22/2012'),
('Business3', 'C3', '01/25/2012')



KH
[spoiler]Time is always against us[/spoiler]





Business3 C3 Jan 18, 2012 To Jan 19, 2012
Business3 C3 Jan 22, 2012
Business3 C3 Jan 25, 2012

Thanks.
Go to Top of Page
   

- Advertisement -