TSQL query history table field change count

Question

I need to find an efficient query to count the number of field changes per history table entry.

Each time any data value changes in the Customer Table, a copy of the record is entered into the history table.

I would like a way to count the numbers of changes per entry into the history table. Each field would compare the current entry with the previous entry to determine a difference.

Is there a good way to do this? Without a sproc?

enter image description here

Screenshot helps, but some sample data would attract more attention. — John Cappelletti, Aug 03 '16 at 18:01

EricZ · Accepted Answer · 2016-08-04T13:48:26.723

0

You may start something as the following. I don't have SQL Server environment at this moment, can not verify if everything is correct.

    WITH cte AS (
      SELECT *, rn =  ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY ID)
      FROM CustomerHistory
    )
    SELECT 
       c1.*,
       FieldChangeCount = 
          (CASE WHEN c1.ServiceLevel <> c2.ServiceLevel THEN 1 ELSE 0 END) 
            +
          (CASE WHEN c1.Status <> c2.Status THEN 1 ELSE 0 END)      
     FROM cte c1
     LEFT JOIN cte c2
          ON c1.CustomerID = c2. CustomerID
           AND c2.rn = c1.rn - 1 
    ORDER BY c1.LogID

edited Aug 04 '16 at 13:48

answered Aug 03 '16 at 18:16

EricZ

6,065
1
30
30

This worked almost perfectly, thank you. I needed to change the ORDER BY GROUP BY to PARTITION BY ORDER BY but ... really this helped. THANK YOU. – Wendi Aug 04 '16 at 04:20
@Wendi, I updated the answer with the correct syntax, glad that help. – EricZ Aug 04 '16 at 13:50

TSQL query history table field change count

1 Answers1