Compare Rows in the same Table

Question

Compare Rows in the same Table

bpowers

SSCertifiable

Points: 5483
More actions
August 7, 2014 at 1:13 pm

#291866

We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made. Any help or feedback will be appreciated.
Thanks

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

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

CodeMuddler SSC Eights! Points: 925 More actions · Answer 1

It would be nice if you could have included more information about how your environment works. Specifically if your audit table contains only historical values or if it also contains the most recent production value as well.

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

Can you provide a sample schema and data (just the basics, don't need to go nuts) that show how your data is organized, by what keys, and if you're update only vs. inclusive (think old records only vs. old and current in audit) and if you're single/double entry, etc.

There's a lot of ways and methods to do record auditing. A sample of your real data will help us get you where you need to be.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

bpowers SSCertifiable Points: 5483 More actions · Answer 3

Attached is an image of our table. The LINE_CHANGE_DOC_NO is an identity column and the PO number is the primary key on the table we are tracking.

Here I am looking at a specific PO and PO Line. Notice that the top record was updated on 8/5/2014 and the DUE_DATE changed from 8/6/2014 to 10/1/2014. We need to be able to return the PO and Line where the DUE_DATE has changed, preferably using a date range on the UPDATED_DTE column. Does that make sense? I appreciate any feedback.

Thanks

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

bpowers (8/7/2014)
Attached is an image of our table. The LINE_CHANGE_DOC_NO is an identity column and the PO number is the primary key on the table we are tracking.
Here I am looking at a specific PO and PO Line. Notice that the top record was updated on 8/5/2014 and the DUE_DATE changed from 8/6/2014 to 10/1/2014. We need to be able to return the PO and Line where the DUE_DATE has changed, preferably using a date range on the UPDATED_DTE column. Does that make sense? I appreciate any feedback.
Thanks

Makes sense, but curious. In your main table, since the last date this was changed is 8/5/14, does your primary table MATCH the data for 8/5, or is 8/5 the old record and something different is in the primary? This determines if you have to union in the primary table to detect if the topmost record is a change from the current or not... though if you don't care about the current record it's a moot question. It really comes into play in determine if DUE DATE hasn't changed for a few records but the last update changed it in the primary vs. the logged record and if you want it reported.

If you can turn that into consumable SQL for a sample #table (CREATE TABLE/INSERT) to work from I (or others) can help you with the code.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

bpowers SSCertifiable Points: 5483 More actions · Answer 5

Thanks for the feedback. Yes the most recent row in the change table reflects what the actual DUE_DATE is in the primary table. I tried the following CTE, but still no luck.

With DueDateCTE as

(

Select Row_Number() OVER (Order by LINE_CHANGE_DOC_NO DESC) as RowNumber

,PO

,LINE

,DUE_DATE

,UPDATED_DTE

from PO_LINE_CHANGE

)

--Actual Query

Select dd.PO

,dd.LINE

,dd.DUE_DATE

FROM DueDateCTE dd

LEFT JOIN DueDateCTE dd2 on dd.RowNumber=dd2.RowNumber-1

WHERE dd.DUE_DATE>dd2.DUE_DATE

--AND CONVERT(datetime,convert(varchar(10),dd.UPDATED_DTE,101))BETWEEN '8/5/2014' and '8/7/2014'

bpowers SSCertifiable Points: 5483 More actions · Answer 6

I should have attached this image instead, as it shows the table without the LINE filter. Since we need to compare the LINES we will first need to group or order by those before comparing.

CodeMuddler SSC Eights! Points: 925 More actions · Answer 7

Is this what you're after?

WITH cte (PO, LINE, DUE_DATE, UPDATED_DTE, rn)

AS

(

SELECT

PO

, LINE

, DUE_DATE

, UPDATED_DTE

, ROW_NUMBER() OVER (PARTITION BY PO, LINE ORDER BY UPDATED_DTE DESC) AS [rn]

FROM PO_LINE_CHANGE

)

SELECT

cte1.PO

, cte1.LINE

, cte1.DUE_DATE

, cte1.UPDATED_DTE

, cte2.DUE_DATE AS [OLD_DUE_DATE]

FROM cte cte1

JOIN cte cte2 ON

cte2.PO = cte1.PO

AND cte2.LINE = cte1.LINE

AND cte2.rn = 2

WHERE

cte1.rn = 1

AND cte1.DUE_DATE <> cte2.DUE_DATE;

blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler

bpowers SSCertifiable Points: 5483 More actions · Answer 8

Dude you freakin Rock! Thank you very much and I appreciate all the feedback and support from everyone here. Greatly appreciate!