Tags

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


SQL PUZZLE | Generate last 5 Quarters with their years | SQL Interview Question

In this puzzle you have to generate last 5 QUARTERs with their years The logic should be generic in nature.Please see the sample input and expected output.

Sample Input

Year Quater
2018 2

Expected Output

Year Quater
2018 1
2018 2
2017 3
2017 4
2017 2

Script – DDL and INSERT Sample Data

--

DECLARE @Y AS INT = 2018
DECLARE @DATE AS INT = DATEPART(QUARTER,GETDATE())
DECLARE @NoOFQuartersToBeProcessed int = 5

--

SOLUTION – 1

--

DECLARE @Y AS INT = 2018
DECLARE @DATE AS INT = DATEPART(QUARTER,GETDATE())
DECLARE @NoOFQuartersToBeProcessed int = 5
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
),CTE1 AS
(
	SELECT @Y-(Number-1) Y FROM Series
	WHERE (@Y-(Number-1)) >= (@Y-5)
)
,CTE2 AS
(
	SELECT * FROM CTE1
	CROSS APPLY ( VALUES (1),(2),(3),(4)) as t(u) 
)
SELECT TOP (@NoOFQuartersToBeProcessed) Y [Year], u [Quater] FROM CTE2
WHERE CONCAT(Y, CONCAT('0',U)) <= CONCAT(@Y, CONCAT('0',@DATE))
ORDER BY Y DESC


--

Output – 1

--

Year        Quater
----------- -----------
2018        1
2018        2
2017        3
2017        4
2017        2

(5 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com

Advertisements