May 15, 2017 at 3:39 pm
I have been requested to create a report using numeric ranges. So for example, a user should be able to put in a starting number (100) and an ending number (200) and an interval (5). What would I create in TSQL to generate the rows with 100, 105, 110, 115, 120, ... 200. The idea is that I will create labels 100 - 105, 106 - 110, 111-115 ... 196 - 200. Any help on this would be greatly appreciated. My thought is that I would create the data in TSQL and then use it in a crystal report.
May 15, 2017 at 4:37 pm
There are a couple of approaches that could be used, which one is best depends on how big any gaps in your numbers could be. Also, your ranges are not even: the range 100-105 contains SIX numbers, whereas all of the other ranges contain FIVE. This brings up the issue of whether you want one endpoint to always be the starting number or want one endpoint to always be a multiple of the interval. That is, if the starting number is 3 and the interval is 5 do you want the first range to be 1-5 or 3-7?
Could you provide sample data and expected results as outlined in the first link in my signature?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 16, 2017 at 7:00 am
The best option is to use a tally table. Then you can work around that to keep only a certain range and use a certain interval.
Here's an example, but as Drew mentioned, you'll have to work on your requirements:
DECLARE @Start int = 100,
@End int = 200,
@Interval int = 3;
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP((@End - @Start)/@Interval)
(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)))*@Interval n
FROM E4
)
SELECT @Start n
UNION ALL
SELECT @Start + n
FROM cteTally
WHERE @Start + n < @End
--UNION ALL
--SELECT @End
;
You can read more about tally tables in here:
http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
http://www.sqlservercentral.com/articles/T-SQL/62867/
May 16, 2017 at 7:28 am
Thanks Luis,
It looks like what you suggested is exactly what I was looking for. As for Drew's comment, you were bang on, I suppose I technically should have started at 101. Thank you both very much for your help with this challenge. I will do some continued studying on tally tables, the seem like they could be very useful.
May 16, 2017 at 9:05 am
I posted this function about a year ago here. It takes for parameters:
@low = represents the lowest value for n1.
@high = represents the highest value for n1.
@gap = how much n1 and n2 will increase each row; @gap also represents the difference between n1 and n2.
@row1 = the first value of rn. When @row = 0 then rn begins at 0, when @row = 1 then rn will begin at 1.
Example1:SELECT rn, n1 FROM dbo.getnumsAB(100, 200, 5, 1);
Results:rn n1
---- ----------------
1 100
2 105
3 110
4 115
5 120
...
20 195
21 200
Example2:
DECLARE @StartDate datetime = '1/1/2015', @EndDate datetime = '2/28/2015';
SELECT
WeekNbr = 'Week #'+CAST(RN AS varchar(2)),
WeekStart = CONVERT(DATE, DATEADD(DAY,N1,@StartDate)),
WeekEnd = CONVERT(DATE, DATEADD(DAY,N2-1,@StartDate))
FROM dbo.getnumsAB(0,datediff(DAY,@StartDate,@EndDate),7,1);
Results:
WeekNbr WeekStart WeekEnd
-------- ---------- ----------
Week #1 2015-01-01 2015-01-07
Week #2 2015-01-08 2015-01-14
Week #3 2015-01-15 2015-01-21
Week #4 2015-01-22 2015-01-28
Week #5 2015-01-29 2015-02-04
Week #6 2015-02-05 2015-02-11
Week #7 2015-02-12 2015-02-18
Week #8 2015-02-19 2015-02-25
Week #9 2015-02-26 2015-03-04
The Function
CREATE FUNCTION dbo.GetNumsAB
(
@low bigint,
@high bigint,
@gap bigint,
@row1 bit
)
/****************************************************************************************
Purpose:
Creates up to 100,544,625 sequential numbers beginning with @low and ending with @high.
Used to replace iterative methods such as loops, cursors and recursive CTEs to solve SQL
problems. Based on Itzik Ben-Gan's getnums function with some tweeks and enhancements
and added functionality. The logic for getting rn to begin at 0 or 1 is based comes from
Jeff Moden's fnTally function.
Compatibility:
SQL Server 2008+ and Azure SQL Database
Syntax:
SELECT rn, n1, n2 FROM dbo.getnumsAB(@low,@high,@gap,@row1);
Parameters:
@low = a bigint that represents the lowest value for n1.
@high = a bigint that represents the highest value for n1.
@gap = a bigint that represents how much n1 and n2 will increase each row; @gap also
represents the difference between n1 and n2.
@row1 = a bit that represents the first value of rn. When @row = 0 then rn begins
at 0, when @row = 1 then rn will begin at 1.
Return Types:
Inline Table Valued Function returns:
rn = bigint; a row number that works just like T-SQL ROW_NUMBER() except that it can
start at 0 or 1 which is dictated by @row1.
n1 = bigint; a sequential number starting at the value of @low and incrimentingby the
value of @gap until it is less than or equal to the value of @high.
n2 = bigint; a sequential number starting at the value of @low+@gap and incrimenting
by the value of @gap.
Developer Notes:
1. The lowest and highest possible numbers returned are whatever is allowable by a
bigint. The function, however, returns no more than 100,544,625 rows (465^3).
2. @gap does not affect rn, rn will begin at @row1 and increase by 1 until the last row
unless its used in a query where a filter is applied to rn.
3. @gap must be greater than 0 or the function will not return any rows.
4. Keep in mind that when @row1 is 0 then the highest row-number will be the number of
rows returned minus 1
5. If you only need is a sequential set beginning at 0 or 1 then, for best performance
use the RN column. Use N1 and/or N2 when you need to begin your sequence at any
number other than 0 or 1 or if you need a gap between your sequence of numbers.
6. Although @gap is a bigint it must be a positive integer or the function will
not return any rows.
7. @high must be equal or higher than @low or the function won't return any rows.
8. There is no performance penalty for sorting by rn ASC; there is a large performance
penalty for sorting in descending order. To sort in descending order, first make sure
that @gap = 1. Next create an alias column (called "n_desc" in the example below) and
use the formula: n_desc = ABS(rn-(@high+1)). The example below will return the numbers
1 to 10 in descending order:
DECLARE @low int = 1, @high int = 10, @gap int = 1, @row1 bit = 1;
SELECT n_desc = ABS(rn-(@high+(@row1)))
FROM dbo.getnumsAB(@low,@high,1,@ro1)
ORDER BY rn;
Examples:
--===== 1. Using RN (rownumber)
-- (1.1) The best way to get the numbers 1,2,3...@high (e.g. 1 to 5):
SELECT RN FROM dbo.getnumsAB(1,5,1,1);
-- (1.2) The best way to get the numbers 0,1,2...@high-1 (e.g. 0 to 5):
SELECT RN FROM dbo.getnumsAB(0,5,1,0);
--===== 2. Using N1
-- (2.1) To begin with numbers other than 0 or 1 use N1 (e.g. -3 to 3):
SELECT N1 FROM dbo.getnumsAB(-3,3,1,1);
-- (2.2) ROW_NUMBER() is built in. If you want a ROW_NUMBER() include RN:
SELECT RN, N1 FROM dbo.getnumsAB(-3,3,1,1);
-- (2.3) If you wanted a ROW_NUMBER() that started at 0 you would do this:
SELECT RN, N1 FROM dbo.getnumsAB(-3,3,1,0);
--===== 3. Using N2 and @gap
-- (3.1) To get 0,10,20,30...100, set @low to 0, @high to 100 and @gap to 10:
SELECT N1 FROM dbo.getnumsAB(0,100,10,1);
-- (3.2) Note that N2=N1+@gap; this allows you to create a sequence of ranges.
-- For example, to get (0,10),(10,20),(20,30).... (90,100):
SELECT N1, N2 FROM dbo.getnumsAB(0,90,10,1);
-- (3.3) Remember that a rownumber is included and it can begin at 0 or 1:
SELECT RN, N1, N2 FROM dbo.getnumsAB(0,90,10,1);
--===== (4) A real life example using RN, N1 and N2:
-- Beginning with @StartDate, to generate ranges of weeks that occur between
-- @startDate & @EndDate:
DECLARE @StartDate datetime = '1/1/2015', @EndDate datetime = '2/28/2015';
SELECT
WeekNbr = 'Week #'+CAST(RN AS varchar(2)),
WeekStart = CONVERT(DATE, DATEADD(DAY,N1,@StartDate)),
WeekEnd = CONVERT(DATE, DATEADD(DAY,N2-1,@StartDate))
FROM dbo.getnumsAB(0,datediff(DAY,@StartDate,@EndDate),7,1);
---------------------------------------------------------------------------------------
Revision History:
Rev 00 - 20140518 - Initial Development - Alan Burstein
Rev 01 - 20151029 - Added 65 rows to make L1=465; 465^3=100.5M. Updated comment section
- Alan Burstein
****************************************************************************************/
RETURNS TABLE WITH SCHEMABINDING AS RETURN
WITH
L1(N) AS
(
SELECT 1
FROM (VALUES
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
(NULL),(NULL),(NULL)) t(N) -- 465 values
),
iTally AS
(
SELECT rn = 0 WHERE @row1 = 0
UNION ALL
SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM L1 a CROSS APPLY L1 b CROSS APPLY L1 c
) --Up to 100,544,625 numbers (select 465*465*465 = 100,544,625)
SELECT TOP (ABS((@high-@low)/ISNULL(NULLIF(@gap,0),1)+1))
rn,
n1 = ((rn-( @row1 ))*@gap+@low),
n2 = ((rn-(@row1-1))*@gap+@low)
FROM iTally
WHERE @high >= @low and @gap > 0
ORDER BY rn;
-- Itzik Ben-Gan 2001
May 16, 2017 at 1:49 pm
Alan, that's a cool ITVF. I've used a similar approach when generating test data, but never thought to encapsulate it like that.
I missed your publication of it. Thanks for posting it again so those of us who run behind from time to time can catch up. 😉
May 17, 2017 at 3:57 pm
Ed Wagner - Tuesday, May 16, 2017 1:49 PMAlan, that's a cool ITVF. I've used a similar approach when generating test data, but never thought to encapsulate it like that.
I missed your publication of it. Thanks for posting it again so those of us who run behind from time to time can catch up. 😉
Thanks a ton Ed!
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply