Missing Sequence Number in SQL | Recursive CTE | Generate Sequence Number | SQL Interview Q&A

SQL With RaviMartha
SQL With RaviMartha
6.3 هزار بار بازدید - 4 سال پیش - ;WITH Emp_CTE (ID) AS
;WITH Emp_CTE (ID) AS
(
SELECT 1  -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
   )
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0) -- MAXRECURSION Query Hint

;WITH Emp_CTE (ID) AS
(
SELECT 1  -- Anchor query
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) 14 -- Recursive Query
   )
SELECT * FROM Emp_CTE

-- PART 2

CREATE TABLE tblEmployee1 (EmpID INT)

INSERT tblEmployee1 values (51000),(51001),(51004),(51009),(51013),(51017),(51020),(51025),(51026)

SELECT * FROM tblEmployee1

DECLARE @StartID INT, @EndID INT;
SELECT @StartID = MIN(EmpID) FROM tblEmployee1
SELECT @EndID = MAX(EmpID) FROM tblEmployee1

;WITH Emp_CTE (ID) AS
(
SELECT @StartID
UNION ALL
SELECT ID + 1 FROM Emp_CTE WHERE ID (Lessthansymbol) @EndID
)
SELECT EC.ID AS Missing_Sequence FROM Emp_CTE EC
LEFT JOIN tblEmployee1 E
ON EC.ID = E.EmpID
WHERE E.EmpID IS NULL
Option (MAXRECURSION 0)


*/
V22: Comma Separated values to multiple rows | string_split() | SQL Interview Q&A | CROSS APPLY
Comma Separated values to multiple ro...

V21: Comma Separated values in SQL | XML PATH | SQL to XML Format | Row Tag| Root Tag | SQL Interview Q&A
Comma Separated values in SQL | XML P...

This channel would provide new videos on SQL, ETL and Data warehouse concepts. I would create a separate play list for SQL Tutorials for beginners, advanced concepts, Interview questions and data warehousing concepts

Feel free to drop me an email at [email protected] in case of any queries you have in SQL or clarifications on the videos I have done :)

Blog: https://sqlwithravimartha.blogspot.co...

Video 12: Import Tables from one Server to another Using SSMS | Import tables using SQL Query in SSMS --*Import Tables from one Server to anot...
Blog: https://sqlwithravimartha.blogspot.co...

Video 11: Table Variables in SQL Server | Pound Tables | Table Variable in SQL | Table Variable in Tempdb --*Table Variables in SQL Server | Pound...
Blog: https://sqlwithravimartha.blogspot.co...

Video 10: Quick Practical onTemporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table--*Quick Practical on Temporary tables i...
Blog: https://sqlwithravimartha.blogspot.co...

Video 9: Temporary tables in SQL Server | Temp Tables in SQL | Local Temp Table | Global Temp Table -- *Temporary tables in SQL Server | Temp...
Blog: https://sqlwithravimartha.blogspot.co...

Video 8: Running total & Avg in SQL | Cumulative Sum & Avg in SQL | Calculating running total & Avg in SQL -- *Running total & Avg in SQL | Cumulati...
https://sqlwithravimartha.blogspot.co...

Video 7 Title: Length Vs DataLength Vs ColumnLength | Len() | DataLength() | Col_Length | Column length in SQL -- * Length Vs DataLength Vs ColumnLength ...
https://sqlwithravimartha.blogspot.co...

Video 6 Title: Tricky CASE Statement Interview Questions | CASE WHEN | WHEN Clause | CASE Statement in SQL -- * Tricky CASE Statement Interview Quest...
https://sqlwithravimartha.blogspot.co...

Video 5 Title: Delete Duplicates in SQL by retaining one Unique record | Row Duplicates | Business Key Duplicates -- * Delete Duplicates in SQL by retaining...
https://sqlwithravimartha.blogspot.co...

Video 4 Title: Difference between TRUNCATE, DELETE and DROP -- * Difference between TRUNCATE, DELETE a...
https://sqlwithravimartha.blogspot.co...

Video 3 Title: Sorting in SQL | Order By | Conditional Sorting| Unusual Sorting -- *Sorting in SQL | Order By | Condition...
https://sqlwithravimartha.blogspot.co...

Video 2 Title: Difference between Primary Key and Unique Key -- *Difference between Primary Key and Un...
https://sqlwithravimartha.blogspot.co...

Video 1 Title: Logical Processing of SELECT Statement -- *Logical Processing of SELECT Statemen...
https://sqlwithravimartha.blogspot.co...

Blog: https://sqlwithravimartha.blogspot.com/

Facebook Page:
Facebook: SQL-With-RaviMartha-109497027391239
Facebook: ETL-DWH-Testing-100494175057349

Youtube Channel:
@sqlwithravimartha6357
4 سال پیش در تاریخ 1399/10/27 منتشر شده است.
6,365 بـار بازدید شده
... بیشتر