x

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
asksqlquestion.jpg (44.2 kB)
more ▼

asked Feb 18, 2012 at 07:29 AM in Default

cdurham gravatar image

cdurham
230 22 22 24

@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?
Feb 20, 2012 at 02:43 PM cdurham
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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 :)
more ▼

answered Feb 20, 2012 at 03:06 PM

robbin gravatar image

robbin
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Feb 19, 2012 at 12:53 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

+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.
Feb 19, 2012 at 01:39 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 19, 2012 at 02:35 AM

Shawn_Melton gravatar image

Shawn_Melton
5.3k 19 21 29

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x28
x21

asked: Feb 18, 2012 at 07:29 AM

Seen: 1085 times

Last Updated: Feb 20, 2012 at 03:06 PM