Compare Rows in same Table for tracking changes.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compare Rows in same Table for tracking changes.

Author

Topic

Alvin_SQL007
Starting Member

12 Posts

Posted - 2014-11-25 : 15:01:41

Hi All,

I need help with writing T-SQL query to compare rows in same table and track changes over time. following is a temp table script with sample data.

CREATE TABLE #SampleData (InvoiceNo VARCHAR(20), ReportRunDate Date, InvoicePart int,Principle int,interest int,other int)
INSERT INTO #SampleData VALUES ('1234','02/22/2014',1,50,10,5)
INSERT INTO #SampleData VALUES ('1234','02/22/2014',2,100,15,4)

INSERT INTO #SampleData VALUES ('1234','02/23/2014',1,50,10,5)
INSERT INTO #SampleData VALUES ('1234','02/23/2014',2,100,15,4)

INSERT INTO #SampleData VALUES ('1234','02/25/2014',1,40,10,5)
INSERT INTO #SampleData VALUES ('1234','02/25/2014',2,100,15,4)

INSERT INTO #SampleData VALUES ('1234','02/26/2014',1,40,10,5)
INSERT INTO #SampleData VALUES ('1234','02/26/2014',2,75,9,4)

INSERT INTO #SampleData VALUES ('1234','02/28/2014',1,40,10,2)
INSERT INTO #SampleData VALUES ('1234','02/28/2014',2,75,9,4)

SELECT * FROM #SampleData

DROP TABLE #SampleData

so for each invoice number and Invoice part, i need to track changes in Principle,Interest and other columns per report run date.comparing it to previous report run date.

and following is a temp table with desired data (new column with Y or N tag)

CREATE TABLE #DesiredData (InvoiceNo VARCHAR(20), ReportRunDate Date, InvoicePart int,Principle int,interest int,other int,ChangeFromPrevious varchar(4))
INSERT INTO #DesiredData VALUES ('1234','02/22/2014',1,50,10,5,'Y')
INSERT INTO #DesiredData VALUES ('1234','02/22/2014',2,100,15,4,'Y')

INSERT INTO #DesiredData VALUES ('1234','02/23/2014',1,50,10,5,'N')
INSERT INTO #DesiredData VALUES ('1234','02/23/2014',2,100,15,4,'N')

INSERT INTO #DesiredData VALUES ('1234','02/25/2014',1,40,10,5,'Y')
INSERT INTO #DesiredData VALUES ('1234','02/25/2014',2,100,15,4,'N')

INSERT INTO #DesiredData VALUES ('1234','02/26/2014',1,40,10,5,'N')
INSERT INTO #DesiredData VALUES ('1234','02/26/2014',2,75,9,4,'Y')

INSERT INTO #DesiredData VALUES ('1234','02/28/2014',1,40,10,2,'Y')
INSERT INTO #DesiredData VALUES ('1234','02/28/2014',2,75,9,4,'N')

SELECT * FROM #DesiredData

DROP TABLE #DesiredData

I tried self joins and rank functions but having difficulty in getting the output. please help. many thanks in advance.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-26 : 08:32:23

[code]
WITH ReportNos
AS
(
SELECT InvoiceNo, ReportRunDate, InvoicePart, Principle, interest, other
,DENSE_RANK() OVER (PARTITION BY InvoiceNo ORDER BY ReportRunDate) AS ReportNo
FROM #SampleData
)
SELECT R1.InvoiceNo, R1.ReportRunDate, R1.InvoicePart, R1.Principle, R1.interest, R1.other
,CASE
WHEN R1.Principle = R2.Principle
AND R1.interest = R2.interest
AND R1.other = R2.other
THEN 'N'
ELSE 'Y'
END AS ChangeFromPrevious
FROM ReportNos R1
LEFT JOIN ReportNos R2
ON R1.InvoiceNo = R2.InvoiceNo
AND R1.InvoicePart = R2.InvoicePart
AND R1.ReportNo = R2.ReportNo + 1;
[/code]

Alvin_SQL007
Starting Member

12 Posts

Posted - 2014-12-01 : 14:34:22

Awesome. That worked like a charm. Thank you so much for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources