May 26, 2016 at 8:50 am
So I have a table that contains serial numbers. However, if there's a range of serial numbers, there's only one record for that range.
For example, the table I'm working with has two fields called Serial_Number and End_Serial_Number. If you have a range of 1 thru 5, the the table only contains one record with Serial_number = 1 and End_Serial_Number = 5.
What I'm trying to do is create a record for all the numbers such as 2, 3, 4, 5.
So the query I have now just to get the one record is this:
Select Serial_Number, End_Serial_Number
From T1
Where Serial_Number = 1
The result from the above query is one record:
Serial_Number--------End_Serial_Number
1------------------------5
May 26, 2016 at 8:53 am
DarthBurrito (5/26/2016)
So I have a table that contains serial numbers. However, if there's a range of serial numbers, there's only one record for that range.For example, the table I'm working with has two fields called Serial_Number and End_Serial_Number. If you have a range of 1 thru 5, the the table only contains one record with Serial_number = 1 and End_Serial_Number = 5.
What I'm trying to do is create a record for all the numbers such as 2, 3, 4, 5.
So the query I have now just to get the one record is this:
Select Serial_Number, End_Serial_Number
From T1
Where Serial_Number = 1
The result from the above query is one record:
Serial_Number--------End_Serial_Number
1------------------------5
Question seems incomplete. What are you trying to accomplish?
May 26, 2016 at 9:00 am
Something like this?
WITH Numbers AS (
SELECT n
FROM (VALUES (1), (2), (3), (4), (5)) x(n)
)
SELECT n
FROM Numbers n
JOIN T1 s
ON n >= Serial_Number
AND n <= End_Serial_Number
WHERE Serial_Number = 1
John
May 26, 2016 at 9:01 am
Would this work?
SELECT
Row_ID, Serial_Table.Serial_Number + Sequence.Digit
FROM
Serial_Table
JOIN
(
SELECT
DIGIT
FROM
(VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9), (10)) Numbers(digit)
) Sequence
ON
Sequence.Digit <= Serial_Table.End_Serial_Number - Serial_table.Serial_Number
May 26, 2016 at 9:04 am
Haha, you beat me to it John Mitchell-245523!
May 26, 2016 at 9:04 am
Since I already have the start serial number and end serial number on the same one record, which is SN=1 and END_SN =5, I want to create the four other records I need with
SN = 1 (already have this)
SN = 2 (need)
SN = 3 (need)
SN = 4 (need)
SN = 5 (need)
Then instead of having one record, I have a total of 5 records.
Hope I cleared it up.
May 26, 2016 at 9:19 am
We can't see your screen, and we can't read your mind, so this is all guesswork, but how about this?
WITH Numbers AS (
SELECT n
FROM (VALUES (1), (2), (3), (4), (5)) x(n)
)
INSERT INTO T1
SELECT N.n, s.End_Serial_Number
FROM Numbers N
JOIN T1 s
ON n > s.Serial_Number
AND n <= s.End_Serial_Number
WHERE s.Serial_Number = 1
John
May 26, 2016 at 9:20 am
here's a more robust example, for more than one row in the original table, and where the end_serial_Number might be null.
the MiniTally table has to contain an integer greater than the largest Serial Number to generate.
WITH T1(SomeDescriptor,Serial_Number, End_Serial_Number)
AS
(
SELECT 'a',1 ,5 UNION ALL
SELECT 'b',44100,44110 UNION ALL
SELECT 'c',44150,NULL UNION ALL
SELECT 'd',44115,44125 UNION ALL
SELECT 'e',44157,NULL UNION ALL
SELECT 'f',44250,44350 UNION ALL
SELECT 'g',44128,NULL
)
Select * FROM
(-- a neat trick to generate 2^N numbers
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.columns CROSS JOIN sys.columns tx CROSS JOIN sys.columns tx2
) MiniTally
INNER JOIN T1
ON MiniTally.N
BETWEEN T1.Serial_Number
AND ISNULL(T1.End_Serial_Number ,T1.Serial_Number )
order by SomeDescriptor,n
Lowell
May 26, 2016 at 9:29 am
sestell1 (5/26/2016)
Would this work?
SELECT
Row_ID, Serial_Table.Serial_Number + Sequence.Digit
FROM
Serial_Table
JOIN
(
SELECT
DIGIT
FROM
(VALUES (0), (1),(2),(3),(4),(5),(6),(7),(8),(9), (10)) Numbers(digit)
) Sequence
ON
Sequence.Digit <= Serial_Table.End_Serial_Number - Serial_table.Serial_Number
This worked perfectly. Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply