July 3, 2016 at 10:34 am
Hello all,
I'm trying to dynamically create a table of days spanned for a aging report. I would like the end user to be able to choose the days span like 10, 15, 30 days and also how many buckets like show me 3 buckets at 10 day intervals. That result would be 0-10, 11-20, 21-30. I am trying some code with a WHILE loop but I'm really horrible with them. Any help would be appreciated.
Current code i'm tryingDECLARE @Interval INT = 5
,@Span INT = 30
,@MinNum INT
,@MaxNum INt
DECLARE @Table TABLE (MinNum INT, MaxNum INT )
WHILE (SELECT COUNT(*) FROM @Table) < @Interval
BEGIN
SET @MinNum = 0 + @Span + 1
SET @MaxNum = @Span + @Span
INSERT INTO @Table (MinNum, MaxNum)
SELECT @MinNum, @MaxNum
END
SELECT * FROM @Table
***SQL born on date Spring 2013:-)
July 3, 2016 at 11:46 am
I'm closer I just need to figure out how to add the 1
DECLARE @Interval INT = 5
,@Span INT = 30
,@MinNum INT = 0
,@MaxNum INt
DECLARE @Table TABLE (MinNum INT, MaxNum INT )
SET @MaxNum = @Span
WHILE (SELECT COUNT(*) FROM @Table) < @Interval
BEGIN
INSERT INTO @Table (MinNum, MaxNum)
SELECT @MinNum, @MaxNum
SET @MinNum = @MinNum + @Span
SET @MaxNum = @MaxNum + @Span;
END
SELECT * FROM @Table
***SQL born on date Spring 2013:-)
July 3, 2016 at 3:42 pm
Will this approach work for you? I don't have a while loop because, from what you described, I don't think it's necessary.
I you have a starting date, from here you can DATEADD to include date columns if you need them.
DECLARE @DaysPerInterval Integer = 10,
@NumberOfIntervals Integer = 3;
WITH ctePartitions AS (
SELECT t.N, Window = NTILE(@NumberOfIntervals) OVER(ORDER BY t.N)
FROM dbo.TallyN(@NumberOfIntervals * @DaysPerInterval) t
)
SELECT Window, RangeStart = MIN(N), RangeEnd = MAX(N)
FROM ctePartitions
GROUP BY Window
ORDER BY Window;
I used the dbo.TallyN function, which is Itzik Ben Gan's zero-read tally ITVF. Here's the function:
ALTER FUNCTION dbo.TallyN(@N Bigint) RETURNS TABLE WITH SCHEMABINDING
AS
RETURN WITH level0 AS (
SELECT 0 AS g UNION ALL SELECT 0), --2
level1 AS (SELECT 0 AS g FROM level0 AS a CROSS JOIN level0 AS b), --2^2 = 4
level2 AS (SELECT 0 AS g FROM level1 AS a CROSS JOIN level1 AS b), --4^2 = 16
level3 AS (SELECT 0 AS g FROM level2 AS a CROSS JOIN level2 AS b), --16^2 = 256
level4 AS (SELECT 0 AS g FROM level3 AS a CROSS JOIN level3 AS b), --256^2 = 65536
level5 AS (SELECT 0 AS g FROM level4 AS a CROSS JOIN level4 AS b), --65536^2 = 4294967296
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM level5)
SELECT TOP (@N) N
FROM Tally
ORDER BY N;
July 4, 2016 at 12:40 am
This is my take on the same issue:
DECLARE@Interval int = 10,
@Span int = 60,
@MinNum int = 0,
@MaxNum int
DECLARE@Table TABLE (MinN INT, MaxN INT)
SET @MaxNum = @MinNum + @Span
INSERT INTO @Table (MinN, MaxN)
select
CASE
WHEN n = 0 THEN 0
ELSE n * @Interval + 1
END,
n * @Interval + @Interval
from tally
where n BETWEEN (@MinNum/@Interval) AND ((@MinNum + @Span)/@Interval) - 1
SELECT * FROM @Table
However it is not as elegant as Ed's and probably will not scale well, perhaps someone can improve or tell me where my thinking is wrong; I have kept the OP's option for minimum start point.
It is based around the excellent article by Jeff Moden on the tally table, I used the following to generate it;
SELECT TOP 10000 IDENTITY(int, 0, 1) N
INTO Tally
FROM master.dbo.syscolumns sc1,
master.dbo.syscolumns
GO
ALTER TABLE Tally
ADD CONSTRAINT Tally_PK PRIMARY KEY CLUSTERED (N)
GO
.
...
July 4, 2016 at 3:13 am
SELECT
MinNum = @MinNum + (n * @Span),
MaxNum = @MinNum + ((n+1) * @Span)
FROM (VALUES (0),(1),(2),(3),(4),(5),(6)) d (n)
WHERE n < @Interval
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 4, 2016 at 8:45 am
WOW, That's why I love coming here so many great solutions for the same issue. I'm testing each one out but I think I may be replacing my loop with Chris's solutions as it is so short and sweet!
So Awesome thank you guys!!:-D
***SQL born on date Spring 2013:-)
July 4, 2016 at 9:06 am
Quick thoughts Thomas, do not use a while loop as there is no need for it and neither a table variable which will only introduce extra pressure on the tempdb.
😎
Here is my version which is unsurprisingly very similar to Chris's 😀
USE TEEST;
GO
DECLARE @Interval INT = 5 ;
DECLARE @Span INT = 10 ;
DECLARE @MinNum INT = 0 ;
DECLARE @MaxNum INT = @Span;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS x(N))
, NUMS(N) AS (SELECT TOP ( @Interval) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5)
SELECT
@MinNum + ( NM.N * @Span) AS MinNum
,@MinNum + ((NM.N + 1) * @Span) - 1 AS MaxNum
FROM NUMS NM;
July 4, 2016 at 12:31 pm
Thanks Eric, the reason I am using a table variable is so I can join the results. Ultimately I'm trying to end up with a adhoc table that I can join on date to find the appropriate aging bucket for money.
Here is my current code any suggestions on making it simpler or faster is greatly appreciated!!!:-D
ALTER PROCEDURE Cus_Aging_Buckets
@AsOfDate date= NULL, @Interval int= 10, @DaySpan int= 30
AS
SET @AsOfDate = GETDATE();
/***********************************************************************
@DaysSpan is the width of the aging bucket in days
@Interval is the number of overall buckets
@AsOfDate is the date used to define aging up to that date
************************************************************************/
/************************************************************************
Generate Calendar table. Use ROW_NUMBER() to find the date differences going in
reverse from @AsOfDate
**************************************************************************/
DECLARE @Cal TABLE
(
DaysDiff int, EventDate date
);
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (
SELECT TOP (10000) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E16
)
INSERT INTO @Cal
SELECT SeqNo = t.N, [Date] = dt.DT
FROM cteTally AS t
CROSS APPLY
( --=== Create the date
SELECT DT = DATEADD(dd, ( t.N - 1 ) * -1, @AsOfDate)) AS dt;
/************************************************************************
Generate table with bucket values chosen by end user.
**************************************************************************/
DECLARE @Table TABLE
(
MinNum int, MaxNum int
);
DECLARE @MinNum int= 0, @MaxNum int;
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS x(N))
, NUMS(N) AS (SELECT TOP ( @Interval) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N FROM T T1,T T2,T T3,T T4,T T5)
INSERT INTO @Table (MinNum, MaxNum)
SELECT
@MinNum + ( NM.N * @DaySpan) AS MinNum
,@MinNum + ((NM.N + 1) * @DaySpan) AS MaxNum
FROM NUMS NM;
UPDATE @Table
SET MinNum = MinNum + 1
FROM @Table
WHERE MinNum > 0;
/************************************************************************
Combine Calendar and Table to give date and what dynamic bucket it belongs
to.
**************************************************************************/
DECLARE @AgingBucket TABLE (DaySeq INT, BucketDate DATE, BucketName VARCHAR (100))
INSERT INTO @AgingBucket (DaySeq, BucketDate, BucketName)
SELECT c.DaysDiff, c.EventDate,
CASE
WHEN c.DaysDiff BETWEEN MinNum AND MaxNum THEN CAST(MinNum AS varchar(10))+' AND '+CAST(MaxNum AS varchar(10))+' Bucket'
WHEN c.DaysDiff > MAX(t.MaxNum) OVER() THEN CAST(MAX(t.MaxNum) OVER() AS varchar(10))+' + bucket'
END
FROM @Cal AS c
LEFT JOIN
@Table AS t
ON c.DaysDiff BETWEEN t.MinNum AND t.MaxNum;
SELECT * FROM @AgingBucket
***SQL born on date Spring 2013:-)
July 4, 2016 at 11:39 pm
Here's my take on the problem. First, you need a Tally function. Here's my permutation of Itzik Ben-Gan's work. The "zero" thing needs a little work but usually good enough until I get to it.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10E1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10E4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10E12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
Instead of writing a stored procedure for this, I built an iTVF so that you could actually do a join to it (sometimes not a great idea) but it can also be used as a part of a SELECT/INTO if you want to build a quick Temp Table and index it.
Comments and usage are in the code. Wrote it to meet the output of your last bit of code above.
CREATE FUNCTION dbo.DateBuckets
/***************************************************************************************************
PURPOSE:
Returns a 10,000 row date table with a sequence number, date, and bucket-range that identifies the
range of days that have passed for the given bucket.
----------------------------------------------------------------------------------------------------
INPUTS:
@EndDate - (DATE) The final date of the 10,000 date return.
@NumberOfBuckets - (INT) The number of range buckets. Does not include the final overage bucket.
@DaysPerBucket - (INT) Pretty much what is says. The number of days per range bucket.
----------------------------------------------------------------------------------------------------
RETURNS:
DaySeq - (BIGINT) The row with a BucketDate equal to @EndDate will have the value of "1".
BucketDate - (DATE) A date starting at @EndDate and sequentially decreasing by one day
for a total of 10,000 days.
BucketName - (VARCHAR(32)) Format for Range Buckets: SSS and EEE Bucket
where SSS is the starting number of days and EEE is the ending number
of days.
Format for Overage Buckets: MAX + Bucket
where MAX is the product of @NumberOfBuckets * @DaysPerBucket
----------------------------------------------------------------------------------------------------
USAGE:
--===== Basic Syntax
SELECT DaySeq
,BucketDate
,BucketName
FROM dbo.DateBuckets(@EndDate,@NumberOfBuckets,@DaysPerBucket)
ORDER BY DaySeq
;
--===== Using today as an end date, return 10 buckets of 30 days each plus the
-- "overage" bucket for the remaining part of 10,000 days.
SELECT DaySeq
,BucketDate
,BucketName
FROM dbo.DateBuckets(GETDATE(),10,30)
ORDER BY DaySeq
;
----------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 04 Jul 2016 - Jeff Moden
- Initial creation (only tested to a minor degree)
- Ref: http://www.sqlservercentral.com/Forums/Topic1799270-391-1.aspx
***************************************************************************************************/
(
@EndDate DATE
,@NumberOfBuckets INT --Change these to BIGINT if you want to get rid of a ton
,@DaysPerBucket INT --of implicit conversions.
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
cteDates AS
( --=== Generate the dates and other base data
SELECT DaySeq = t.N+1
,BucketDate = DATEADD(dd,-t.n,@EndDate)
,BucketBase = t.N / @DaysPerBucket * @DaysPerBucket --Starts at 0, INT math to bucketize.
FROM dbo.fnTally(0,9999) t
)
,cteBucketEndDays AS
( --=== Calculates the bucket range values
SELECT DaySeq
,BucketDate --== Add 1 to base for start day unless it's a 0.
,BucketStartDay = CASE WHEN BucketBase > 0 THEN BucketBase+1 ELSE 0 END
,BucketEndDay = BucketBase + @DaysPerBucket
FROM cteDates
) --=== Format the final output
SELECT DaySeq
,BucketDate
,BucketName = CASE -- Calculate start as range or overage value
WHEN BucketStartDay < @NumberOfBuckets * @DaysPerBucket
THEN CAST(BucketStartDay AS VARCHAR(10))
ELSE CAST(@NumberOfBuckets * @DaysPerBucket AS VARCHAR(10))
END
+ ' '
+ CASE -- Calulate end as a range or "+" for overage value
WHEN BucketStartDay < @NumberOfBuckets * @DaysPerBucket
THEN 'and ' + CAST(BucketEndDay AS VARCHAR(10))
ELSE '+'
END
+ ' Bucket'
FROM cteBucketEndDays
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
July 5, 2016 at 7:57 am
Jeff,
As usually most excellent. I have no idea why doing a ITVF slipped by my feeble mind. Very, very elegant solution, works perfectly and is much cleaner and now I can use the Tally function for other things without having to re-code the wheel.
Your a inspiration sir ! Thanks again:-D
***SQL born on date Spring 2013:-)
July 5, 2016 at 9:36 am
thomashohner (7/5/2016)
... re-code the wheel.
Haha! +1
-- Itzik Ben-Gan 2001
July 11, 2016 at 8:39 pm
thomashohner (7/5/2016)
Jeff,As usually most excellent. I have no idea why doing a ITVF slipped by my feeble mind. Very, very elegant solution, works perfectly and is much cleaner and now I can use the Tally function for other things without having to re-code the wheel.
Your a inspiration sir ! Thanks again:-D
Thank you for the very kind feedback, Thomas.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply