CODEWITHSUNDEEP
sqlsql-server
user3432824
user3432824

Reputation: 111

Sql Calculating history from migrated data

I have some data like given below.

Table with some sample data

The problem is, I have to calculate the History on the basis of number. Suppose number is unique identity of a "submission form". Every time there is a change in value of any column i have to extract it change.

Below is structure for history table.

Structure of history table

Now we have two form in system. with number 2 and 4. So the result history data should look like this.

Result of history table for form number 2 and 4

Upvotes: 1

Views: 56

Answers (2)

valex
valex

Reputation: 24134

In MSSQL you can use function LAG() OVER () to get previous value.

WITH CT AS
(
  SELECT
       ID,
       number as submissionID, 
       'amount' as Field,
       LAG(amount) OVER (PARTITION BY number ORDER BY ID)as OldValue,
       amount as NewValue
  FROM T
) 

SELECT * FROM CT 
WHERE OldValue IS NOT NULL 
ORDER BY submissionID,ID

SQLFiddle demo

Another way is to use ROW_NUMBER() function to connect current record with the previous so you can get all values from the previous record:

WITH CT AS
(
  SELECT
       T.*, 
       ROW_NUMBER() OVER (PARTITION BY number ORDER BY ID DESC)as RowNum
  FROM T
) 

SELECT CT.*,CT2.amount as oldValue FROM CT 
JOIN CT as CT2 on CT.Number=CT2.Number 
     AND CT.RowNum+1=CT2.RowNum
ORDER BY CT.number,CT.ID

SQLFiddle demo

Upvotes: 1

StanislavL
StanislavL

Reputation: 57381

select 
  d1.number,
  'amount' as field,
  d1.amount as newValue,
  d2.amount as oldValue
from data d1 join data d2 on d1.number=d2.number and d1.id=d2.id-1

Won't work if id have gaps but you caan use row number to replace the id's solution

Upvotes: 0

Related Questions