asked
Is it possible to roll back a date when a field ID changes?
![alt text][1] Greetings all, I am needing to know is it possible in SQL Server 2008 R2 to make a date change as a particular row id changes? I have to recreate a sales import process. I always grab the max id for the previous date (getdate() - 1). I am currently doing this in FileMaker Pro Advanced 11.2, but my company is migrating from FileMaker Pro Advanced 11.2 to SQL Server 2008 R2. I am currently using SQL Server R2 to recreate the sales import process, but I just need to know how to write a code that would change the date field when the row id changes. I have attached a file (i wrote a result set using excel just as an example) to show you all what I am trying to accomplish. On Mondays, I always run the import for the previous Friday, Saturday, and Sunday. This is why I need to know when there is a change in ID, there will be a change in date (going back) I am pretty sure that I would have to use something like getdate() -2, getdate() - 3, but I need to know how to apply this as the Id changes. as the ID decreases, the date will go back a previous day with each change. Your help would be greatly appreciated. [1]:
http://ask.sqlservercentral.com/storage/temp/102-asksqlquestion.jpg
@Grant Fritchey, Thanks for the answer. I have only been a DBA for a few months. I just put together some data for the attachment in excel just for demonstration purposes. First I would need to create a temp table and add a date since the company I work for gets its tables from another company. Would I need then need to write an if statement saying: if a row has a change in id, then update the date by decreasing the date by 1 day (getdate() -1), else if the date decreases by two, then update the date by decreasing the date by 2 days (getdate() - 2), and so forth. I do not really know how to write the code for it. Would this example be correct?
If I understand correctly, the OP is trying to import the data for last days from some other source (may not be updating any existing data) and getting bogged down by the scenario to have some sort of a derived column. I may be completely wrong but if it is the need, then @Shawn 's suggestion are good. Another way around may be to run such kind of a script SELECT id, DATEADD(dd,DENSE_RANK() OVER (ORDER BY [Id])* - 1, GETDATE()) FROM ( SELECT 1 AS Id UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ) A BTW, since it is an import process, SSIS solution comes to mind which also provide Derived column transformation :)
I would say a trigger would be something that might work to see when changes are made to the particular table. However since you are going to SQL Server 2008 R2 you might look at [Change Data Capture][1]. It might be a little more advanced then just writing a small trigger, I have not worked much with it yet. [1]:
http://msdn.microsoft.com/en-us/library/bb522489.aspx
Although, can't it just be a part of the process? If you update the value for SalesId also update the value for Date? I try to keep things as simple as I can during these processes. PS: Date is a reserve word in SQL Server. You'll have to put brackets around it, [Date] to use it. I'd suggest changing the column name to SalesDate or something like that to avoid the hassle.
+1 for simple as possible. I've been to a lot of IT shops and see solutions over-engineered everywhere. This is especially prevalent in ETL processes where there are way too many moving parts that only widen the problem space.
5 People are following this question.
