CODEWITHSUNDEEP
sql-servert-sql
MrSiezen
MrSiezen

Reputation: 95

Query results in three different result sets instead of one

I need a month list for three years , but I get three different result sets instead of one list (so with 36 lines). I'm quite new to SQL server so I could be completely wrong in the logic here below ;).

declare @yearlist table (value int)
declare @year int
INSERT INTO @yearlist VALUES (year(getdate())-1)
INSERT INTO @yearlist VALUES (year(getdate())+0)
INSERT INTO @yearlist VALUES (year(getdate())+1)

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @yearlist
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @year   

WHILE @@FETCH_STATUS = 0   
BEGIN   
  ;with mths as(
    select 1 as mth, DATENAME(MONTH, cast(@year*100+1 as varchar) + '01')  as monthname, RIGHT(CAST(@year AS CHAR(4)),2) AS yr2, @year as yr 
    union all
    select mth+1, DATENAME(MONTH, cast(@year*100+(mth+1) as varchar) + '01'), RIGHT(CAST(@year AS CHAR(4)),2) AS yr2, @year as yr 
    from mths where mth<12
  )
  select yr2 + RIGHT('00' + CONVERT(NVARCHAR(2), mth), 2) as monthID,  
  * from mths  

  FETCH NEXT FROM db_cursor INTO @year   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

But this is the needed data: 

1701    January     2017
1702    February    2017
1703    March       2017
1704    April       2017
1705    May         2017
etc...
1908    August      2019
1909    September   2019
1910    October     2019
1911    November    2019
1912    December    2019

Where the year must be the past, current and next year (dynamic).

Upvotes: 0

Views: 90

Answers (2)

S.Jose
S.Jose

Reputation: 246

If you are only interested on past, current and next year, following query should work for you

SELECT RIGHT(YEAR(GETDATE())-1, 2) + right ('00'+ltrim(str(mth )),2 ) ,  DateName( month , DateAdd( month , mth , 0 ) - 1 ), YEAR(GETDATE())-1  FROM  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS X(mth)
UNION ALL
SELECT RIGHT(YEAR(GETDATE()), 2) + right ('00'+ltrim(str(mth )),2 ), DateName( month , DateAdd( month , mth , 0 ) - 1 ), YEAR(GETDATE())  FROM  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS X(mth)
UNION ALL
SELECT RIGHT(YEAR(GETDATE())+1, 2) + right ('00'+ltrim(str(mth )),2 ), DateName( month , DateAdd( month , mth , 0 ) - 1 ), YEAR(GETDATE()) + 1  FROM  (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS X(mth)

Upvotes: 0

Thom A
Thom A

Reputation: 95554

Ideally, you if this is going to be a repeating thing, where the values could change, you may well want a Calendar Table.

Otherwise, you could use an inline tally table:

DECLARE @StartYear int = 2017,
        @EndYear int = 2019;

DECLARE @StartDate date = CONVERT(varchar(4),@StartYear) + '0101',
        @EndDate date = DATEADD(YEAR,1,CONVERT(varchar(4),@EndYear) + '0101');


WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
    )
SELECT RIGHT(DATEPART(YEAR, V.D),2) + RIGHT('0' + CONVERT(varchar(2),DATEPART(MONTH,V.D)),2) AS YearMonth,
       DATENAME(MONTH, V.D) AS [MonthName],
       DATEPART(YEAR, V.D) AS [Year]
FROM Tally T
     CROSS APPLY (VALUES(DATEADD(MONTH, T.I, @StartDate))) V(D)
WHERE V.D < @EndDate;

This solution covers you for up to 1000 months (just over 83 years), so should be more than enough.

Upvotes: 3

Related Questions