Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

FiscalPeriod
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

Expected Output

FiscalPeriod
201401
201501
201601

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

Advertisements