In a record of purchases, I want to generate a sequence of +/-12 months out of a base date
Client Date
Joe 2020-03-15
Maria 2019-11-01
so the desired outcome would be
Client Date
Joe 2019-03-15
Joe 2019-04-15
.
.
.
Joe 2020-03-15
.
.
.
Joe 2021-02-15
Joe 2021-03-15
Maria 2018-11-01
Maria 2018-12-01
.
.
.
Maria 2019-11-01
.
.
.
Maria 2020-10-01
Maria 2020-11-01
How can this be achieved?
One option uses a recursive query:
with cte as (
select client, dateadd(month, -12, date) date, 0 lvl from mytbale
union all
select client, dateadd(month, 1, date), lvl + 1 from cte where lvl < 24
)
select * from cte order by client, date
Just another option is an ad-hoc Tally Table in concert with a CROSS JOIN
Example
Select A.Client
,[Date] = date,DateAdd(MONTH,N,[Date])
From YourTable A
Cross Join ( Select Top (12+1) N=-1+Row_Number() Over (Order By (Select Null)) From master..spt_values n1) B
Returns
Client Date
Joe 2020-03-15
Joe 2020-04-15
Joe 2020-05-15
Joe 2020-06-15
Joe 2020-07-15
Joe 2020-08-15
Joe 2020-09-15
Joe 2020-10-15
Joe 2020-11-15
Joe 2020-12-15
Joe 2021-01-15
Joe 2021-02-15
Joe 2021-03-15
Maria 2019-11-01
Maria 2019-12-01
Maria 2020-01-01
Maria 2020-02-01
Maria 2020-03-01
Maria 2020-04-01
Maria 2020-05-01
Maria 2020-06-01
Maria 2020-07-01
Maria 2020-08-01
Maria 2020-09-01
Maria 2020-10-01
Maria 2020-11-01
EDIT: If you want +/- 12 months, just a small tweak is required
...
Cross Join ( Select Top (24+1) N=-13+Row_Number() Over (Order By (Select Null)) From master..spt_values n1) B
Here's a slightly different solution that uses the essence of a Tally table. The reason why I'm posting it is because a lot of people aren't allowed to use things in the master database by their DBAs and also because needing a sequence of values is a very common thing. The following method also causes no Logical Reads in the generation of the sequence.
To wit, the "Tally Table" has frequently been referred to as the "Swiss Army Knife" of T-SQL. It's so important that Erland Sommarskog requested a built in sequence generator though MS "CONNECT" (the old name for "Feedback") more than a decade ago. Unfortunately, MS has done nothing other than keep the request open for more than a decade.
With that in mind, here's my rendition of Itzik Ben-Gan's fine work to create such a function. It very intentionally can only start at "0" or "1" but can go up to (actually past) any positive INT value you might need. If you need it to start at a different number than "0" or "1", a simple bit of integer math outside of the function will make it happen. The reason why I didn't build that capability into the function is because sequences starting at "0" or "1" are the most common usage and I didn't want to penalize performance even by a millisecond or two because of the extreme usage that I put this function through.
Here's the function... everyone should have something like it either in every database or in a "utility" database that the general public can use.
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 10 Quadrillion.
Usage:
--===== Syntax example
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
@ZeroOrOne will internally conver to a 1 for any number other than 0 and a 0 for a 0.
@MaxN has an operational domain from 0 to 4,294,967,296. Silent truncation occurs for larger numbers.
Please see the following notes for other important information
Notes:
1. This code works for SQL Server 2008 and up.
2. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URL for how it works.
https://www.itprotoday.com/sql-server/virtual-auxiliary-table-numbers
3. To start a sequence at 0, @ZeroOrOne must be 0. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
4. 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 4,294,967,296. If a larger
number is used, the function will silently truncate after that max. If you actually need a sequence with that many
or more 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 implicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
9. This will return 1-10,000,000 to a bit-bucket variable in about 986ms.
This will return 0-10,000,000 to a bit-bucket variable in about 1091ms.
This will return 1-4,294,967,296 to a bit-bucket variable in about 9:12( mi:ss).
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 - 07 Sep 2013 - Jeff Moden
- Change the max for @MaxN from 10 Billion to 10 Quadrillion to support an experiment.
This will also make it much more difficult for someone to actually get silent truncation in the future.
Rev 04 - 04 Aug 2019 - Jeff Moden
- Enhance performance by making the first CTE provide 256 values instead of 10, which limits the number of
CrossJoins to just 2. Notice that this changes the maximum range of values to "just" 4,294,967,296, which
is the entire range for INT and just happens to be an even power of 256. Because of the use of the VALUES
clause, this code is "only" compatible with SQLServer 2008 and above.
- Update old link from "SQLMag" to "ITPro". Same famous original article, just a different link because they
changed the name of the company (twice, actually).
- Update the flower box notes with the other changes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;
p.s. I normally don't use "Hungarian-Notation" for objects but I also have a Tally Table and you can't have two objects with the same name.
Once you have something like that, problems that require sequences of numbers become easy, fast, and inexpensive resource-wise. For example, here's how I would code the solution to the OP's problem using this function.
--===== Solve the problem using the fnTally function as a numeric sequence generator starting
-- at 0 and ending at the +/- 12 month differences for each Client's base date.
DECLARE @OffsetMonths INT = 12 --Just to make it a bit more flexible
;
SELECT d.Client
,[Date] = DATEADD(mm,t.N-@OffsetMonths,d.[Date])
FROM #TestTable d
CROSS APPLY dbo.fnTally(0,@OffsetMonths*2) t
ORDER BY d.Client,[Date]
;
If you want to test that code, here's what I built for a test table.
--===== Create and populate a test table.
-- This is NOT a part of the solution.
-- We're just creating test data here.
DROP TABLE IF EXISTS #TestTable
;
CREATE TABLE #TestTable
(
Client VARCHAR(20)
,[Date] DATE
)
;
INSERT INTO #TestTable
(Client,[Date])
VALUES ('Joe','2020-03-15')
,('Maria','2019-11-01')
;
If you'd like to know more about how "Tally Tables" and related functions work, here's a link to my article, which is much too long to post here.
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
