Change in row values identification

Question

Post reply

Change in row values identification

Rehan Ahmad

Right there with Babe

Points: 791

March 19, 2019 at 3:10 am

#380060

Dear Fellows,

How could be find the records where specific values/field changed. In the sample table we need to find those accounts where class has been changed based on date.

SQL Code for test table creation is as under for creation of table:



CREATE TABLE TBL_TESTLOG(



	[ACCOUNT] [varchar](50) NULL,



	[CLASS] [varchar](50) NULL,



	[DATE] [DATE] NULL



)



GO



INSERT INTO TBL_TESTLOG (ACCOUNT,CLASS,DATE)



VALUES 



('1001','A','19-Mar-19'),



('1001','F','18-Mar-19'),



('1001','F','17-Mar-19'),



('1002','E','16-Mar-19'),



('1002','C','15-Mar-19'),



('1002','F','14-Mar-19'),



('1002','F','13-Mar-19'),



('1003','D','8-Mar-19'),



('1003','F','7-Mar-19'),



('1003','F','6-Mar-19'),



('1004','B','5-Mar-19'),



('1004','C','4-Mar-19'),



('1004','C','3-Mar-19'),



('1005','D','12-Mar-19'),



('1005','F','11-Mar-19'),



('1005','A','10-Mar-19'),



('1005','F','9-Mar-19')



GO

Output/Required Result:

ACCOUNT	NEW_CLASS	OLD_CLASS	CHANGED
1001	A	F	19-Mar-19
1002	E	C	16-Mar-19
1002	C	F	15-Mar-19
1003	D	F	8-Mar-19
1004	B	C	5-Mar-19
1005	D	F	12-Mar-19
1005	F	A	11-Mar-19
1005	A	F	10-Mar-19

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99009 More actions · Answer 1

You can use a CTE and LAG to easily achieve this:

WITH CTE AS(
    SELECT ACCOUNT,
           CLASS AS NEW_CLASS,
           LAG(CLASS,1,CLASS) OVER (PARTITION BY ACCOUNT ORDER BY [DATE] ASC) AS OLD_CLASS,
           [DATE] AS CHANGED
    FROM TBL_TESTLOG)
SELECT ACCOUNT,
       NEW_CLASS,
       OLD_CLASS,
       CHANGED
FROM CTE
WHERE NEW_CLASS != OLD_CLASS
ORDER BY ACCOUNT,
         CHANGED;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Rehan Ahmad Right there with Babe Points: 791 More actions · Answer 2

Thom A - Tuesday, March 19, 2019 5:54 AM

You can use a CTE and LAG to easily achieve this:

WITH CTE AS(

SELECT ACCOUNT,

    CLASS AS NEW_CLASS,

    LAG(CLASS,1,CLASS) OVER (PARTITION BY ACCOUNT ORDER BY [DATE] ASC) AS OLD_CLASS,

    [DATE] AS CHANGED

FROM TBL_TESTLOG)

SELECT ACCOUNT,

   NEW_CLASS,

   OLD_CLASS,

   CHANGED

FROM CTE

WHERE NEW_CLASS != OLD_CLASS

ORDER BY ACCOUNT,

   CHANGED;

Thanks Thom, It worked fine with CTE. Would you please suggest any GOOD book or source over the internet where new functionalities such as CTE and LAG could be learned.

Could the same task done without CTE in case for older versions of SQL

Thom A SSC Guru Points: 99009 More actions · Answer 3

Rehan Ahmad - Tuesday, March 19, 2019 6:52 AM

Thanks Thom, It worked fine with CTE. Would you please suggest any GOOD book or source over the internet where new functionalities such as CTE and LAG could be learned.

Could the same task done without CTE in case for older versions of SQL

I'm not much of a book reader I'm afraid, I learned a lot of what I did by participating on websites such as SSC and reading the questions and answers as well as the articles; they are an invaluable resource.

CTEs were introduced in SQL Server 2005; if you're using an older version than that (why though, even 2005 is completely out of support) you'll need to use a subquery. LAG, however, was introduced in SQL Server 2012. 2008 is almost completely out of support as well, but you'll need to use a LEFT JOIN to the previous row if you are still using a 2008 instance.

If you're using SQL Server 2000 (or older) you have far worse problems than not having access to CTEs.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Rehan Ahmad Right there with Babe Points: 791 More actions · Answer 4

Thom A - Tuesday, March 19, 2019 6:58 AM

Rehan Ahmad - Tuesday, March 19, 2019 6:52 AM

Thanks Thom, It worked fine with CTE. Would you please suggest any GOOD book or source over the internet where new functionalities such as CTE and LAG could be learned.

Could the same task done without CTE in case for older versions of SQL

I'm not much of a book reader I'm afraid, I learned a lot of what I did by participating on websites such as SSC and reading the questions and answers as well as the articles; they are an invaluable resource.

CTEs were introduced in SQL Server 2005; if you're using an older version than that (why though, even 2005 is completely out of support) you'll need to use a subquery. LAG, however, was introduced in SQL Server 2012. 2008 is almost completely out of support as well, but you'll need to use a LEFT JOIN to the previous row if you are still using a 2008 instance.

If you're using SQL Server 2000 (or older) you have far worse problems than not having access to CTEs.

Thanks for the insights.

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

Rehan Ahmad - Tuesday, March 19, 2019 6:52 AM

Thom A - Tuesday, March 19, 2019 5:54 AM

You can use a CTE and LAG to easily achieve this:

WITH CTE AS(

SELECT ACCOUNT,

    CLASS AS NEW_CLASS,

    LAG(CLASS,1,CLASS) OVER (PARTITION BY ACCOUNT ORDER BY [DATE] ASC) AS OLD_CLASS,

    [DATE] AS CHANGED

FROM TBL_TESTLOG)

SELECT ACCOUNT,

   NEW_CLASS,

   OLD_CLASS,

   CHANGED

FROM CTE

WHERE NEW_CLASS != OLD_CLASS

ORDER BY ACCOUNT,

   CHANGED;

Thanks Thom, It worked fine with CTE. Would you please suggest any GOOD book or source over the internet where new functionalities such as CTE and LAG could be learned.

Could the same task done without CTE in case for older versions of SQL

Books by Itzik Ben-Gan. In particular, "Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions". (I guess he got paid by number of words in the title :D).

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!