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.
| Author |
Topic |
|
KidSQL
Yak Posting Veteran
88 Posts |
 Posted - 2005-05-05 : 10:58:32
|
| I have a while loop that does a bunch of operations on data in a table (full of restrictive conditions, etc.). The objective of the loop is to generate a calculation for each record in the table. What I would like to learn how to do is how I would go about inserting the value this loop generates into a new column for the table at each successive record.So, let's say I have a table that looks like thisDate ID ValueJan-03 ABC 500Feb-03 ABC 600Mar-03 ABC 450Jan-03 DEF 220Feb-03 DEF 260 ..etc.for which I have a while loop which iterates through each Date for each ID and uses the Values in this table to return yet another value per record in my table. How would I go about getting a table like this:Date ID Value Value2Jan-03 ABC 500 100Feb-03 ABC 600 120Mar-03 ABC 450 80Jan-03 DEF 220 100Feb-03 DEF 260 140..etc.where each of the values my while loop returns (each Value2 record) is inserted into the Value2 column as my while loop iterates through all the rows in my table.Thanks in advance to anyone willing to offer any advice. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-05-05 : 13:44:39
|
| Tsup Kid!It kinda depends on what your calc logic is. If all the factors are included in the row itself, I don't see why you can't use a regular update statement (no loop)Be One with the OptimizerTG |
 |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-05-05 : 13:46:11
|
1st why must you use a loop?2nd please give us your table and procidure. JimUsers <> Logic |
|
|
|
KidSQL
Yak Posting Veteran
88 Posts |
Posted - 2005-05-05 : 14:19:51
|
| Hello, TG. I had a feeling I was going to run in to you again!Well, I was getting ready to write a reply here, but I've gone back to the drawing board and I see what you mean. A regular update/no loop does seem to do the trick. It seems there was something that was quite working for me, however. I'll write back if I still can't do it all.Thanks for the advice, though. |
|
|
|
KidSQL
Yak Posting Veteran
88 Posts |
Posted - 2005-05-05 : 15:39:53
|
| Okay, so here's what I've got going on.I can either modify my existing table by adding a column and updating it to equal a calculation or by selecting everything into a new table, etc.The sticky part is that I need to perform a lot of calculations on this data and rather than creating many temporary tables or adding a zillion columns, I'd like to just be able to update the data in the one column of this table. Here's what I'm doing - I've got:Date ID ValueJan-03 ABC 500Feb-03 ABC 600Mar-03 ABC 450Jan-03 DEF 220Feb-03 DEF 260 ..etc.What I'm trying to do is1). Calculate the ratio of the numbers in the value column (so 600/500 for Feb/Jan for ABC would be my first record, 450/600 would be my second, etc.). This is an essential part of what I'm doing and it's something I can accomplish by just adding a whole new column and doing a self join, and using a regular update. This would give me my Value2 column:Date ID Value Value2Jan-03 ABC 500 nullFeb-03 ABC 600 1.20Mar-03 ABC 450 0.75Jan-03 DEF 220 nullFeb-03 DEF 260 1.18..etc.2). Next, I'd like the flexibility to multiply through the ratios I've created in this column. This means calculating the product of (600/500) * (450/600) representing (Feb/Jan) * (Mar/Feb), etc. The number of times I'm going to do this, however, will vary. Sometimes I might want to multiply (Feb/Jan) all the way through (Dec/Nov) and sometimes only a few months ahead, sometimes 36 months, and so on. Since my needs vary, I need to specify each time I run the loop for how many months I need to run it, which month to start with, etc.To do this, I was able to put together a primitive while loop which multiplies through these ratios given a specified ID and for each Date for which there is a record for the ID, but I don't know where to go from here. I'd like to be able to either have a column at the end that fills up with these values as they're calculated (I'm dreaming, I'm sure) or maybe add a completely new column and have all these values updated that way. I'm envisioning something like the column Value3 which would change as my loop runs through the table.Date ID Value Value2 Value3Jan-03 ABC 500 null nullFeb-03 ABC 600 1.20 1.20Mar-03 ABC 450 0.75 0.90Jan-03 DEF 220 null nullFeb-03 DEF 260 1.18 1.18..etc.I probably need to just blow out my whole line of thinking as I'm sure I've gone off the SQL path, but if there's any advice anyone could give, I'd really appreciate it. |
|
|
|
KidSQL
Yak Posting Veteran
88 Posts |
Posted - 2005-05-05 : 16:13:42
|
| By the way, I know this has to sound very confusing, but I think I've got all the hard stuff done. Lets just assume I wanna do something that's a little more involved than, say, adding 100 to the values in this table (in which case: update mytable set newcol = Value2+100) and that I need a loop to get that number '100', a number which will change for every record...so the question is, how do I then reflect the new value in the rightmost column (as it would appear I couldn't simply use something like set newcol = Value2 + SomeNumber given that SomeNumber will be different for every ID, etc.) |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-05-06 : 03:21:53
|
| These all sound like very solvable issues. But it's difficult to know how when the business rules presented are so nebulous. If you can define how "SomeNumber" is defined for each row, we could potentially be more forthcoming with assistance.HTH=================================================================Some mistakes are too much fun to only make once. |
|
|
|
|
|
|
|
|
Trying to update records using a while loop