Category Archives: Common Table Expression

An Example of Using LEAD and LAG Functions from SQL Server 2012

I recently encountered a technical interview question and it was something like this: A company has its door access logging system and the company policy dictates that every employee’s card-in/card-out records shall reflect his/her true presence in or absence from … Continue reading

Posted in Common Table Expression, T-SQL Script | Tagged , , | Leave a comment

Two Examples of Recursion with T-SQL

T-SQL is very good for dealing with set-based data, but it doesn’t shy away from recursion either. You can use either user defined functions (UDF) or common table expression (CTE) to achieve this and I put up hereunder two examples … Continue reading

Posted in Common Table Expression, Recursion, T-SQL Script, User Defined Function | Tagged , , | Leave a comment

Remove Full Duplicates by Using row_number() Function and Common Table Expression

This blog article is inspired by another very informative one¬†which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated … Continue reading

Posted in Common Table Expression, T-SQL Script | Tagged , , , | Leave a comment