SQL Puzzle | The Fiscal Puzzle – II

Well this is a simple puzzle and should be easily solved by many people. Here we have to accept 2 parameters say @FromFiscal & @ToFiscal. Let’s say values for these parameters are below-

DECLARE @FromFiscal AS INT = ‘201401’
DECLARE @ToFiscal AS INT = ‘201701’

Here first four digits are Fiscal Year, which are 2014 & 2017. So we have to get data for Fiscal Year 2014 to 2017.
Last 2 digits are Fiscal month. For this puzzle fiscal month in @FromFiscal & @ToFiscal will always be same. SO here it is 01.

So collectively we have fetch data for Fiscal Period – 201401 , 201501 , 201601 , 201701

Please check out the sample input values and expected output below.

Sample Input

Expected Output

Script

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

--

CREATE TABLE Fiscals
(
	FiscalPeriod VARCHAR(6)
)
GO

INSERT INTO Fiscals VALUES
('201401'),
('201402'),
('201403'),
('201404'),
('201405'),
('201406'),
('201407'),
('201408'),
('201409'),
('201410'),
('201411'),
('201412'),
('201501'),
('201502'),
('201503'),
('201504'),
('201505'),
('201506'),
('201507'),
('201508'),
('201509'),
('201510'),
('201511'),
('201512'),
('201601'),
('201602'),
('201603'),
('201604'),
('201605'),
('201606'),
('201607'),
('201608'),
('201609'),
('201610'),
('201611'),
('201612'),
('201702'),
('201703'),
('201704'),
('201705'),
('201706'),
('201707'),
('201708'),
('201709'),
('201710'),
('201711'),
('201712')
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 Select & Cross Join

--

DECLARE @FromFiscal AS INT  =  '201401'
DECLARE @ToFiscal   AS INT  =  '201701'

SELECT f.FiscalPeriod FROM Fiscals f
INNER JOIN 
(
	SELECT CONCAT(FiscalYear,FiscalMonth) FiscalPeriod FROM 
	(
		SELECT DISTINCT number + CAST(LEFT(@FromFiscal,4) AS INT) FiscalYear , k FiscalMonth FROM master..spt_values CROSS JOIN (SELECT RIGHT(@ToFiscal,2) k) Months
		WHERE number >= 0 and number <= CAST(LEFT(@ToFiscal,4) AS INT) - CAST(LEFT(@FromFiscal,4) AS INT)
	)b
)u ON U.FiscalPeriod = f.FiscalPeriod
 
--

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