Sybase IQ does not have native lead and lag functions .. here is how to add these as custom functions.
create function fn_lead (
@table_nm varchar(30),
@column_nm varchar(30),
@filter_fld_nm varchar(30),
@filter_fld_val integer,
@pivot_fld_nm varchar(30),
@pivot_fld_val integer, @span integer )
returns real
as
declare @sqlcmd varchar(1000),
@pivot real,
@i integer
select @i = 0
while (@i < @span)
begin
select @i = @i + 1
select @sqlcmd = 'select @pivot=' +
'min(' + @pivot_fld_nm + ') ' + ' from ' + @table_nm +
' where ' + @pivot_fld_nm + ' > ' + convert(varchar(15), @pivot_fld_val )
execute (@sqlcmd)
select @pivot_fld_val = @pivot
end
declare @val real
select @sqlcmd = 'select @val = ' + @column_nm + ' from ' + @table_nm + ' where ' +
@filter_fld_nm + ' = ' + convert( varchar(12),@filter_fld_val ) + ' and ' +
@pivot_fld_nm + ' = ' + convert( varchar(12),@pivot )
execute (@sqlcmd)
return @val
go
-- sample call
declare @val real
select @val = fn_lead ( 'sales_history','gross_sales','salesman_id',333, 'period_id',200404,2)
select @val as result111
go
create function fn_lag (
@table_nm varchar(30),
@column_nm varchar(30),
@filter_fld_nm varchar(30),
@filter_fld_val integer,
@pivot_fld_nm varchar(30),
@pivot_fld_val integer, @span integer )
returns real
as
declare @sqlcmd varchar(1000),
@pivot real,
@i integer
select @i = 0
while (@i < @span)
begin
select @i = @i + 1
select @sqlcmd = 'select @pivot=' +
'max(' + @pivot_fld_nm + ') ' + ' from ' + @table_nm +
' where ' + @pivot_fld_nm + ' < ' + convert(varchar(15), @pivot_fld_val )
execute (@sqlcmd)
select @pivot_fld_val = @pivot
end
declare @val real
select @sqlcmd = 'select @val = ' + @column_nm + ' from ' + @table_nm + ' where ' +
@filter_fld_nm + ' = ' + convert( varchar(12),@filter_fld_val ) + ' and ' +
@pivot_fld_nm + ' = ' + convert( varchar(12),@pivot )
execute (@sqlcmd)
return @val
go
-- sample call
declare @val real
select @val = fn_lag ( 'flight_log','total_complaints','flight_key',555, 'period_id',200404,2)
select @val as result111
go
|
|
