SQL With RaviMartha

Saturday, February 20, 2021

Finding Missing Sequence Dates using SQL

Link for video answer:

Queries:

CREATE TABLE tblEmployee_HireDates (HireDate DATE)

INSERT tblEmployee_HireDates values ('2020-01-01'),('2020-01-03'),('2020-01-05'),('2020-01-07'),('2020-01-10'),('2020-01-12')

SELECT * FROM tblEmployee_HireDates

DECLARE @StartDate Date, @EndDate Date;

SELECT @StartDate = MIN(HireDate) FROM tblEmployee_HireDates

SELECT @EndDate = MAX(HireDate) FROM tblEmployee_HireDates

;WITH Emp_CTE (DateSequence) AS

(

SELECT @StartDate --> Anchor Query

UNION ALL

SELECT DATEADD(DD,1,DateSequence) FROM Emp_CTE WHERE DateSequence < @EndDate --> Recusive Query

)

--SELECT * FROM Emp_CTE

SELECT EC.DateSequence AS Missing_Sequence FROM Emp_CTE EC

LEFT JOIN tblEmployee_HireDates E

ON EC.DateSequence = E.HireDate

WHERE E.HireDate IS NULL

Option (MAXRECURSION 0)

You tube Palylists:

SQL Interview Questions:

https://www.youtube.com/watch?v=YZB8p2Anhr4&list=PLgGPoIouio2B3sXJQd_pQwy6UWo68vp6j

SQL Tutorial Series for Beginners:

https://www.youtube.com/watch?v=Tfu4EQ1E8cs&list=PLgGPoIouio2DaI1lC1Eb0nMmYnk-28dft

SQL Tips and Tricks:

https://www.youtube.com/watch?v=zq6IEqJKnGk&list=PLgGPoIouio2A12x8PhqUcOIO1EyuMpPCc

SSIS Video Series:

https://www.youtube.com/watch?v=1HkSyYl7Pr0&list=PLgGPoIouio2Amr9zjBmZQre64HgkwMXjk

SQL In 2 Minutes:

https://www.youtube.com/watch?v=xDqueXgL7r8&list=PLgGPoIouio2AXEk2WixZFJEcDKWEeZFYU