Change in row values identification

Question

Change in row values identification

Rehan Ahmad

Right there with Babe

Points: 791
More actions
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: 99130 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: 99130 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: 101103 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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