create new table based on union of range data

microsoft.public.sqlserver.programming

7 Jan 2006 8:58 PM

Cathy Smith

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

7 Jan 2006 9:42 PM

John Bell

Hi Cathy

You may want to check out Itzik's articles in SQL Server Magazine

http://www.windowsitpro.com/Articles/ArticleID/44570/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

Show quote

"Cathy Smith" <c*@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
>

7 Jan 2006 11:30 PM

Erland Sommarskog

Cathy Smith (c*@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.databases.ms-sqlserver/browse_thread/thread/6017ebd3dc467439/448dda4c48fb808b?lnk=st&q=Hummel+group%3Acomp.databases.ms-sqlserver+author%3AErland+author%3ASommarskog&rnum=6&hl=sv#448dda4c48fb808b

your problem reminds me of the problem in that thread.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

8 Jan 2006 3:29 AM

--CELKO--

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.developersdex.com ***

8 Jan 2006 9:22 AM

markc600

-- 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,minstartdate,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

8 Jan 2006 2:01 PM

Mark

-- 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,minstartdate,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

8 Jan 2006 5:37 PM

Damien

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

Show quote

"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 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
>
>
>

9 Jan 2006 12:57 PM

Cathy Smith

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/comp.databases/msg/7e3dba76e3bc5d57

I modified it to encompass an additional column called client.

Thanks so much everyone for your wonderful solutions!!!

Cathy

Show quote

"Damien" <Dam***@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:
>
>> 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
>>
>>
>>