Tags

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


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

Advertisements