x

Using different values within a DATEADD statement

Is it possible to use DATEADD with the values from a table to add different numbers of months to the current date?

I am trying to write an update statement, which sets one column equal to another for a number of months, where the number of months to be updated for each record is held in a different table

what I have is...

declare @CurrFcstPeriod varchar(6)
declare @EndFcstPeriod varchar(6)
declare @periods table --this data is in table customer_forecast.periods

Set @CurrFcstPeriod = left(convert(varchar(8),getdate(),112),6)
Set @EndFcstPeriod = LEFT(convert(varchar(8),dateadd(month, @periods, GETDATE()),112),6)

update FORECAST_TABLE
set forecast = customer_forecast         
where FCST_YR_PRD > @CurrFcstPeriod   --date format is YYYYMM
and FCST_YR_PRD < @EndFcstPeriod

Sample data:

FORECAST TABLE ITEM CUSTOMER FCST_YR_PRD FORECAST CUSTOMER_FORECAST 1 ABC 201001 100 200 1 ABC 201002 100 100
1 ABC 201003 150 200 1 ABC 201004 150 400 1 XYZ 201001 10 200 1 XYZ 201002 100 50 1 XYZ 201003 50 25

CUSTOMER_FORECAST CUSTOMER PERIODS ABC 3 XYZ 2
more ▼

asked Apr 13, 2010 at 01:15 PM in Default

Andy 1 gravatar image

Andy 1
23 1 1 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Yes.

declare @values table (id int, value int)
insert into @values
select 1,2 union
select 2,4 union
select 3,10

select 
    *,
    dateadd(mm, value, getdate())
from @values

gives

id          value       
----------- ----------- -----------------------
1           2           2010-06-13 17:15:12.817
2           4           2010-08-13 17:15:12.817
3           10          2011-02-13 17:15:12.817

(3 row(s) affected)

where the current date and time was 13 April 2010 17:15:12.817


update: now we have some sample data

Ignoring the fact that non of the sample data actually falls within the given where clause, see what happens when we join the two tables and calculate some start and end 'periods'

declare @forecast table 
     (item int, customer varchar(10), fcst_yr_prd varchar(10), 
     forecast int, customer_forecast int)

insert into @forecast
select 1,'ABC','201001',100,200
union select 1,'ABC','201002',100,100    
union select 1,'ABC','201003',150,200
union select 1,'ABC','201004',150,400
union select 1,'XYZ','201001', 10,200
union select 1,'XYZ','201002',100, 50
union select 1,'XYZ','201003', 50, 25

declare @customer_forecast table(customer varchar(10), periods int)
insert into @customer_forecast
select 'ABC',3
union select 'XYZ',2

select 
    *, 
    left(convert(varchar(8),getdate(),112),6), 
    LEFT(convert(varchar(8),dateadd(month, periods, GETDATE()),112),6)
from @forecast f
join @customer_forecast cf on f.customer= cf.customer

gives us

item        customer   fcst_yr_prd forecast    customer_forecast customer   periods            
----------- ---------- ----------- ----------- ----------------- ---------- ----------- ------ ------
1           ABC        201001      100         200               ABC        3           201004 201007
1           ABC        201002      100         100               ABC        3           201004 201007
1           ABC        201003      150         200               ABC        3           201004 201007
1           ABC        201004      150         400               ABC        3           201004 201007
1           XYZ        201001      10          200               XYZ        2           201004 201006
1           XYZ        201002      100         50                XYZ        2           201004 201006
1           XYZ        201003      50          25                XYZ        2           201004 201006

and if we add a slightly modified where clause

select 
    *, 
    left(convert(varchar(8),getdate(),112),6), 
    LEFT(convert(varchar(8),dateadd(month, periods, GETDATE()),112),6)
from @forecast f
join @customer_forecast cf on f.customer= cf.customer
where FCST_YR_PRD >= left(convert(varchar(8),getdate(),112),6)
and FCST_YR_PRD 
       < LEFT(convert(varchar(8),dateadd(month, periods, GETDATE()),112),6)

we get one row :

item        customer   fcst_yr_prd forecast    customer_forecast customer   periods            
----------- ---------- ----------- ----------- ----------------- ---------- ----------- ------ ------
1           ABC        201004      150         400               ABC        3           201004 201007

now we can turn this into an update:

update f
set forecast = customer_forecast 
from @forecast f
join @customer_forecast cf on f.customer= cf.customer
where FCST_YR_PRD >= left(convert(varchar(8),getdate(),112),6)
and FCST_YR_PRD 
      < LEFT(convert(varchar(8),dateadd(month, periods, GETDATE()),112),6)

select * from @forecast

so you can see there is no need to set the variables outside the update, in fact this is where your syntax error was

more ▼

answered Apr 13, 2010 at 01:19 PM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

That looks great but I am still struggling to apply it in what I am trying to do (getting a "you must declare scalar variable" message).
Apr 13, 2010 at 03:32 PM Andy 1

This is what I have

declare @periods CUST_FCST (ship_to int, sold_to int, periods int) Set @CurrFcstPeriod = left(convert(varchar(8),getdate(),112),6) Set @EndFcstPeriod = LEFT(convert(varchar(8),dateadd(month, @values, GETDATE()),112),6) select * from SCP_FCST_TIME_SERIES t inner join SCP_FCST_ROOT r on t.SCP_SEQ_NBR = r.SCP_SEQ_NBR inner join CUST_FCST c on r.fcst_2_id = c.Sold_To and r.fcst_3_id = c.Ship_To

update SCP_FCST_TIME_SERIES set FCST_RSLT_QTY = ads1_fcst_rslt_qty, MGMT_CODE = 'R' where FCST_YR_PRD > @CurrFcstPeriod and FCST_YR_PRD < @EndFcstPeriod
Apr 13, 2010 at 03:33 PM Andy 1
could you re-paste that into your original question -it doesn't make much sense, and maybe some sample data? That error message means simply that you haven't declared your variables, and I can see straight away that @values appears from nowhere...
Apr 13, 2010 at 04:15 PM Kev Riley ♦♦
you need to supply a data type for @periods
Apr 13, 2010 at 08:54 PM Scot Hauder
Awesome, thanks so much for your help with this!
Apr 14, 2010 at 10:49 AM Andy 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x132
x43

asked: Apr 13, 2010 at 01:15 PM

Seen: 1546 times

Last Updated: Apr 13, 2010 at 05:37 PM