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] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-15 : 23:39:40
|
something like this?;With CTE1AS(Select BusinessName, ContactName, AppointmentDate, Row_number() OVER (partition by BusinessName,ContactName ORDER BY AppointmentDate) AS Seq From #Input),CTE2AS(SELECT BusinessName, ContactName, AppointmentDate,Seq,CAST(CONVERT(varchar(11),AppointmentDate,100) AS varchar(max))AS ApptDateGrp,CAST(1 AS int) AS GrpNoFROM CTE1WHERE Seq=1UNION ALLSELECT 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 c1INNER JOIN CTE2 c2ON c2.BusinessName = c1.BusinessNameAND c2.ContactName = c1.ContactNameAND c2.Seq+1 = c1.Seq)SELECT BusinessName, ContactName,AppDateGrpFROM(SELECT ROW_NUMBER() OVER (PARTITION BY BusinessName, ContactName,GrpNo ORDER BY Seq DESC) AS Rn,*FROM CTE2)tWHERE Rn=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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! |
|
|
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 AppointmentDateBusiness1 C1 Nov 12, 2012 To Nov 15, 2012Business1 C1 Dec 15, 2012 To Dec 16, 2012Business1 C2 Nov 15, 2012Business2 C2 Dec 16, 2012 To Dec 17, 2012Business2 C2 Jan 17, 2013Business2 C3 Dec 16, 2012Business3 C3 Dec 16, 2012Business3 C3 Jan 18, 2012 To Jan 19, 2012Thank you again. |
|
|
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 CTE1AS(Select BusinessName, ContactName, AppointmentDate, Row_number() OVER (partition by BusinessName,ContactName ORDER BY AppointmentDate) AS Seq From #Input),CTE2AS(SELECT BusinessName, ContactName, AppointmentDate,Seq,CAST(CONVERT(varchar(11),AppointmentDate,100) AS varchar(max))AS ApptDateGrp,CAST(1 AS int) AS GrpNoFROM CTE1WHERE Seq=1UNION ALLSELECT 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 c1INNER JOIN CTE2 c2ON c2.BusinessName = c1.BusinessNameAND c2.ContactName = c1.ContactNameAND c2.Seq+1 = c1.Seq)SELECT BusinessName, ContactName,AppDateGrpFROM(SELECT ROW_NUMBER() OVER (PARTITION BY BusinessName, ContactName,GrpNo ORDER BY Seq DESC) AS Rn,*FROM CTE2)tWHERE Rn=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
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] |
|
|
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, 2012Business3 C3 Jan 22, 2012 Business3 C3 Jan 25, 2012 Thanks. |
|
|
|
|
|