Comparing every row in one table

Question

Post reply

Comparing every row in one table

RedSoxRay

SSC Enthusiast

Points: 194
More actions
July 14, 2021 at 5:50 pm
Go to Answer
#3907945

I want to compare values in every row of one table. For example, if I have a table that has the following columns: Invoice Number, Status, TimeStamp. So for example, let's say I had the following data:

Invoice Number Status TimeStamp

1 Not Paid 7-1-21

1 Partial Payment 7-7-21

1 Partial Payment 7-9-21

1 Paid In Full 7-12-21

I want to compare every row in the table to see when one of these values has changed. So, for example, between the first TimeStamp and the second TimeStamp the status changed, nothing changed from second and third iteration, but then the status changed again from #3 to #4 timestamp....then the result set would list the Timestamp and each change made.

Is it possible to do this, or, would I be better served to have a Cube created and query using Analysis Services.

Thanks!

ScottPletcher

SSC Guru

Points: 100262

July 14, 2021 at 10:30 pm

Answer

#3908031

Hmm, ran fine for me, but I'm on SQL 2016. I think that code should work all way back to 2008, or at least 2012.

We could join to the entire previous row, so would not have to do a separate one for each column.

Certainly no need to apologize, we're here to help introduce people to SQL elements they're not familiar with yet.

CREATE TABLE #data (Invoice_Number int NOT NULL, Status varchar(30) NOT NULL, TimeStamp date NOT NULL)
INSERT INTO #data VALUES
    (1,'Not Paid', '7-1-21'),
    (1,'Partial Payment', '7-7-21'),
    (1,'Partial Payment', '7-9-21'),
    (1,'Paid In Full', '7-12-21')

;WITH cte_data_with_row_num AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Invoice_Number ORDER BY TimeStamp) AS row_num
    FROM #data
)
SELECT *
FROM cte_data_with_row_num curr
LEFT OUTER JOIN cte_data_with_row_num prev ON prev.Invoice_Number = curr.Invoice_Number AND prev.row_num = curr.row_num - 1

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!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Michael L John One Orange Chip Points: 26551 More actions · Answer 1

Why wouldn't this work?

SELECT [Invoice Number], Status, MIN(TimeStamp) ChangeDate

FROM YourTable

GROUP BY [Invoice Number], Status

ORDER BY MIN(TimeStamp), [Invoice Number], Status,

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

;WITH cte_test_data AS(
    SELECT * FROM ( VALUES
        (1,'Not Paid', CAST('7-1-21' AS date)),
        (1,'Partial Payment', '7-7-21'),
        (1,'Partial Payment', '7-9-21'),
        (1,'Paid In Full', '7-12-21') ) AS data(Invoice_Number, Status, TimeStamp)
)
SELECT *
FROM (
    SELECT *, LAG(Status, 1) OVER(PARTITION BY Invoice_Number ORDER BY TimeStamp) AS Previous_Status
    FROM cte_test_data
) AS derived
WHERE Status <> Previous_Status

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!

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 3

Thanks for the replies. Scott, when I tried your solution with my actual code below, it didn't like the portion "AS data(InvoiceNumber, InvoiceStatus, Timestamp"; it gave me a syntax error near "AS". When I removed that portion, it worked. I'm not sure why that is...

;WITH cte_test_data AS (

SELECT InvoiceNumber, InvoiceStatus, TimeStamp

FROM Invoices ) AS data(InvoiceNumber, InvoiceStatus, TimeStamp)

SELECT *

FROM (

SELECT *, LAG(InvoiceStatus, 1) OVER(PARTITION BY InvoiceNumber ORDER BY TimeStamp ) AS Previous_Status

FROM cte_test_data

) AS derived

WHERE InvoiceStatus <> Previous_Status

Having said that, what if I wanted to do this for every column in that table? Would that require multiple iterations of your code sample? In other words, a separate CTE for each column, or would there be another way to do it with one CTE by modifying the WHERE clause. Apologies as the PARTITION and LAG functions are new to me.

Thanks!

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 4

Awesome! Thank you....that has me well on my way to my final goal.

One final question; if I connect to my DB and open SSMS using my own credentials, when I run your statement above, I get a showplan error. Yet, if I connect using Admin credentials it runs fine.

I'm just curious more than anything, but why would SQL generate such an error using my own id? I realize it must be permissions related, but is it because of the use of PARTITION?

Thanks again for all your help, very much appreciated!

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

Your own login must not have SHOWPLAN permissions. Check to see if someone will GRANT you that permission.

As a DBA, I give that permission to everyone. I don't see how it can do any harm.

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!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

Now that you have a couple of answers, I have to ask... why do you need to do this? What will it be used for? What business purpose does it solve?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

RedSoxRay SSC Enthusiast Points: 194 More actions · Answer 7

RedSoxRay

SSC Enthusiast

Points: 194

July 15, 2021 at 3:15 pm

#3910302

Jeff:

It's for auditing purposes. We want to be able to query a specific timespan and generate a report on the values that changed. So, in my example above, I want to find out what changed with a specific invoice from 7-1-21 to 7-15-21. It will tell me that the status changed among other things.