SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 date transformation with consecutive days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learnsql123
Starting Member

USA
23 Posts

Posted - 11/15/2012 :  20:51:06  Show Profile  Reply with Quote
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
17608 Posts

Posted - 11/15/2012 :  23:07:02  Show Profile  Reply with Quote
; 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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/15/2012 :  23:39:40  Show Profile  Reply with Quote
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

USA
23 Posts

Posted - 11/16/2012 :  00:17:54  Show Profile  Reply with Quote
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!
Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 11/26/2012 :  16:15:55  Show Profile  Reply with Quote
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

USA
23 Posts

Posted - 11/26/2012 :  16:18:30  Show Profile  Reply with Quote
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)

Singapore
17608 Posts

Posted - 11/26/2012 :  20:08:02  Show Profile  Reply with Quote
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

Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 11/26/2012 :  22:56:44  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000