A client recently asked me to start uploading a text file into a SQL Server database each day. This file is provided by a third party, and reflects current values, not changes. I've got this upload process working as a simple append query, but it is resulting in a bunch of duplicate values (records that are identical except for the upload_date field).
The SQL table contains fields like:
Upload_Date, Company_ID, Column1, Column2, Column3
I'm seriously thinking about modifying the upload, so that it is not a simple append, but a merge which would use all of the columns to perform the merge and if the record is matched, (everything but the upload_date), then I would simply update a counter field, but if the record is unmatched, I would perform the append, but I need to discuss this with the client first.
In the meantime, what I need to do is create a query which will identify the dates a companies data (Column1, Column2, or Column3) changed. Assuming these 5 columns, with data similar to
UPload_Date Company_ID Column1 Column2 Column3
2018-01-01 1 a b c
2018-01-02 1 a b c
2018-01-03 1 a b d
2018-01-04 1 a b d
2018-01-05 1 z b d
I would like a recodset which looks like:
Company_ID Upload_Date PrevChange
1 2018-01-05 2018-01-03
1 2018-01-03 2018-01-01
1 2018-01-01 NULL