Stored Procedure, Select/Compute/Update

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure, Select/Compute/Update

Author

Topic

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-20 : 13:14:04

I am trying to update fields in my table based on certain critera.

UPDATE tblALMLoans

SET tblALMLoans.NextRepDate = dateadd(YY,5,tblALMLoans.OriginalDate)

do until tblALMLoans.NextRepDate > getdate()
tblALMLoans.NextRepDate = dateadd(YY,1,tblALMLoans.OriginalDate)
loop

FROM tblALMLoans
WHERE (tblALMLoans.RateFlag = 'A');

I know this is no where near what its supposed to look like, but this is in code what I am looking to do.

Any help would be greatly appriciated.

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-20 : 13:16:15

Not sure I understand the requirements you have from the code above. Could you state the update you are trying to do in English?

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-20 : 13:24:58

I have a 2 fields in my table, tblALMLoans, called NextRepDate and OrginalDate.
If tblALMLoans.RateFlag = 'A' then I need to add 5 years to OrginalDate and save it in the field NextRepDate.
After that is done, if NextRepDate < Todays date then I have to keep adding 1 year to NextRepDate until it is > then todays date.
Then save it back into the table.

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-20 : 13:26:46

In VBA/ACCESS it looks something like this...

qrySelectALMAdjustable
SELECT tblALMLoans.Account_Number, tblALMLoans.Orig_Date, tblALMLoans.Rate_Flag, tblALMLoans.Loan_Type
FROM tblALMLoans
WHERE (((tblALMLoans.Rate_Flag)="A"));

VBA
Public Function Adjustment_Date()

Dim VarCurrent As Date, VarNextRep As Date, VarOpenDate As Date
Dim RS, RS2 As Recordset
Dim DB As Database
Dim strsql As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("qrySelectALMAdjustable")
VarCurrent = Date

Do Until RS.EOF
VarOpenDate = RS!Orig_Date
VarNextRep = DateAdd("yyyy", 5, VarOpenDate)
Do Until VarNextRep > VarCurrent
VarNextRep = DateAdd("yyyy", 1, VarNextRep)
Loop
strsql = "UPDATE tblALMLoans SET Next_Rep_Date = '" & VarNextRep & "' WHERE Account_Number = '" & RS!Account_Number & "';"
DoCmd.RunSQL strsql
RS.MoveNext
Loop

End Function

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-20 : 13:43:25

quote:
Originally posted by tmaiden

I have a 2 fields in my table, tblALMLoans, called NextRepDate and OrginalDate.
If tblALMLoans.RateFlag = 'A' then I need to add 5 years to OrginalDate and save it in the field NextRepDate.
After that is done, if NextRepDate < Todays date then I have to keep adding 1 year to NextRepDate until it is > then todays date.
Then save it back into the table.

Now we're talking... but I'm not clear if the RateFlag = 'A' on the 2nd update...


UPDATE tblALMLoans
  SET NextRepDate = DATEADD(yy, 5, OriginalDate)
  WHERE RateFlag = 'A'

UPDATE tblALMLoans
  SET NextRepDate = DATEADD(yy, DATEDIFF(yy, NextRepDate, GETDATE())+1 , NextRepDate) 
  WHERE NextRepDate < GETDATE()

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-20 : 13:52:51

This is what I got so far... Please elaberate if possible

DECLARE @NextRepDate smalldatetime, @OriginalDate smalldatetime, @Member int

while 1=1
BEGIN
SELECT TOP 1 @Member=Member, @OriginalDate=OriginalDate, tblALMLoans.RateFlag
FROM tblALMLoans
WHERE (((tblALMLoans.Rate_Flag)='A'))
SET @NextRepDate = dateadd(YY,5,@OriginalDate)
IF @@rowcount=0 break
WHILE @NextRepDate < GetDate()
SET @NextRepDate = dateadd(YY,5,@NextRepDate)
update tblALMLoans set NextRepDate=@NextRepDate where Member=@Member

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-20 : 18:20:05

quote:
Originally posted by tmaiden

This is what I got so far... Please elaberate if possible

Why do you prefer while loops over the non-looping solution I posted?

tmaiden
Yak Posting Veteran

86 Posts

Posted - 2005-10-21 : 15:12:00

Wasn't sure if they were the same, thanks for your help. Would also though like to know how to do it with a loop as well, for reference.

SamC
White Water Yakist

3467 Posts

Posted - 2005-10-22 : 10:07:49

These solutions are the same. Set-based solutions generally outperform looping and cursor-based solutions by an order of magnitude, so it might be worth taking a closer look.

quote:
Originally posted by tmaiden

I have a 2 fields in my table, tblALMLoans, called NextRepDate and OrginalDate.
If tblALMLoans.RateFlag = 'A' then I need to add 5 years to OrginalDate and save it in the field NextRepDate.

This part is pretty easily done with the first update...


UPDATE tblALMLoans
  -- Add 5 years to OriginalDate and save it in the field NextRepDate
  SET NextRepDate = DATEADD(yy, 5, OriginalDate) 
  WHERE RateFlag = 'A'

quote:
Originally posted by tmaiden

After that is done, if NextRepDate < Todays date then I have to keep adding 1 year to NextRepDate until it is > then todays date. Then save it back into the table.

This isn't as tough as it sounds. If the number of years that NextRepDate is less than GETDATE can be calculated, then it can be added to NextRepDate without a loop...

DATEDIFF(yy, NextRepDate, GETDATE()) -- Number of years that NextRepDate is less than GETDATE()

Once the number of years is known, it's not tough to add it in...

UPDATE tblALMLoans
  SET NextRepDate = DATEADD(yy, DATEDIFF(yy, NextRepDate, GETDATE())+1 , NextRepDate) -- 
  WHERE NextRepDate < GETDATE()

In the solution you posted above, you're adding 5 years (not 1 year) in the loop until the date is greater than GETDATE(). This can be done if the 5 year incriment (0, 5, 10, ...) is known...

If this is the requirement, then you don't need two UPDATE statements. The UPDATE below is the only SQL needed to solve this problem.

(DATEDIFF(yy, NextRepDate, GETDATE()) / 5 + 1) * 5 -- Results in 0, 5, 10, 15, etc...

UPDATE tblALMLoans
  SET NextRepDate = DATEADD(yy, (DATEDIFF(yy, NextRepDate, GETDATE()) / 5 + 1) * 5 , NextRepDate) -- 
  WHERE NextRepDate < GETDATE()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources