Well, the documentation states that these new columns were added from SQL 2016.  However, they are available in my SQL 2014 instance.


SELECT @@VERSION;


You could achieve this with a MERGE statement.
There is no need for an explicit transaction, as it is one one statement that will succeed or rollback as a whole.

The query works.

What is your expected outcome?

The format of your query appears to ba MySQL format.

In T-SQL, we can do it like this ...

Create some sample data

CREATE TABLE...

Please provide us with CREATE TABLE scripts for the 2 tables, as well as INSERT statements with sample data, and the expected results.

Although off topic, in SQL 2008 the query optomiser always assumes 1 record in @staging_table_var.  This is because SQL does not maintain stats for @Table variables.

If you change to...

You could try to get the Primary number first ...

WITH ctePhoneNumber AS (
SELECT
  pn.PersonID,
  pn.PhoneNumber,
  rn =...

Firstly, start off by using Jeff Moden's Tally function

CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including...

You can also provide a Default value for the LEAD/LAG Window functions.

In this case, it means not having to add the extra AND PREVIOUS_CODE IS NOT NULL in the...

Yes, there s a single open tran for the full duration of the looping and deleting.
Since there is no where clause ... why would he not simply do a...

The following code will work, so long as you only have 5 days per VM.

WITH cteDayNum AS (
SELECT *
  , [DayNum] =...


SELECT
  ID
, DateDifference = SUM(DATEDIFF(DAY, ReportedDate, CompletedDate))
FROM #ReportTable
GROUP BY ID;