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 🙂

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 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/