sql server - Automate up to 10 years in a result set - Stack Overflow

admin管理员组

文章数量:1421943

If I was to do a simple select statement that returned one row, where the first column is a row number - in this instance a 1, a start date and an end date. .

SELECT 1, GETDATE(), DATEADD(year,1,GETDATE()) 'EffectiveToDate' 

So this would give me 1, 2025-01-17 and one year later 2026-01-16.

What I want to achieve, is a further 9 rows - 2 through to 10 that would then be the same as row one - but one year one.

So Row 2 would be -

2, 2026-01-17 and 2027-01-16

and so on. . .

If I was to do a simple select statement that returned one row, where the first column is a row number - in this instance a 1, a start date and an end date. .

SELECT 1, GETDATE(), DATEADD(year,1,GETDATE()) 'EffectiveToDate' 

So this would give me 1, 2025-01-17 and one year later 2026-01-16.

What I want to achieve, is a further 9 rows - 2 through to 10 that would then be the same as row one - but one year one.

So Row 2 would be -

2, 2026-01-17 and 2027-01-16

and so on. . .

• sql-server
• t-sql

Share Improve this question Follow edited Jan 19 at 20:17 Thom A 96.3k1111 gold badges6161 silver badges9595 bronze badges asked Jan 17 at 15:44 ikilledbillikilledbill 24111 gold badge33 silver badges1919 bronze badges 3

• 1 Use a Calendar table. If you don't have one, invest in one. – Thom A Commented Jan 17 at 15:52
• 2 stackoverflow/questions/21425546/… – BenderBoy Commented Jan 17 at 15:57
• 2 As a side note, don't use single quotes (') for aliases. Single quotes are for literal strings, not delimit identifying object names. They have some "gotchas" as their behaviour is not consistent depending on where they are referenced. Also some syntaxes with literal string aliases are deprecated. Stick to object and alias names that don't need delimit identifying, and if you must delimit identify them use T-SQL's identifier, brackets ([]), or ANSI-SQL's, double quotes ("). – Thom A Commented Jan 17 at 16:07

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Managed to do it this way -

    declare @startDate datetime,  
        @endDate datetime;  
  
select  @startDate = getdate(),  
        @endDate = dateadd(year,1,getdate()) -1


;with myCTE as  
   (  
      select 1 as ROWNO,@startDate "StartDate" ,@EndDate "EndDate"
  
  union all  
       select  ROWNO+1 ,dateadd(YEAR, 1, StartDate) ,  dateadd(YEAR, 1, EndDate)
 
  FROM  myCTE  

  where ROWNO+1 <= 10

    )  
select ROWNO,Convert(varchar(10),StartDate,105)  as StartDate ,Convert(varchar(10),EndDate,105) from myCTE 

There are many way you can achieve this. One of the simplest is likely using GENERATE_SERIES:

SELECT GS.value+1,
       DATEADD(YEAR, -GS.Value,GETDATE()) AS DateFrom,
       DATEADD(YEAR, -GS.Value+1,GETDATE()) AS DateTo
FROM GENERATE_SERIES(0,9,1) GS;

If you aren't on SQL Server 2022+, then you won't have access to the function. For such a small number, you could just put the literal values in a VALUES clause:

SELECT V.value+1,
       DATEADD(YEAR,-V.Value, GETDATE()) AS DateFrom,
       DATEADD(YEAR, -V.Value+1,GETDATE()) AS DateTo
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))V(Value);

If you wanted this scale, however, you could use an inline tally:

DECLARE @I int = 10;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@I) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
SELECT T.I,
       DATEADD(YEAR,-T.I+1, GETDATE()) AS DateFrom,
       DATEADD(YEAR, -T.I+2,GETDATE()) AS DateTo
FROM Tally T;

Or you could also create a UDF for the Tally and use that:

CREATE   FUNCTION [fn].[Tally] (@LastNumber bigint, @Zero bit) 
RETURNS table
WITH SCHEMABINDING
AS RETURN

    WITH N AS(
        SELECT N
        FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
        SELECT 0 AS I
        WHERE @Zero = 0
          AND @LastNumber IS NOT NULL
        UNION ALL
        SELECT TOP (ISNULL(@LastNumber,0))
               ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
        FROM N N1, N N2, N N3, N N4, N N5, N N6, N N7) --Up to 10,000,000 rows
    SELECT I
    FROM Tally T;
GO

SELECT T.I+1,
       DATEADD(YEAR, -T.I+1,GETDATE()) AS DateFrom,
       DATEADD(YEAR, -T.I+2,GETDATE()) AS DateTo
FROM fn.Tally(9,0) T;

Or, finally, you could use a Calendar table. One method could look like this, however, note this actually fails if the current date is 29 February:

SELECT ROW_NUMBER() OVER (ORDER BY C.CalendarDate DESC),
       C.CalendarDate AS DateFrom,
       DATEADD(YEAR, 1, C.CalendarDate) AS DateTo
FROM tbl.Calendar C
WHERE C.CalendarMonth = MONTH(GETDATE())
  AND C.CalendarDay = DAY(GETDATE())
  AND C.CalendarYear >= YEAR(GETDATE()) - 9
  AND C.CalendarYear <=  YEAR(GETDATE());

It would, in truth, likely be easier to use a Tally here, rather than a Calendar table.

本文标签： sql serverAutomate up to 10 years in a result setStack Overflow