1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for enthusiasts and scholars of mythology and folklore

declare @Tbl table
(
  FirstColumn datetime primary key,
  S_E1 float
)

insert into @Tbl(FirstColumn, S_E1) values
('2012-01-01T00:00:01', 1),
('2012-01-01T00:00:02', 2),
('2012-01-01T00:00:03', 3),
('2012-01-01T00:00:04', 4),
('2012-01-01T00:00:05', 5),
('2012-01-01T00:00:06', 6)

declare @Start varchar(10) = '1'
declare @End varchar(10) = '3'
declare @ColumnName varchar(5) = 'S_E1'
declare @StartDate varchar(20)='2012-01-01T00:00:01'
declare @EndDate varchar(20) = '2012-01-01T00:00:06'
declare @Skip varchar(10) = 0
declare @Take varchar(2) = 4
DECLARE @SQL nvarchar(Max)
declare @count varchar(Max)

set @count = (select count(FirstColumn) from @Tbl Where FirstColumn Between ''+@StartDate+'' and ''+@EndDate+'')
--set @SQL =' select S_E1 from @T where S_E1 ='+ @Take+''
BEGIN try
while(CAST(@Skip AS int) < CAST(@count AS int))
  Begin
        set @SQL = 'select avg('+@ColumnName+') from (select '+@ColumnName+',row_number() over(order by FirstColumn) as rn from @Tbl
                  Where 
                         FirstColumn Between '''+@StartDate+''' and '''+@EndDate+'''  And ('+@ColumnName+' Between '+@Start+' And '+@End+' )
                 ) T
            where rn > '+@Skip+' and 
            rn <= '+Convert(varchar,CAST(@Skip AS int)+'+'+CAST(@Take AS int))+''
         EXEC sp_executesql @SQL
          set @Skip = CONVERT(varchar,CAST(@Skip AS int)+CAST(@Take AS int))
   end
 END TRY
 BEGIN CATCH
        select ERROR_MESSAGE() as errormessage
 END CATCH

fork query

edited may 21 12

created may 21 12 jon.doe7984