vendredi 18 décembre 2015

Loop based on parameter value

I need to create a temporary table that is populated based on two parameters:

declare @Start date = '01/01/2015'
declare @End date = '12/31/2015'

The temporary table should have a column that will capture YYYYMM for all the years and month that are between @Start and @End parameter.

Here's what I have so far. I want to stop it at 201412 and then start again at 201501. Instead, this loop keeps going in increment of plus 1 (I do not want to see 201413..so on):

declare @Start date = '01/01/2014'
declare @End date = '12/31/2015'

declare @monthstart as int
declare @monthend as int
declare @increment as int

set @monthstart = (SELECT LEFT(CONVERT(varchar, @Start,112),6))
set @monthend = (SELECT LEFT(CONVERT(varchar, @End,112),6))

create table #datetemp  (RelevantYYYYMM int)
insert into #datetemp values (@monthstart)
set @increment = @monthstart


While @increment < @monthend
BEGIN 
set @increment = (select Max(RelevantYYYYMM) + 1 from #datetemp)
insert into #datetemp values (@increment)
set @increment = (select Max(RelevantYYYYMM) from #datetemp)
IF (select Max(RelevantYYYYMM) from #datetemp) > @monthend
Break
else 
continue
END

select * from #datetemp

Aucun commentaire:

Enregistrer un commentaire