SQL Puzzle | The Fiscal Puzzle

Puzzle Statement

This is a very good puzzle, Here you have to accept 2 input parameters. Both the parameters are string.

1. The first 4 characters will be year and the last 2 are month.

2. Basically we will have start year month and end year month as inputs. Normally in fiscal calendars you will have this kind of scenario.

3. Input table will also contain year and month

4. You have to write a query to fetch data based on the range end user has provided.

5. Please check out the input table & some sample input strings with their expected output below-

Sample Input

FPeriod	FYear	Data
7	2009	1000
8	2009	1020
9	2009	1300
10	2009	1400
1	2010	1000
2	2010	2100
3	2010	1900
4	2010	2300

Expected Output

/* Output for ‘200908’ – ‘201002’ */

FPeriod	FYear	Data
8	2009	1020
9	2009	1300
10	2009	1400
1	2010	1000
2	2010	2100

/* Output for ‘201001’ – ‘201002’ */

FPeriod	FYear	Data
1	2010	1000
2	2010	2100

/* Output for ‘201004’ – ‘201005’ */

FPeriod	FYear	Data
4	2010	2300

Rules/Restrictions – The solution should be should use “SELECT” statement or “CTE”.

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

--


CREATE TABLE GetDates
(
	 FPeriod INT
	,FYear INT
	,Data INT
)
GO


INSERT INTO GetDates VALUES
(7,2009,1000),
(8,2009,1020),
(9,2009,1300),
(10,2009,1400),
(1,2010,1000),
(2,2010,2100),
(3,2010,1900),
(4,2010,2300)
GO




--

SOLUTION 1 | Using Casting

--


DECLARE @Start AS VARCHAR(6) = '200908'
DECLARE @End AS VARCHAR(6) = '201002'


SELECT FPeriod,FYear,Data FROM 
(
	SELECT * , CAST(CONCAT(FYear , RIGHT(CONCAT('0',CAST(FPeriod AS VARCHAR(2))),2)) AS INT) Keys FROM GetDates
)t
WHERE Keys >= CAST(@Start AS INT) AND Keys <= CAST(@End AS INT)

GO


--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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