Create row from number range

Greetings:

I would like to create a row for each number range in a table and add its associated fields with it.

Source Table has:

BEG_NUM END_NUM PRINTER USER DATE
150 153 P01 Ed 6-1-07

I would like to convert that to a table which consists of:
Number Printer User Date
150 P01 Ed 6-1-07
151 P01 Ed 6-1-07
152 P01 Ed 6-1-07
153 P01 Ed 6-1-07

How can I create this using SQL? The source table has lots of rows.

Thanks,
Ericselect min(Number) as BEG_NUM
, max(Number) as END_NUM
, Printer as PRINTER
, User as USER
, Date as DATE
from Source
group
by Printer
, User
, Date|||r937,

Thank you for your reply.

Your solution combines the rows. I want to make a row for each number between the beginning and ending number, so if beginning is 150 and ending is 153 i would have 4 rows listed from that one row. 150,151,152,153.

My goal is to have one row for each series of numbers.|||aaargh, i misunderstood, i got your tables backwards, didn't i

you will need an integers table:create table integers (i integer not null primary key);
insert into integers (i) values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)then you can generate your range of numbers like this:select BEG_NUM + i as Number
, PRINTER as Printer
, USER as User
, DATE as Date
from integers
inner
join Source
on BEG_NUM + i <= END_NUM|||When I do the 2nd part, I get invalid column name for all except i because I am selecting fields from integer table that don't exist.

I tried adding the correct table names and selecting from both tables and then joining but it doesn't work. It says tables have the same exposed names.

When I tried another way, it gave me 10 rows for each number, which wasnt what i want either :(|||i tested my query and it works

probably your table is actually different from what you posted, or you made an error in your query

can't help you any further unless you show your query|||I apologize, Its working now.

Thank you for all your help!!|||The "filling holes in a table" problem is a well-known "difficult" one in relational databases.
If your database system supports it, you should use recursive SQL for the "integers table" part of the story:WITH integers(i) AS (VALUES (0)
UNION ALL
SELECT i+1 FROM integers
WHERE i < 9)
SELECT ... <Rudy's query>
Now this query is much more flexible in case there are bigger holes than width 10: just replace the "9" by, say, "99".
Or better yet, replace it by (SELECT MAX(End_Num-Beg_Num) FROM Source) or something like that, so that the query becomes completely unparametrized and will work on any table with any numeric column pair.
(Or with a date pair, for that matter.)|||nice trick, peter, and i mean that as a sincere compliment

it is a true "hack" and that is a good thing

:)

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