Update Question - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Question

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 12:30:48

I have the following table:

I want to update the Accountfeely in dec2006 to reflect the november 2006 #, not sure how to go about writing the update statement, there are 20K records that I need to do this to, and need to match the accounts numbers.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 13:05:14

Here is how to get data summed per month.
This can get you a hint of how to start.

select dateadd(month, datediff(month, 0, datetimecolumnhere), 0), sum(someothercolumnhere)
from yourtablenamehere
group by dateadd(month, datediff(month, 0, datetimecolumnhere), 0)

You can easily add an extra month with this

select dateadd(month, 1 + datediff(month, 0, datetimecolumnhere), 0), sum(someothercolumnhere)
from yourtablenamehere
group by dateadd(month, 1 + datediff(month, 0, datetimecolumnhere), 0)

The above code will sum per month but report on next month.

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 14:27:16

quote:
Originally posted by Peso

Here is how to get data summed per month.
This can get you a hint of how to start.

select dateadd(month, datediff(month, 0, datetimecolumnhere), 0), sum(someothercolumnhere)
from yourtablenamehere
group by dateadd(month, datediff(month, 0, datetimecolumnhere), 0)

You can easily add an extra month with this

select dateadd(month, 1 + datediff(month, 0, datetimecolumnhere), 0), sum(someothercolumnhere)
from yourtablenamehere
group by dateadd(month, 1 + datediff(month, 0, datetimecolumnhere), 0)

The above code will sum per month but report on next month.

Peter Larsson
Helsingborg, Sweden

Maybe I wasn't clear, I don't want to do any math, I just want to update the December AccountFeeLy to reflect the same amount thats shown for November.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 14:30:58

Do you have some kind of ID value for the months?

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 14:37:27

december would be 12/1/2006, november would be 11/1/2006, that what your asking?

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 17:18:49

any ideas????

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2007-01-22 : 20:54:55

THIS POST HAS BEEN ANSWERED:

UPDATE TableName
SET AccountFeeLy = b.AccountFeeLy
FROM TableName a
JOIN (
SELECT Account, AccountFeeLy
FROM TableName
WHERE month = '11-1-2006'
) b
On a.Account = b.Account
where a.Month = '12-1-2006'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 20:58:07

quote:
THIS POST HAS BEEN ANSWERED:

over here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77921

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-23 : 01:21:17

Oh, so you only wanted to UPDATE december with the value from november?
I interpreted the original question such as you also wanted the november record to be updated with the value from october and so on...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources