Is it possible to roll back a date when a field ID changes?
![alt text] 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. :
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]. It might be a little more advanced then just writing a small trigger, I have not worked much with it yet. :
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.