SQL PUZZLE | Missing Dates for each month ?? | SQL Interview Question

In this puzzle you have to find out the missing dates for each month. Please check out sample input and expected output. The should be generic in nature.

Sample Input

Month Date
201806 1
201806 2
201806 5
201806 6
201806 8
201807 1
201807 4
201807 6
201809 1
201809 2

Expected output

Month MissingDates
201806 3
201806 4
201806 7
201807 2
201807 3
201807 5

Script – DDL and INSERT sample data

 ```-- CREATE TABLE Dts ( [Month] INT ,[Date] INT ) GO INSERT INTO Dts VALUES (201806,1), (201806,2), (201806,5), (201806,6), (201806,8), (201807,1), (201807,4), (201807,6), (201809,1), (201809,2) GO SELECT * FROM Dts GO -- ```

SOLUTION – 1

 ```-- ;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) Number from SingleDigits as d1, SingleDigits as d2 ), CTE1 AS ( SELECT [Month] , MIN([Date]) MinDt, MAX([Date]) Maxdt FROM Dts GROUP BY [Month] ) SELECT c.Month [Month], s.Number MissingDates FROM CTE1 c INNER JOIN Series s on number >= MinDt AND number <= Maxdt LEFT JOIN Dts dt on s.Number = dt.[date] AND c.[month] = dt.[month] WHERE dt.[date] IS NULL -- ```

OUTPUT – 1

 ```-- Month MissingDates ----------- ------------ 201806 3 201806 4 201806 7 201807 2 201807 3 201807 5 (6 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/