July 23, 2012 at 2:42 pm
The below script returns this data set.
RowOrgID StartDateEndDate
11219042/4/20086/1/2008
21219048/21/20081/1/2010
31219048/14/20096/30/2010
41219046/30/20106/30/2011
51219048/10/20119/1/2012
61219049/21/20119/21/2012
What I am trying to do is merge the rows where the dates are continuous. So I would wind up with
OrgID StartDateEndDate
1219042/4/20086/1/2008 (Row 1 by itself)
1219048/21/20086/30/2011 (Rows 2-4)
1219048/10/20119/21/2012 (Rows 5-6)
There is an interruption in the service between 6/1/2008 and 8/21/2008 and then again between 6/30/2011 and 8/10/2011.
How would I go about getting the min/max date of the three groups? I'm really having a hard time envisioning in SQL how to create the three groups. I've tried some recursive joins with CTE, but when it comes down to getting the min/max dates of the groups, I wind up with a min date of 2/4/2008 and a max date of 9/21/2012. Anyone have any ideas?
declare @Dates table
([Row] int NOT NULL,
[OrgID] varchar(50) NOT NULL,
[StartDate] date NULL,
[EndDate] date NULL)
Insert into @Dates
Values
(1, '121904', '2/4/2008', '6/1/2008'),
(2, '121904', '8/21/2008', '1/1/2010'),
(3, '121904', '8/14/2009', '6/30/2010'),
(4, '121904', '6/30/2010', '6/30/2011'),
(5, '121904', '8/10/2011', '9/1/2012'),
(6, '121904', '9/21/2011', '9/21/2012')
select * from @Dates
July 23, 2012 at 2:49 pm
adams.squared (7/23/2012)
The below script returns this data set.RowOrgID StartDateEndDate
11219042/4/20086/1/2008
21219048/21/20081/1/2010
31219048/14/20096/30/2010
41219046/30/20106/30/2011
51219048/10/20119/1/2012
61219049/21/20119/21/2012
What I am trying to do is merge the rows where the dates are continuous. So I would wind up with
OrgID StartDateEndDate
1219042/4/20086/1/2008 (Row 1 by itself)
1219048/21/20086/30/2011 (Rows 2-4)
1219048/10/20119/21/2012 (Rows 5-6)
There is an interruption in the service between 6/1/2008 and 8/21/2008 and then again between 6/30/2011 and 8/10/2011.
How would I go about getting the min/max date of the three groups? I'm really having a hard time envisioning in SQL how to create the three groups. I've tried some recursive joins with CTE, but when it comes down to getting the min/max dates of the groups, I wind up with a min date of 2/4/2008 and a max date of 9/21/2012. Anyone have any ideas?
declare @Dates table
([Row] int NOT NULL,
[OrgID] varchar(50) NOT NULL,
[StartDate] date NULL,
[EndDate] date NULL)
Insert into @Dates
Values
(1, '121904', '2/4/2008', '6/1/2008'),
(2, '121904', '8/21/2008', '1/1/2010'),
(3, '121904', '8/14/2009', '6/30/2010'),
(4, '121904', '6/30/2010', '6/30/2011'),
(5, '121904', '8/10/2011', '9/1/2012'),
(6, '121904', '9/21/2011', '9/21/2012')
select * from @Dates
Why is there no interuption between rows 5 and 6?
July 23, 2012 at 3:21 pm
I think you should take a look at this article. http://qa.sqlservercentral.com/articles/T-SQL/71550/[/url]
It seems to be exactly what you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2012 at 3:28 pm
Lynn Pettis (7/23/2012)
Why is there no interuption between rows 5 and 6?
The start date on 6 is before the end date on 5, so it would be continuous.
July 23, 2012 at 3:44 pm
adams.squared (7/23/2012)
Lynn Pettis (7/23/2012)
Why is there no interuption between rows 5 and 6?
The start date on 6 is before the end date on 5, so it would be continuous.
Okay, sorry. When I first looked at it, it looked like the end date on 5 was 2011 not 2012. That's what happens when you don't look close enough.
July 24, 2012 at 7:53 am
Sean Lange (7/23/2012)
I think you should take a look at this article. http://qa.sqlservercentral.com/articles/T-SQL/71550/[/url]It seems to be exactly what you are looking for.
I'll mess with his logic and see if I cannot come up with something. The problem is that they are not all contiguous, even though they are continuous, such as with rows 5 and 6.
July 24, 2012 at 8:15 am
You need to flatten out your dates to use Jeff's methods using something similar to this code:
declare @Dates table
([Row] int NOT NULL,
[OrgID] varchar(50) NOT NULL,
[StartDate] date NULL,
[EndDate] date NULL)
Insert into @Dates
Values
(1, '121904', '2/4/2008', '6/1/2008'),
(2, '121904', '8/21/2008', '1/1/2010'),
(3, '121904', '8/14/2009', '6/30/2010'),
(4, '121904', '6/30/2010', '6/30/2011'),
(5, '121904', '8/10/2011', '9/1/2012'),
(6, '121904', '9/21/2011', '9/21/2012')
;WITH CTE
AS (
SELECT TOP 1000 rc = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns
)
select orgid,DATEADD(d,rc,startdate) from cte CROSS JOIN @dates
WHERE DATEADD(d,rc,startdate) BETWEEN startdate AND enddate
GROUP BY orgid,DATEADD(d,rc,startdate)
Then you can apply the island's algorithm (see Jeff's page) and group on the orgid
July 24, 2012 at 8:34 am
Looks like I got beat to this, but I'll post what I did any ways.
declare @Dates table(
[Row] int NOT NULL,
[OrgID] varchar(50) NOT NULL,
[StartDate] date NULL,
[EndDate] date NULL
);
Insert into @Dates
Values
(1, '121904', '2/4/2008', '6/1/2008'),
(2, '121904', '8/21/2008', '1/1/2010'),
(3, '121904', '8/14/2009', '6/30/2010'),
(4, '121904', '6/30/2010', '6/30/2011'),
(5, '121904', '8/10/2011', '9/1/2012'),
(6, '121904', '9/21/2011', '9/21/2012');
select * from @Dates;
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
tally(n) as (select 0 union all select row_number() over (order by (select null)) from e4 a cross join e2 b),
basedata as (
select distinct
d.OrgID,
dateadd(dd,dt.n,StartDate) ServiceDate
from
@Dates d
cross apply (select top (datediff(dd,d.StartDate,d.EndDate) + 1) n from tally) dt(n)
),
WorkingData as (
select
OrgID,
ServiceDate,
row_number() over (partition by OrgID order by ServiceDate) as rn
from
basedata
)
select
OrgID,
min(ServiceDate) as StartDate,
max(ServiceDate) as EndDate
from
WorkingData
group by
OrgID,
dateadd(dd,-1 * rn, ServiceDate)
order by
OrgID,
dateadd(dd,-1 * rn, ServiceDate);
July 24, 2012 at 9:09 am
Impressive. Thanks. It did the trick.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply