Do While to add 3mo to a date till > another date

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

Do While to add 3mo to a date till > another date

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-26 : 11:51:02

I have a stored procedure which I want to add 3 months to a date field (fi_start_date) until it is greater then another date field (asof_dt). Once it is greater, I want to update and store the value into a field called w_calc_repric3M0. I tried the logic below but keep getting an error noting "Invalid column name 'fi_start_date'. The field is definitely in the database. Thoughts? Any help is greatly appreciated. Thanks!
p.s. I using sql 2008

while (DATEADD(mm,3,fi_start_date)) > asof_dt
begin

update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = DATEADD(m,3,fi_start_date)
end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-26 : 12:11:44

Which tables do fi_start_date and asof_dt
come from or should they be vriables?

while @fi_start_date < @asof_dt
select @fi_start_date = dateadd(mm,3,@fi_start_date)

update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = @fi_start_date

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-26 : 12:18:03

The fi_start_date , asof_dt , and w_calc_repric3MO fields come from the same table sbc_bdi_db.dbo.bdi_bolt.

I implemented your recommendation and declared a few fields but now getting error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." I think its because of my select statements within the @fi_start_date field. It seems like it only wants to read from one record. The asof_dt is always the same for all records, thus I selected the top 1. The fi_start_date can be different in each record. I am not sure how to get around this. Thoughts???

This is the new code I put in still with issue on red line below:
declare @asof_dt datetime;
declare @fi_start_date datetime;
set @asof_dt = (select top 1 asof_dt from sbc_bdi_db.dbo.bdi_bolt )
set @fi_start_date = (select fi_start_date from sbc_bdi_db.dbo.bdi_bolt )

while @fi_start_date < @asof_dt
select @fi_start_date = dateadd(mm,3,@fi_start_date)

update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = @fi_start_date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 03:05:01

sounds like what you want is this


update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = DATEADD(m,3,fi_start_date)
where DATEADD(m,3,fi_start_date)>asof_dt

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-10-27 : 09:12:03

Does each row have a different asof_dt and fi_start_date or are they all the same?
If they are different then your code won't work as it only gets the first one (see my first post for updating each row with different values).
If they are all the same then (and should they be on that table rather than another single row table?)

declare @asof_dt datetime;
declare @fi_start_date datetime;
set @asof_dt = (select top 1 asof_dt from sbc_bdi_db.dbo.bdi_bolt )
set @fi_start_date = (select top 1 fi_start_date from sbc_bdi_db.dbo.bdi_bolt)

while @fi_start_date < @asof_dt
select @fi_start_date = dateadd(mm,3,@fi_start_date)

update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = @fi_start_date

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-27 : 16:50:45

Each row has the same asof_date but a different fi_start_date. Can this maybe be written in a function? I thinking maybe I can pass it the asof_dt and fi_start_date fields as parameters and the function can then perform the
while @fi_start_date < @asof_dt
set @fi_start_date = dateadd(mm,3,@fi_start_date)

I am just not sure how to do this in a function since I have never created one before. I spend 4 hours researching on google, but couldn't get anything to work. I am a bit frustrated. Any help would be appreciated. Thx.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 05:03:07

quote:
Originally posted by cirugio

Each row has the same asof_date but a different fi_start_date. Can this maybe be written in a function? I thinking maybe I can pass it the asof_dt and fi_start_date fields as parameters and the function can then perform the
while @fi_start_date < @asof_dt
set @fi_start_date = dateadd(mm,3,@fi_start_date)

I am just not sure how to do this in a function since I have never created one before. I spend 4 hours researching on google, but couldn't get anything to work. I am a bit frustrated. Any help would be appreciated. Thx.

I'm not still fully getting you. seems like what you want is set based solution as i gave. did you try that? any problems with it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-28 : 10:22:19

HiVisakh16.

I tried your recommendation. It updated the the w_calc_repric3mo field correctly for all records where the fi_start_date was after June 2011, but for other records with a prior fi_start_date it provided incorrect results. See below for a example of some of the records with the incorrect results in the w_calc_pric3mo field and what I would expect the result to be:


fi_start_date 	asof_dt	    w_calc_repric3mo   expected result
-------------   ---------   ----------------   ---------------
6/8/2011	9/30/2011	12/19/2011	12/8/2011
6/13/2011	9/30/2011	12/19/2011	12/13/2011
6/28/2011	9/30/2011	12/19/2011	12/28/2011

2/9/2011	9/30/2011	12/19/2011	11/10/2011

I really don't understand why this would occur. Thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-28 : 10:59:19

ok. this was not specified as part of original requirement. see my update it deals only with cases DATEADD(m,3,fi_start_date)>asof_dt whereas in all above cases its not the same. if you want to deal with them also modify update like below



update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO = CASE WHEN DATEADD(m,3,fi_start_date)>asof_dt THEN DATEADD(m,3,fi_start_date)
                            ELSE DATEADD(m,6,fi_start_date)
                       END

i dont know rules to be used for updating w_calc_repric3mo for cases where DATEADD(m,3,fi_start_date)<asof_dt so i'm assuming its month + 6 seeing sample data (its not matching for last one though which i feel might be a typo). If assumption is not correct, please let us know whats the rule for updating those values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-28 : 13:48:25

My apologies, but I thought I actually did mention it? Just to recap, all I want to do is ALWAYS add 3 months to the fi_start_date UNTIL it is greater than the asof_dt then store the value when it is greater into the w_calc_repric3MO field.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 00:47:58

quote:
Originally posted by cirugio

My apologies, but I thought I actually did mention it? Just to recap, all I want to do is ALWAYS add 3 months to the fi_start_date UNTIL it is greater than the asof_dt then store the value when it is greater into the w_calc_repric3MO field.

in case its not > asof_dt where will we store result to?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-29 : 08:04:44

we should always add 3months to the fi_start_date till it is greater then asof_dt. The fi_start_date is always less then the asof_dt field. There will never be a situation where asof_dt is less then the fi_start_date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-29 : 10:01:28

i think this is what you want

update sbc_bdi_db.dbo.bdi_bolt
set w_calc_repric3MO =DATEADD(mm,CIELING((DATEDIFF(mm,fi_start_date,asof_dt) + CASE WHEN  DAY(DATEADD(m,3,fi_start_date))< DAY(asof_dt) THEN 1 ELSE 0 END )/3.0)* 3,fi_start_date)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2011-10-31 : 10:15:14

Your a Genius. It works perfectly. Thank you!!!!!!!!!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-31 : 11:17:57

wc
make sure you specify reqmnts clearly first time itself to avoid confusions