1.输出报表效果如下图
19186
准备数据1,获取进货明细
准备数据2,函数
ALTER Function .(
@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