一、单语句表值函数
ALTER function [dbo].[uf_get_jxc_da_sum](@dt char(8),@dt2 char(8))
RETURNS table
as
return(
select aa.outlet_id,aa.store_id as storespace_id,aa.supp_id as supplier_id,aa.aid, aa.q as curr_q, aa.ea as curr_ea , aa.ra as curr_ra,aa.dj_ea as curr_dj_ea,
quota_q
,quota_ea
,quota_ra
,quota_diff_ea
,quota_diff_ra
,entry_q
,entry_ea
,entry_ra
,transferin_q
,transferin_ea
,transferin_ra
,retquota_q
,retquota_ea
,retquota_ra
,retpurchase_q
,retpurchase_ea
,retpurchase_ra
,transferout_q
,transferout_ea
,transferout_ra
,pftloss_in_q
,pftloss_in_ea
,pftloss_in_ra
,pftloss_out_q
,pftloss_out_ea
,pftloss_out_ra
,inventory_in_q
,inventory_in_ea
,inventory_in_ra
,inventory_out_q
,inventory_out_ea
,inventory_out_ra
,movestock_q
,movestock_ea
,movestock_ra
,sale_q
,sale_ea
,sale_ra
,favour_ra
,fifo_adjust_in_q
,fifo_adjust_in_ea
,fifo_adjust_out_q
,fifo_adjust_out_ea
,adjust_in_ea
,adjust_in_ra
,adjust_out_ea
,adjust_out_ra
,retpurchase_dj_ea,
case left(aa.outlet_id,1) when 'D' then (isnull(q,0) - isnull(entry_q,0) + isnull(quota_q,0) - isnull(fifo_adjust_in_q,0) - isnull(pftloss_in_q,0) - isnull(inventory_in_q,0) + isnull(sale_q,0) - isnull(retquota_q,0) + isnull(retpurchase_q,0) + isnull(pftloss_out_q,0) + isnull(inventory_out_q,0) + isnull(fifo_adjust_out_q,0))
else (isnull(q,0) - isnull(entry_q,0) - isnull(quota_q,0) - isnull(fifo_adjust_in_q,0) - isnull(pftloss_in_q,0) - isnull(inventory_in_q,0) - isnull(transferin_q,0) + isnull(sale_q,0) + isnull(retquota_q,0) + isnull(retpurchase_q,0) + isnull(pftloss_out_q,0) + isnull(inventory_out_q,0) + isnull(fifo_adjust_out_q,0) + isnull(transferout_q,0))
end as last_q,
case left(aa.outlet_id,1) when 'D' then (isnull(ea,0) - isnull(entry_ea,0) + isnull(quota_ea,0) - isnull(fifo_adjust_in_ea,0) - isnull(pftloss_in_ea,0) - isnull(inventory_in_ea,0) - isnull(adjust_in_ea,0) + isnull(sale_ea,0) - isnull(retquota_ea,0) + isnull(retpurchase_ea,0) + isnull(pftloss_out_ea,0) + isnull(inventory_out_ea,0) + isnull(fifo_adjust_out_ea,0) + isnull(adjust_out_ea,0))
else (isnull(ea,0) - isnull(entry_ea,0) - isnull(quota_ea,0) - isnull(fifo_adjust_in_ea,0) - isnull(pftloss_in_ea,0) - isnull(inventory_in_ea,0) - isnull(adjust_in_ea,0) - isnull(transferin_ea,0) + isnull(sale_ea,0) + isnull(retquota_ea,0) + isnull(retpurchase_ea,0) + isnull(pftloss_out_ea,0) + isnull(inventory_out_ea,0) + isnull(fifo_adjust_out_ea,0) + isnull(adjust_out_ea,0) + isnull(transferout_ea,0))
end as last_ea,
case left(aa.outlet_id,1) when 'D' then (isnull(ra,0) - isnull(entry_ra,0) + isnull(quota_ra,0) - isnull(pftloss_in_ra,0) - isnull(inventory_in_ra,0) - isnull(adjust_in_ra,0) + isnull(sale_ra,0) + isnull(favour_ra,0) - isnull(retquota_ra,0) + isnull(retpurchase_ra,0) + isnull(pftloss_out_ra,0) + isnull(inventory_out_ra,0) + isnull(adjust_out_ra,0))
else (isnull(ra,0) - isnull(entry_ra,0) - isnull(quota_ra,0) - isnull(pftloss_in_ra,0) - isnull(inventory_in_ra,0) - isnull(adjust_in_ra,0) - isnull(transferin_ra,0) + isnull(sale_ra,0) + isnull(favour_ra,0) + isnull(retquota_ra,0) + isnull(retpurchase_ra,0) + isnull(pftloss_out_ra,0) + isnull(inventory_out_ra,0) + isnull(adjust_out_ra,0) + isnull(transferout_ra,0))
end as last_ra,
-- case left(aa.outlet_id,1) when 'D' then (isnull(dj_ea,0) - isnull(entry_ea,0) + isnull(quota_ea,0) - isnull(fifo_adjust_in_ea,0) - isnull(pftloss_in_ea,0) - isnull(inventory_in_ea,0) - isnull(adjust_in_ea,0) + isnull(sale_ea,0) - isnull(retquota_ea,0) + isnull(retpurchase_dj_ea,0) + isnull(pftloss_out_ea,0) + isnull(inventory_out_ea,0) + isnull(fifo_adjust_out_ea,0) + isnull(adjust_out_ea,0))
-- else (isnull(dj_ea,0) - isnull(entry_ea,0) - isnull(quota_ea,0) - isnull(fifo_adjust_in_ea,0) - isnull(pftloss_in_ea,0) - isnull(inventory_in_ea,0) - isnull(adjust_in_ea,0) - isnull(transferin_ea,0) + isnull(sale_ea,0) + isnull(retquota_ea,0) + isnull(retpurchase_ea,0) + isnull(pftloss_out_ea,0) + isnull(inventory_out_ea,0) + isnull(fifo_adjust_out_ea,0) + isnull(adjust_out_ea,0) + isnull(transferout_ea,0))
-- end as last_dj_ea
(isnull(dj_ea,0) - isnull(entry_ea,0) - isnull(fifo_adjust_in_ea,0) - isnull(pftloss_in_ea,0) - isnull(inventory_in_ea,0) - isnull(adjust_in_ea,0) + isnull(sale_ea,0) + isnull(retpurchase_dj_ea,0) + isnull(pftloss_out_ea,0) + isnull(inventory_out_ea,0) + isnull(fifo_adjust_out_ea,0) + isnull(adjust_out_ea,0)) as last_dj_ea
from
dbo.uf_get_stock_da_curr(@dt2) aa
left join
dbo.uf_get_jxc_sum(@dt,@dt2) bb
on
aa.outlet_id = bb.outlet_id
and aa.store_id = bb.storespace_id
and aa.supp_id = bb.supplier_id
and aa.aid = bb.aid
where left(aa.outlet_id,1)<>'8'
)
二、多语句表值函数
alter function [dbo].[f_getFjxx](@fjh varchar(50),@dt varchar(10))
returns @fjxx table (FJXXMX_FJH varchar(10) NULL,FJXXMX_HKGSDM varchar(10) NULL,FJXXMX_3ZM varchar(10) NULL,FJXXMX_ZDQFQZ float NULL,FJXXMX_ZDZW float NULL,FJXXMX_KGZW float NULL,FJXXMX_ZDYZ float NULL,FJXXMX_KGYZ float NULL,FJXXMX_XGRQ datetime NULL)
as
begin
declare @id int,
@max_xgrq varchar(10)
select @max_xgrq= max(CONVERT(varchar(10),fjxxmx_xgrq,120)) from ARP_FJXXMX where FJXXMX_FJH=@fjh and @dt>=CONVERT(varchar(10),fjxxmx_xgrq,120)
select @id= fjxxmx_id from ARP_FJXXMX where FJXXMX_FJH=@fjh and CONVERT(varchar(10),fjxxmx_xgrq,120)=@max_xgrq
insert into @fjxx(FJXXMX_FJH,FJXXMX_HKGSDM,FJXXMX_3ZM,FJXXMX_ZDQFQZ,FJXXMX_ZDZW,FJXXMX_KGZW,FJXXMX_ZDYZ,FJXXMX_KGYZ,FJXXMX_XGRQ)
select FJXXMX_FJH,FJXXMX_HKGSDM,FJXXMX_3ZM,FJXXMX_ZDQFQZ,FJXXMX_ZDZW,FJXXMX_KGZW,FJXXMX_ZDYZ,FJXXMX_KGYZ,FJXXMX_XGRQ
from ARP_FJXXMX where FJXXMX_ID=@id
return
end