create new table based on union of range data

Hi, I was hoping someone could help me out.
Is it possible for SQL to take a table which has date range data (start and
end date indicating the contract period of the client) and create a new
table which creates a union of 'unionizable' range data for each specific
client. For example (here i am using numbers to indicate date order):
client startdate enddate
A 2 5
A 3 7
A 7 10
A 11 12
B 4 6
B 8 14
B 5 7
C 2 3
C 3 10
C 1 20
The table operation would give (for example {2..5} U {3..7} U {7..10} =
(2..10} in the resultant table. But {4..6} U {8..14} does not have a common
union, so I just leave them as {4..6} and {8..14} in the resultant table:
client startdate enddate
A 2 10
A 11 12
B 4 7
B 8 14
B 15 21
C 1 20
I am unable to determine how to do this. I was thinking to move towards
implementing cursors, but that in itself will be a complex algorithm. Is
there some easier method to use? I was also thinking of cross joining the
initial table with itself on the condition that t1.client = t2.client (yes
this is not a cross join, jut results in an inner join). Then deriving a new
table from this based upon a comparison between t1.startdate , t2.startdate
and t1.enddate, t2.enddate
Would anyone have any insight into this?
any help most appreciated!
thanks!
CathyHi Cathy
You may want to check out Itzik's articles in SQL Server Magazine
http://www.windowsitpro.com/Article...4570/44570.html
You may need to undo the current ranges such as (using your sample data,
plus a few more test cases)
CREATE TABLE #values ( Client char(1), Num int )
INSERT INTO #values ( Client , Num )
SELECT DISTINCT C.[Client], N.[Num]
FROM
( SELECT 'A' AS [client], 2 as [start], 5 as [end]
UNION ALL SELECT 'A', 3, 7
UNION ALL SELECT 'A', 7, 10
UNION ALL SELECT 'A', 11, 12
UNION ALL SELECT 'B', 4, 6
UNION ALL SELECT 'B', 8, 14
UNION ALL SELECT 'B', 5, 7
UNION ALL SELECT 'B', 16, 24
UNION ALL SELECT 'C', 2, 3
UNION ALL SELECT 'C', 3, 10
UNION ALL SELECT 'C', 1, 20
UNION ALL SELECT 'D', 2, 2
) C
JOIN (
SELECT 1 AS Num
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20
UNION SELECT 21
UNION SELECT 22
UNION SELECT 23
UNION SELECT 24
UNION SELECT 25
UNION SELECT 26
UNION SELECT 27
UNION SELECT 28
UNION SELECT 29
) N ON C.[Start] <= N.Num and C.[end] >= n.num
John
"Cathy Smith" <cs@.cs.com.au> wrote in message
news:%23FPXb08EGHA.2856@.TK2MSFTNGP12.phx.gbl...
> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a
> common union, so I just leave them as {4..6} and {8..14} in the resultant
> table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a
> new table from this based upon a comparison between t1.startdate ,
> t2.startdate and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>|||Cathy Smith (cs@.cs.com.au) writes:
> Is it possible for SQL to take a table which has date range data (start
> and end date indicating the contract period of the client) and create a
> new table which creates a union of 'unionizable' range data for each
> specific client. For example (here i am using numbers to indicate date
> order):
Have a look at
http://groups.google.com/group/comp...48dda4c48fb808b
your problem reminds me of the problem in that thread.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||1) Look up the Rick Snodgrass book at University of Arizona.
2) Look up the use of a Calendar Auxiliary table.
3) Look up SQL FOR SMARTIES for this kind of query using a calendar
table. I have to go to bed now, but the idea is to see what ranges each
calendar dates falls inside of. Make a list of cal_dates by client,
such that there is a missing date before the MIN() and after the MAX()
of the list.
It is a very simple set of joins and you do not need elaborate
subqueries.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Cathy,
what about something like this:
-- DROP TABLE #tmp
CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
SET NOCOUNT ON
INSERT INTO #tmp VALUES ( 'A', 2, 5 )
INSERT INTO #tmp VALUES ( 'A', 3, 7 )
INSERT INTO #tmp VALUES ( 'A', 7, 10 )
INSERT INTO #tmp VALUES ( 'A', 11, 12 )
INSERT INTO #tmp VALUES ( 'B', 4, 6 )
INSERT INTO #tmp VALUES ( 'B', 8, 14 )
INSERT INTO #tmp VALUES ( 'B', 5, 7 )
INSERT INTO #tmp VALUES ( 'C', 2, 3 )
INSERT INTO #tmp VALUES ( 'C', 3, 10 )
INSERT INTO #tmp VALUES ( 'C', 1, 20 )
SET NOCOUNT OFF
-- SELECT * FROM #tmp
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1, #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
GROUP BY t1.client
UNION
SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
FROM #tmp t1
WHERE NOT EXISTS
(
SELECT *
FROM #tmp t2
WHERE t1.client = t2.client
AND t2.startdate > t1.startdate
AND t2.startdate Between t1.startdate And t2.startdate
)
GROUP BY t1.client
If the code doesn't quite do what you want, perhaps the theory is good, ie a
UNION of records which have range matches, and those that don't.
Let me know how you get on.
Damien
"Cathy Smith" wrote:

> Hi, I was hoping someone could help me out.
> Is it possible for SQL to take a table which has date range data (start an
d
> end date indicating the contract period of the client) and create a new
> table which creates a union of 'unionizable' range data for each specific
> client. For example (here i am using numbers to indicate date order):
> client startdate enddate
> A 2 5
> A 3 7
> A 7 10
> A 11 12
> B 4 6
> B 8 14
> B 5 7
> C 2 3
> C 3 10
> C 1 20
> The table operation would give (for example {2..5} U {3..7} U {7..10} =
> (2..10} in the resultant table. But {4..6} U {8..14} does not have a commo
n
> union, so I just leave them as {4..6} and {8..14} in the resultant table:
> client startdate enddate
> A 2 10
> A 11 12
> B 4 7
> B 8 14
> B 15 21
> C 1 20
> I am unable to determine how to do this. I was thinking to move towards
> implementing cursors, but that in itself will be a complex algorithm. Is
> there some easier method to use? I was also thinking of cross joining the
> initial table with itself on the condition that t1.client = t2.client (yes
> this is not a cross join, jut results in an inner join). Then deriving a n
ew
> table from this based upon a comparison between t1.startdate , t2.startdat
e
> and t1.enddate, t2.enddate
> Would anyone have any insight into this?
> any help most appreciated!
> thanks!
> Cathy
>
>|||-- If you are using SQL Server 2005, you can use
-- recursive CTEs to get the results
create table Contracts(client char(1), startdate int, enddate int)
insert into Contracts(client,startdate,enddate) values ('A', 2 , 5)
insert into Contracts(client,startdate,enddate) values ('A', 3 , 7)
insert into Contracts(client,startdate,enddate) values ('A', 7 , 10)
insert into Contracts(client,startdate,enddate) values ('A', 11 , 12)
insert into Contracts(client,startdate,enddate) values ('B', 4 , 6)
insert into Contracts(client,startdate,enddate) values ('B', 8 , 14)
insert into Contracts(client,startdate,enddate) values ('B', 5 , 7)
insert into Contracts(client,startdate,enddate) values ('C', 2 , 3)
insert into Contracts(client,startdate,enddate) values ('C', 3 , 10)
insert into Contracts(client,startdate,enddate) values ('C', 1 , 20);
with cte_contracts(client,startdate,enddate,m
instartdate,maxenddate) as
(
select A.client,A.startdate,A.enddate,A.startdate,A.enddate
from Contracts A
union all
select A.client,A.startdate,A.enddate,B.startdate,C.enddate
from cte_contracts A
inner join Contracts B on B.client=A.client
and B.enddate >= A.minstartdate and B.startdate <= A.maxenddate
inner join Contracts C on C.client=A.client
and C.enddate >= A.minstartdate and C.startdate <= A.maxenddate
where (B.startdate < A.minstartdate and C.enddate >= A.maxenddate)
or (B.startdate <= A.minstartdate and C.enddate > A.maxenddate)
)
select distinct client,min(minstartdate),max(maxenddate)
from cte_contracts
group by client,startdate,enddate
drop table Contracts|||Thanks everyone! I really appreciate the wonderful feedback!!!
I took everyone's suggestions into perspective and finally came up with a
solution based on two views and a select statement, taken from the following
article I found at:
http://groups.google.com.au/group/c...e3dba76e3bc5d57
I modified it to encompass an additional column called client.
Thanks so much everyone for your wonderful solutions!!!
Cathy
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:BC80A0E1-86A5-4EB5-83D3-821FEC1D0765@.microsoft.com...
> Cathy,
> what about something like this:
> -- DROP TABLE #tmp
> CREATE TABLE #tmp ( client CHAR(1), startdate INT, enddate INT )
> SET NOCOUNT ON
> INSERT INTO #tmp VALUES ( 'A', 2, 5 )
> INSERT INTO #tmp VALUES ( 'A', 3, 7 )
> INSERT INTO #tmp VALUES ( 'A', 7, 10 )
> INSERT INTO #tmp VALUES ( 'A', 11, 12 )
> INSERT INTO #tmp VALUES ( 'B', 4, 6 )
> INSERT INTO #tmp VALUES ( 'B', 8, 14 )
> INSERT INTO #tmp VALUES ( 'B', 5, 7 )
> INSERT INTO #tmp VALUES ( 'C', 2, 3 )
> INSERT INTO #tmp VALUES ( 'C', 3, 10 )
> INSERT INTO #tmp VALUES ( 'C', 1, 20 )
> SET NOCOUNT OFF
>
> -- SELECT * FROM #tmp
>
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1, #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> GROUP BY t1.client
> UNION
> SELECT t1.client, MIN( t1.startdate ), MAX( t1.enddate )
> FROM #tmp t1
> WHERE NOT EXISTS
> (
> SELECT *
> FROM #tmp t2
> WHERE t1.client = t2.client
> AND t2.startdate > t1.startdate
> AND t2.startdate Between t1.startdate And t2.startdate
> )
> GROUP BY t1.client
> If the code doesn't quite do what you want, perhaps the theory is good, ie
> a
> UNION of records which have range matches, and those that don't.
> Let me know how you get on.
>
> Damien
> "Cathy Smith" wrote:
>