Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-12-30 : 05:02:37
|
| JDC writes "OS = Win2K (SP2) MS SQL Server 2K (SP2) I need to write the SQL necessary to perform the following actions:Create a view that pulls together 22 different columns (including a unique identifier, account number) plus a LAST_UPDATE field from a database that is updated daily.Run this view each day & keep a running history table.Compare each days results (for that customer) to the previous record (if present) in the history table.If any of the 22 column values for a given record have changed, replace the existing record in the history table. If the incoming record is a new insert, place it in the history table.Each day, select those records that are either changes or new inserts.Place selected records into a text file to be FTP’d to our business user each day.Keep a log of the results.I’m inclined to put a transaction date field into the history table to indicate when the record was added. Then write a trigger to select a record when LAST_UPDATE > TRANSACTION_DATE and insert it into the history table, replacing the old record. Another select could retrieve all records that are new inserts.I’ve been coding for six months & could use the insight of someone more experienced. I’m having particular difficulty at the point where the incoming record is compared to the existing record (if present) & replacing it. Any recommendations for how to write this process would be greatly appreciated." |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-12-30 : 14:47:11
|
| If the incoming values are stored someplace, this should be straightforward.Have you tried storing the incoming values in any of:- temporary table- Local variables (22 isn't too many)Now you can take your time to determine where they go.I'll be back tomorrow. Interested in seeing how you make out on this.Sam |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2002-12-30 : 15:31:40
|
| Here's a way to archive data like this, in a general sense.If we have the following table:Data: ID, Field1, Field2, ... FieldNand we want to store a history of changes of that table in the following table:History (primary key of ID, Date): ID, Date, Field1, Field2, .... FieldNHere is a way to do the INSERT into the history table whenever you wish to archive the data table:INSERT INTO History (ID, Date, Field1, ... FieldN)SELECT ID, GetDate(), Field1, .... FieldNFROM(SELECT ID, '1/1/1900' as Date, Field1, ... FieldNFROM DataUNION ALLSELECT A.ID, A.Date, A.Field1, ... A.FieldNFROM History AINNER JOIN (SELECT ID, Max(Date) as Latest FROM History GROUP BY ID) BON A.ID = B.ID AND A.date = B.Latest) AGROUP BY ID, Field1, .... FieldNHAVING COUNT(*) = 1 ANDDate = '1/1/1900'Note: in the final GROUP BY, do NOT group by the 'Date' field.That will add a new record for any changes in the fields 1 through N, and for all new records.The above uses a UNION of the two tables and GROUP BY's to check for changes in any of the fields, and handles NULLS very well unlike JOINs.Basically, we UNION the current data with the latest record for each ID from the history table, and GROUP BY all of the fields. If we get a COUNT(*) of 1, we know the record isn't matching any others. So, we just take the one from the Data table (we fake the date of 1/1/1900) and add it to the history table, along with the current date.- JeffEdited by - jsmith8858 on 12/30/2002 15:32:41 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-01-02 : 10:24:01
|
| Nice job Jasper. Wish I had thought of it. Sam |
|
|
|
JDC
Starting Member
1 Post |
Posted - 2003-01-09 : 15:59:09
|
| Jeff & Sam - many thanks to you both. |
|
|
|
|
|
|
|
|
Net Changes Only Strategy