How to track the changes in data over months

Question

1.00/5 (1 vote)

See more:

I have a scenario where I'm trying to identify the changes made in fields over the months. If we identify the change, only the row with the latest data should be displayed along with a Changeflag for that column.
In other words, identifying what field values were updated.

CREATE TABLE [dbo].[TestChange](
	[ID] [int] NULL,
	[InTime] [date] NULL,
	[dept] [varchar](100) NULL,
	[Manager] [varchar](100) NULL,
	[Budget] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (101, CAST(N'2021-08-29' AS Date), N'Dept1', N'Manager1', 100000)
GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (101, CAST(N'2021-09-29' AS Date), N'Dept1', N'Manager2', 200000)
GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (102, CAST(N'2021-08-29' AS Date), N'Dept2', N'Manager1', 105000)
GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (102, CAST(N'2021-09-29' AS Date), N'Dept2', N'Manager2', 220000)
GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (103, CAST(N'2021-08-29' AS Date), N'Dept3', N'Manager1', 100000)
GO
INSERT [dbo].[TestChange] ([ID], [InTime], [dept], [Manager], [Budget]) VALUES (103, CAST(N'2021-09-29' AS Date), N'Dept3', N'Manager1', 150000)
GO

What I have tried:

with Ctetest as(
select t1.ID, t1.InTime,t1.dept,t1.Manager,t1.Budget, ROW_NUMBER() over(partition by t1.id order by t1.intime desc) as rownum 
from TestChange t1
inner join TestChange t2
on t1.ID=t2.ID
where t1.Manager<>t2.Manager or t1.Budget<>t1.Budget
)
select * from ctetest where rownum=1

This gets the latest record, but doesn't say which record was updated from the previous month and what would be the right approach if there would be like 100 column which needs to be compared.

Posted 29-Sep-21 4:14am

PreetMDX

Updated 2-Oct-21 20:57pm

Add a Solution

Comments

[no name] 30-Sep-21 2:14am

You write what changed to a log at the time of the change. A log that you can query later.

Richard Deeming 4-Oct-21 5:47am

Depending on which version of SQL Server you're using, maybe temporal tables might help?
Temporal Tables - SQL Server | Microsoft Docs[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

User 9916080 · Answer 1 · 2021-09-30T06:55:00

Solution 1

As Gerry suggested, you need to create a log to query. The way I would approach it this way:

1. Create a log table

CREATE TABLE [dbo].[TestChangeLog](<br />
	[ID] [bigint] IDENTITY(1,1) NOT NULL,<br />
    [RowID] [int] NOT NULL,<br />
	[ModifyDate] [datetime] NOT NULL,<br />
	[ColumnName] [varchar](100) NOT NULL,<br />
	[StrValue] [varchar](100) NULL,<br />
	[IntValue] [int] NULL<br />
) ON [PRIMARY]

2. Create a trigger on the TestChange table for Update passing the column name and value and insert into the log table.

You can then join the two tables TestChange and TestChangeLog together based on ID and RowID and Modify date to get the last change and what changed.

Posted 30-Sep-21 6:55am

User 9916080

Comments

PreetMDX 5-Oct-21 5:40am

Thank you Gerry and Donnathan for your inputs.

The issue here is that every time there is a new record being inserted.
It is basically for data analysis purposes and not from a transactional database.
The data gets added once a month and there is a new record every time with almost redundant data except few columns get the values updated.
I won't be able to make changes to the existing process. I'm not sure how triggers and log table would work in that case.

Therefore, I was trying to compare data and get the latest value and a way to identify which of the column was different from the previous month.

I could think of using a case statement if it is different from the previous month add the latest value else 0. but there are a lot of columns as well.

So, if I go with that approach I would require some help to loop through all the columns in case statement instead of writing all those.
or I would welcome any suggestions to achieve this another way.