1.输出报表效果如下图 准备数据1，获取进货明细 准备数据2，函数 ALTER Function [dbo].[UF_GetLastPrice]( @TL004 AS nvarchar(10), @date as nvarchar(8), @item as nvarchar(30) )returns decimal(18,6) as begin --set @TL004='09001' --set @date='20150116' --set @item='10102069620001'; declare @price as decimal(18,6); with b as( select TM004,TM014,TM010,TL004 from PURTL inner join PURTM ON TL001=TM001 AND TL002=TM002 where TM011='Y' AND TL004=@TL004 and TM014<=@date and TM004=@item) select @price=isnull(t.TM010,0) from ( select b.TM004,b.TM010,ROW_NUMBER() over(partition by b.TM004 order by b.TM014 desc )rn from b ) t where rn=1 return @price end |
