how do you link fields? - 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 2005 Forums

Transact-SQL (2005)

how do you link fields?

Author

Topic

cabo68
Starting Member

4 Posts

Posted - 2008-08-05 : 14:38:00

Hello,
I'm new to the board so forgive me if I'm not following the correct protocol. I'm trying to link a variable to a different field in a fetch statement.

while @@FETCH_STATUS = 0
begin
set @sMM = Month(@Period) + Year(@Period) - @YY
if @sMM = 1
update dbo.tblActPerClient
set [01Act] = [01Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId

I need the numbers in green to be what ever is entered in the field with the red number. Is there a way to do this? Thanks for any help you can give me.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-05 : 14:53:08

yes using a dynamic query, but there does not seem any reason for this loop. Can you please post your entire query, and briefly explain your thought process. Someone will more than likly be able to assist you with a more efficient solution.

cabo68
Starting Member

4 Posts

Posted - 2008-08-05 : 15:22:24

It gets rather lengthy

while @@FETCH_STATUS = 0
begin
set @sMM = Month(@Period) + Year(@Period) - @YY
if @sMM = 1
update dbo.tblActPerClient
set [01Act] = [01Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
if @sMM = 2
update dbo.tblActPerClient
set [02Act] = [02Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId

It continues on through 18 then ends in:

if @sMM = 18
update dbo.tblActPerClient
set [18Act] = [18Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
end
update dbo.tblActPerClient
Set Q1Act = [01Act] + [02Act] + [03Act],
Q2Act = [04Act] + [05Act] + [06Act],
Q3Act = [07Act] + [08Act] + [09Act],
Q4Act = [10Act] + [11Act] + [12Act]
close actuals_cursor
deallocate actuals_cursor

This wasn't my thought process, this was a project handed to me to help clean up. I'm just trying to simplify it a little. I thought it would be easier to to link the variable rather than define every possibility. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-08-05 : 17:30:26

If you want to improve this process, rather try to eliminate the IF statements you may want to look at eliminating the CURSOR. If you want help, post the query that defines the cursor.

I wouldn't recommend the exchanging the IF statements with a dynamic statement. It may be less code visualy but not necessarily an improvement.

Be One with the Optimizer
TG

cabo68
Starting Member

4 Posts

Posted - 2008-08-06 : 09:49:27

Is this what you are referring too?

declare actuals_cursor cursor
for
select [Client Number],
RevenueID,
[Period],
[Amount]
from dbo._Actuals_Feed
where year(Period) = @YY
open actuals_cursor
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
while @@FETCH_STATUS = 0
begin
set @sMM = Month(@Period) + Year(@Period) - @YY
if @sMM = 1
update dbo.tblActPerClient
set [01Act] = [01Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
if @sMM = 18
update dbo.tblActPerClient
set [18Act] = [18Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
end
update dbo.tblActPerClient
Set Q1Act = [01Act] + [02Act] + [03Act],
Q2Act = [04Act] + [05Act] + [06Act],
Q3Act = [07Act] + [08Act] + [09Act],
Q4Act = [10Act] + [11Act] + [12Act]
close actuals_cursor
deallocate actuals_cursor

I apologize for my ignorance, I'm new to SQL and still trying to figure things out. I appreciate everyones help very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-06 : 13:19:38

quote:
Originally posted by cabo68

Is this what you are referring too?

declare actuals_cursor cursor
for
select [Client Number],
RevenueID,
[Period],
[Amount]
from dbo._Actuals_Feed
where year(Period) = @YY
open actuals_cursor
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
while @@FETCH_STATUS = 0
begin
set @sMM = Month(@Period) + Year(@Period) - @YY
if @sMM = 1
update dbo.tblActPerClient
set [01Act] = [01Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
if @sMM = 18
update dbo.tblActPerClient
set [18Act] = [18Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
end
update dbo.tblActPerClient
Set Q1Act = [01Act] + [02Act] + [03Act],
Q2Act = [04Act] + [05Act] + [06Act],
Q3Act = [07Act] + [08Act] + [09Act],
Q4Act = [10Act] + [11Act] + [12Act]
close actuals_cursor
deallocate actuals_cursor

I apologize for my ignorance, I'm new to SQL and still trying to figure things out. I appreciate everyones help very much.

what does @sMM designate? you are adding month & year and subtracting @YY. what would be its value?

cabo68
Starting Member

4 Posts

Posted - 2008-08-06 : 15:13:10

Good eye, I didn't see that. Turns out there is supposed to be a "less than" value in there.

declare actuals_cursor cursor
for
select [Client Number],
RevenueID,
[Period],
[Amount]
from dbo._Actuals_Feed
where year(Period) >= @YY
open actuals_cursor
fetch next from actuals_cursor into
@Client_Number, @RevenueID, @Period, @Amount
while @@FETCH_STATUS = 0
begin
set @sMM = Month(@Period) + Year(@Period) - @YY
if @sMM = 1
update dbo.tblActPerClient
set [01Act] = [01Act] + @Amount
where [Client Number] = @Client_Number and RevenueId = @RevenueId

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources