Create a column which references other columns and their values?

Question

I am trying to create a time series which shows what the values of a specific Column was at a particular time. All I currently have access to is a table which logs all the changes, the current value of the columns, dates and the names of the column which was altered. I would like to create a new column which tracks what the previous value of the column was on the date before it was changed. There are over 63 columns in the change referenced in ‘Column_name’

This is what I currently have

________________________________________________
Name |  date    |A  | B  |C  |NEW | Column_name|
bob  |  12302019|2  | 23 |153|2   | a          |
bob  |  12102019|2  | 23 |153|362 | a          |
bob  |  10242019|2  | 23 |153|7   | a          | 
john |  10062017|684| 452|1  |254 | c          |
john |  11052018|684| 452|1  |1   | c          |
________________________________________________

This is what I would like help creating

_____________________________________________________
Name |  date    |A  | B  |C  |NEW | Column_name| Old |
bob  |  12302019|2  | 23 |153|2   | a          | 362 | 
bob  |  12102019|2  | 23 |153|362 | a          | 7   |
bob  |  10242019|2  | 23 |153|7   | a          |     | 
john |  10062017|684| 452|1  |254 | c          | 458 |
john |  11052018|684| 452|1  |1   | c          | 254 |
______________________________________________________

My apologies, this would come from a previous input that would not be captured because it is out of the time scope — Dominic Naimool, Dec 09 '19 at 17:11

score 0 · Answer 1 · answered Dec 09 '19 at 16:55

0

You seem to want lag():

select t.*,
       lag(new) over (partition by name order by date) as old
from t;

answered Dec 09 '19 at 16:55

Gordon Linoff

1,242,037
58
646
786

There are over 60 distinct factors under column name in the complete data-set is there a way to do this without listing them all? I attempted to use a sub query and an error was returned Select *,LAG(dmhi.value_) OVER(PARTITION BY (select distinct dmhi.field from dmhi),dmhi.field ORDER BY dmhi.effective) AS Old from cust left join dmd on dmd.rowno_custdmd_cust = cust.rowno left join dmhi on dmd.rowno=dmhi.rowno_dmddmhi_dmd where dmhi.field != ' ' Subquery returned more than 1 value. This is not permitted... – Dominic Naimool Dec 10 '19 at 14:00
@DominicNaimool . . . I'm not sure that I fully understand your problem. SQL Server doesn't have a short-cut for handling multiple columns, but it is easy enough to generate the code in a spreadsheet. There might be other approaches, if you ask a question closer to what you really want to do. – Gordon Linoff Dec 10 '19 at 14:17
I see, for me the real issue is that columns may be added to the db periodically. if this happens then code based on a static list would not capture the changes to those columns,perhaps I should consider other options. Your help is appreciated! – Dominic Naimool Dec 10 '19 at 15:24

score 0 · Answer 2 · answered Dec 10 '19 at 10:40

you've got the answer already, it is LAG():

CREATE TABLE #test
(
    UserName VARCHAR(20),
    TheDate DATE,
    A INT,
    B INT,
    C INT,
    ColumnName NVARCHAR(128),
    New INT
);

INSERT INTO #test(UserName, TheDate, A, B, C, ColumnName, New)
VALUES 
('bob', '20191230', 2, 23, 153, 'a', 2),
('bob', '20191210', 2, 23, 153, 'a', 362),
('bob', '20191024', 2, 23, 153, 'a', 7),
('john', '20171006', 684, 452, 1, 'c', 458),
('john', '20181105', 684, 452, 1, 'c', 254);

SELECT *, LAG(New) OVER(PARTITION BY A, B, C, ColumnName ORDER BY TheDate) AS Old
FROM #test
ORDER BY A, B, C, TheDate;

Create a column which references other columns and their values?

2 Answers2