SQL Puzzle | Generate Multiple Years Puzzle

Puzzle Statement

This is a good puzzle. Recently I was working on an assignment & I got this. In this puzzle you have to accept 2 year parameters from year and to year. You have to generate all the years between input years including input years. Now apart from first all remaining years has be generated twice. Please check out the sample input values and expected output below.

Output-Screen Shot

Sample Input

DECLARE @FromYear AS INT = 2010 /* Start Year Check */
DECLARE @ToYear AS INT = 2016 /* To Year Check */

Expected Output

Script

Use below script to create table and insert sample data into it.

--


DECLARE @FromYear AS INT = 2010  /* Start Year Check */
DECLARE @ToYear   AS INT = 2016  /* To Year Check */
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using LAG Analytical Function

--

DECLARE @FromYear AS INT = 2010  /* Start Year Check */
DECLARE @ToYear   AS INT = 2016  /* To Year Check */

SELECT n Years
FROM 
(
	SELECT DISTINCT @FromYear + number n FROM Master..Spt_Values
	WHERE number >= 0 and number <= (@ToYear - @FromYear) )u UNION ALL SELECT * FROM ( SELECT LEAD(n) OVER (ORDER BY @FROMYear) y FROM ( SELECT DISTINCT @FromYear + number n FROM Master..Spt_Values WHERE number >= 0 and number <= (@ToYear - @FromYear) 
)u ) x
WHERE y IS NOT NULL
ORDER BY n

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com