Using different values within a DATEADD statement

Question

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

Answer 1

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

Using different values within a DATEADD statement

Follow this question

Related Questions