sql - update issue: I just want to update the past or future column to 0 or 1 depending on the date and I can't get it working, can anybody help me? -

    if object_id('dbo.dimensiondate', 'u') not null           drop table dbo.dimensiondate      /**********************************************************************************/      create table    [dbo].[dimensiondate]         (   [datekey] int primary key,              [date] datetime,             [fulldate] char(10),-- date in dd-mm-yyyy format             [dayofmonth] varchar(2), -- field hold day number of month             [daysuffix] varchar(4), -- apply suffix 1st, 2nd ,3rd etc             [dayname] varchar(9), -- contains name of day, sunday, monday              [dayofweekire] char(1),-- first day monday=1 , sunday=7             [dayofweekinmonth] varchar(2), --1st monday or 2nd monday in month             [dayofweekinyear] varchar(2),             [dayofquarter] varchar(3),             [dayofyear] varchar(3),             [weekofmonth] varchar(1),-- week number of month              [weekofquarter] varchar(2), --week number of quarter             [weekofyear] varchar(2),--week number of year             [month] varchar(2), --number of month 1 12             [monthname] varchar(9),--january, february etc             [monthofquarter] varchar(2),-- month number belongs quarter             [quarter] char(1),             [quartername] varchar(9),--first,second..             [year] char(4),-- year value of date stored in row             [yearname] char(7), --cy 2012,cy 2013             [monthyear] char(10), --jan-2013,feb-2013             [mmyyyy] char(6),             [firstdayofmonth] date,             [lastdayofmonth] date,             [firstdayofquarter] date,             [lastdayofquarter] date,             [firstdayofyear] date,             [lastdayofyear] date,             [isholidayire] bit,-- flag 1=national holiday, 0-no national holiday             [isweekday] bit,-- 0=week end ,1=week day             [holidayire] varchar(50),--name of holiday in             [isholidayuk] bit null,-- flag 1=national holiday, 0-no national holiday             [holidayuk] varchar(50) null, --name of holiday in uk             [pastorfuture] bit null -- flag  0=past, 1=future           )     go      truncate table dbo.dimensiondate      /********************************************************************************************/     --specify start date , end date here     --value of start date must less end date       declare @startdate datetime = '04/01/2012' --starting value of date range     declare @enddate datetime = '04/01/2015' --end value of date range      --temporary variables hold values during processing of each date of year     declare         @dayofweekinmonth int,         @dayofweekinyear int,         @dayofquarter int,         @weekofmonth int,         @currentyear int,         @currentmonth int,         @currentquarter int      /*table data type store day of week count month , year*/     declare @dayofweek table (dow int, monthcount int, quartercount int, yearcount int)      insert @dayofweek values (1, 0, 0, 0)     insert @dayofweek values (2, 0, 0, 0)     insert @dayofweek values (3, 0, 0, 0)     insert @dayofweek values (4, 0, 0, 0)     insert @dayofweek values (5, 0, 0, 0)     insert @dayofweek values (6, 0, 0, 0)     insert @dayofweek values (7, 0, 0, 0)      --extract , assign various parts of values current date variable      declare @currentdate datetime = @startdate     set @currentmonth = datepart(mm, @currentdate)     set @currentyear = datepart(yy, @currentdate)     set @currentquarter = datepart(qq, @currentdate)     set @startdate = getdate()      /********************************************************************************************/     --proceed if start date(current date ) less end date specified above      while @currentdate < @enddate     begin      /*begin day of week logic*/       /*check change in month of current date if month changed        change variable value*/ if @currentmonth != datepart(mm, @currentdate)  begin     update @dayofweek     set monthcount = 0     set @currentmonth = datepart(mm, @currentdate) end      /* check change in quarter of current date if quarter changed change       variable value*/  if @currentquarter != datepart(qq, @currentdate) begin     update @dayofweek     set quartercount = 0     set @currentquarter = datepart(qq, @currentdate) end      /* check change in year of current date if year changed change       variable value*/   if @currentyear != datepart(yy, @currentdate) begin     update @dayofweek     set yearcount = 0     set @currentyear = datepart(yy, @currentdate) end      -- set values in table data type created above variables   update @dayofweek set      monthcount = monthcount + 1,     quartercount = quartercount + 1,     yearcount = yearcount + 1 dow = datepart(dw, @currentdate)  select     @dayofweekinmonth = monthcount,     @dayofquarter = quartercount,     @dayofweekinyear = yearcount @dayofweek dow = datepart(dw, @currentdate) 

/end day of week logic/

/* populate dimension table values*/

insert [dbo].[dimensiondate] select      convert (char(8),@currentdate,112) datekey,     @currentdate date,      convert (char(10),@currentdate,103) fulldate,     datepart(dd, @currentdate) dayofmonth,     --apply suffix values 1st, 2nd 3rd etc..     case          when datepart(dd,@currentdate) in (11,12,13)          cast(datepart(dd,@currentdate) varchar) + 'th'         when right(datepart(dd,@currentdate),1) = 1          cast(datepart(dd,@currentdate) varchar) + 'st'         when right(datepart(dd,@currentdate),1) = 2          cast(datepart(dd,@currentdate) varchar) + 'nd'         when right(datepart(dd,@currentdate),1) = 3          cast(datepart(dd,@currentdate) varchar) + 'rd'         else cast(datepart(dd,@currentdate) varchar) + 'th'          end daysuffix,      datename(dw, @currentdate) dayname,       -- check day of week per , change per uk format           case datepart(dw, @currentdate)         when 1 7         when 2 1         when 3 2         when 4 3         when 5 4         when 6 5         when 7 6         end          dayofweekire,      @dayofweekinmonth dayofweekinmonth,     @dayofweekinyear dayofweekinyear,     @dayofquarter dayofquarter,     datepart(dy, @currentdate) dayofyear,     datepart(ww, @currentdate) + 1 - datepart(ww, convert(varchar,      datepart(mm, @currentdate)) + '/1/' + convert(varchar,      datepart(yy, @currentdate))) weekofmonth,     (datediff(dd, dateadd(qq, datediff(qq, 0, @currentdate), 0),      @currentdate) / 7) + 1 weekofquarter,     datepart(ww, @currentdate) weekofyear,     datepart(mm, @currentdate) month,     datename(mm, @currentdate) monthname,     case         when datepart(mm, @currentdate) in (1, 4, 7, 10) 1         when datepart(mm, @currentdate) in (2, 5, 8, 11) 2         when datepart(mm, @currentdate) in (3, 6, 9, 12) 3         end monthofquarter,     datepart(qq, @currentdate) quarter,     case datepart(qq, @currentdate)         when 1 'first'         when 2 'second'         when 3 'third'         when 4 'fourth'         end quartername,     datepart(year, @currentdate) year,     'cy ' + convert(varchar, datepart(year, @currentdate)) yearname,     left(datename(mm, @currentdate), 3) + '-' + convert(varchar,      datepart(yy, @currentdate)) monthyear,     right('0' + convert(varchar, datepart(mm, @currentdate)),2) +      convert(varchar, datepart(yy, @currentdate)) mmyyyy,     convert(datetime, convert(date, dateadd(dd, - (datepart(dd,      @currentdate) - 1), @currentdate))) firstdayofmonth,     convert(datetime, convert(date, dateadd(dd, - (datepart(dd,      (dateadd(mm, 1, @currentdate)))), dateadd(mm, 1,      @currentdate)))) lastdayofmonth,     dateadd(qq, datediff(qq, 0, @currentdate), 0) firstdayofquarter,     dateadd(qq, datediff(qq, -1, @currentdate), -1) lastdayofquarter,     convert(datetime, '01/01/' + convert(varchar, datepart(yy,      @currentdate))) firstdayofyear,     convert(datetime, '12/31/' + convert(varchar, datepart(yy,      @currentdate))) lastdayofyear,     null isholidayire,     case datepart(dw, @currentdate)         when 1 0         when 2 1         when 3 1         when 4 1         when 5 1         when 6 1         when 7 0         end isweekday,     null holidayire, null, null       update          dimensiondate     set  [pastorfuture] =          case             when @startdate <= [date] 0             else 1           end 

set @currentdate = dateadd(dd, 1, @currentdate)

end select * dimensiondate

msg 213, level 16, state 1, line 99 column name or number of supplied values not match table definition.

i want fix update in @ bottom of code not letting me update pastorfuture column 0 or 1 depending on date

it works perfect if take out update , put in null.

your insert dimensiondate short column.

you have 3 columns after holidayire, insert has 2 nulls after case holidayire

either add column list insert, or add null end of insert.

for matter, no need update tack case on end of insert , make case last column , insert pastorfuture on fly...

case   when @startdate <= [date] 0   else 1 end