| Author |
 Topic  |
|
|
learnsql123
Starting Member
USA
23 Posts |
 Posted - 11/15/2012 : 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
|
Edited by - learnsql123 on 11/15/2012 22:35:07
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/15/2012 : 23:07:02
|
; 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
Time is always against us
|
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47121 Posts |
Posted - 11/15/2012 : 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/
|
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/16/2012 : 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
Time is always against us
Works wonderful! |
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/26/2012 : 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. |
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/26/2012 : 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/
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 11/26/2012 : 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
Time is always against us
|
|
|
|
learnsql123
Starting Member
USA
23 Posts |
Posted - 11/26/2012 : 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
Time is always against us
Business3 C3 Jan 18, 2012 To Jan 19, 2012
Business3 C3 Jan 22, 2012
Business3 C3 Jan 25, 2012
Thanks. |
|
|
| |
Topic |
|
|
|
date transformation with consecutive days
