November 25, 2014 at 12:58 pm
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.
November 25, 2014 at 2:08 pm
Great job posting ddl and sample data. Can you take it one step further and post what the desired output would be for this sample data? I think I get it but am not 100% certain.
You did post the desired output....lemme take a look at this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 25, 2014 at 2:30 pm
Here's one possible solution. It creates a way to make a self join with the previous reportrundate for each invoiceno and invoicepart. Feel free to ask any questions that you have so you can fully understand it.
WITH CTE AS(
SELECT * ,
ROW_NUMBER() OVER( PARTITION BY InvoiceNo, InvoicePart ORDER BY ReportRunDate) rn
FROM #SampleData
)
SELECT c1.InvoiceNo,
c1.ReportRunDate,
c1.InvoicePart,
c1.Principle,
c1.interest,
c1.other,
CASE WHEN c1.Principle = c2.Principle
AND c1.interest = c2.interest
AND c1.other = c2.other THEN 'N'
ELSE 'Y'
END ChangeFromPrevious
FROM CTE c1
LEFT
JOIN CTE c2 ON c1.InvoiceNo = c2.InvoiceNo
AND c1.InvoicePart = c2.InvoicePart
AND c1.rn = c2.rn + 1
December 1, 2014 at 12:34 pm
Awesome. That worked like a charm. Thank you so much for your help!
December 1, 2014 at 12:36 pm
myjobsinus (12/1/2014)
Awesome. That worked like a charm. Thank you so much for your help!
Thank you for the feedback.
The question is, do you understand how does it work?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply