An improved version of my previous code. 😉

IF OBJECT_ID('TablesInformation') IS NOT NULL

DROP TABLE TablesInformation;

CREATE TABLE TablesInformation(

name ...

This is another formula to get there.

WITH TestData AS(

SELECT GETDATE() - RAND(CHECKSUM(NEWID())) * 7 AS date1, GETDATE() + RAND(CHECKSUM(NEWID())) * 7 AS date2

...

Without having to rely on an undocumented procedure, you could use something like this:

IF OBJECT_ID('TablesInformation') IS NOT NULL

DROP TABLE TablesInformation;

CREATE TABLE TablesInformation(

name...

As you said you're looking for an update query, I thought on posting an answer using some 2012 capabilities. Along with the use of updatable CTE's which work under the...

But your excel file with the expected results didn't follow a common logic for all the rows.

GilaMonster (7/2/2015)

---

Informal survey before I make a fool of myself in an article (again)

Do you get the feeling that IT people, especially ones at the top-end of the field, are...

Thank you for the formatted expected results Lynn. You're right, the results are different. However, I'm not sure if the expected results are wrong or the query results are wrong.

For...

Sean Lange (7/2/2015)

---

Luis Cazares (7/2/2015)

---

jasona.work (7/2/2015)

---

Today is the kind of day (and it's only been an hour and a half since I got to work) that you clock watch...

OTOH, got...

gazy007 (7/2/2015)

---

Thanks Luis, But I am not getting right numbers for 2015 and 2016 as we need to get for the whole year rather than getdate() or today. I hope...

Probably your mind is already in a long weekend.

I'm not sure about your desired results, but I believe that you need to add a partition to your window functions.

SELECT

S_ID,

LAG(S_ACTV_CODE, 1)...

jasona.work (7/2/2015)

---

Today is the kind of day (and it's only been an hour and a half since I got to work) that you clock watch...

OTOH, got plenty to look forward...

Lynn Pettis (7/1/2015)

---

How do people keep their jobs when it is obvious they are dependent on the good will of strangers on the internet to diagnose and resolve database issues,...

Steve,

Just as an FYI, you can remove the ISNULL() by using the third parameter of LAG()

--Instead of

ISNULL(LAG(P.Lev, 1) OVER(PARTITION BY T.AccountID ORDER BY T.Datekey), 0) AS LAG_VALUE

-- Use

LAG(P.Lev, 1, 0)...

Steve,

You can test your code using SQL Fiddle