Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

bringing consecutive days on the same row as start date and end date Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 06, 2013 4:33 PM
Points: 8, Visits: 35
I want group data so that the consecutive days for same Location and Contac tName come on one row as Start date To End date.
If they are non consecutive days for the same location and contact, then they should be on separate rows.
Pls see my sample data and desired output.
How do I do this using Sql.

Thank You.

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 for helping.
Post #1388914
Posted Monday, November 26, 2012 10:21 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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)



~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1388939
Posted Tuesday, November 27, 2012 12:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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/



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1388984
Posted Tuesday, November 27, 2012 12:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
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.


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1388998
Posted Tuesday, November 27, 2012 2:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
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.


Then it is working as requested.

('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

Looking at the data above, only the 12th, 13th, and 14th are consecutive days. The 16th isn't and should be on a line of its own as my code returns. Without the 15th, the 16th isn't a consecutive date.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1389023
Posted Tuesday, November 27, 2012 5:13 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Here's an article that explains the base method that Lynn is using (sans the formatting).
http://www.sqlservercentral.com/articles/T-SQL/71550/


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1389112
Posted Tuesday, November 27, 2012 5:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
Thanks Lynn and Jeff for clarifying
Undoubtably this method seems to be the best fit for consecutive dates!


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1389125
Posted Tuesday, November 27, 2012 5:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:46 AM
Points: 1,371, Visits: 1,547
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).
http://www.sqlservercentral.com/articles/T-SQL/71550/


Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?

Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.


~ Lokesh Vij

Guidelines for quicker answers on T-SQL question
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1389129
Posted Tuesday, November 27, 2012 6:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
Lokesh Vij (11/27/2012)
Jeff Moden (11/27/2012)
Here's an article that explains the base method that Lynn is using (sans the formatting).
http://www.sqlservercentral.com/articles/T-SQL/71550/


Just one doubt: Is this method optimized enough of such problems? or that varies case-to-case basis?

Was trying to compare the execution plan of my query with the one used by Lynn and found that my query was little better. I am sure there will be situations where "Group Island of Contiguous Dates" method will give an optimized performance as compared to other methods.


First, you can't compare the cost of the two plans and say that one is better because of a lower cost. To really know if one is better or not you have to do testing. Comparing the outputs of your query to mine, I would say mine is better because it doesn't erronously report the 16th as the end of a consecutive sequence of dates where yours does. Sorry, but 12, 13, 14, 16 are not consecutive since the 15th is missing.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1389144
Posted Tuesday, November 27, 2012 6:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:53 PM
Points: 22,511, Visits: 30,236
deep3.kaur 98681 (11/26/2012)
I want group data so that the consecutive days for same Location and Contac tName come on one row as Start date To End date.
If they are non consecutive days for the same location and contact, then they should be on separate rows.
Pls see my sample data and desired output.
How do I do this using Sql.

Thank You.

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 for helping.



Just wanted to say thank you and good job for your first post on ssc. It made it very easy to help work your problem.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1389146
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse