-- 作者：jackwin
-- 发布时间：2014/6/19 14:50:00
-- [求助]SQL在fox中的实现
各位
写了串sql查询 ，其中有两个变量：开始时期和结束日期。
问题：希望通过时间输入控件关联sql的变量实现查询
你把参数代入值，测试一下看看
select \'19000101\' as 库存年月,
\'A010110010210201A0101-00102-0201\' as 产品编码,
\'700103600000700103600000\' as 南菲编码,
\'1111A\' as 仓库编号,
99999999.9999 as 单位成本,
99999999.9999 as 本月销货数量,
99999999.9999 as 本月销货金额
into #a
insert into #a
EXEC zf_srv.fanski.dbo.[me_procGetMaterialCost] \'01A\',@开始日期,@结束日期
/*----++++*/
select left(CONVERT(varchar(100),w.c_au_dt,112),6) as 业务年月,
bar.c_barcode as 主条码,
gds.c_name as 产品名称,
sum(isnull(c_number,0)+isnull(c_number_free,0))as 数量,
case when sum(isnull(#a.单位成本,0))=0 then sum(isnull(wg.c_pt_cost,0)/(isnull(wg.c_tax_rate,0)/100+1)*(isnull(wg.c_number,0)+isnull(wg.c_number_free,0)))
else sum(isnull(#a.单位成本,0)*(isnull(c_number,0)+isnull(c_number_free,0))) end as 成本金额
from tb_o_w w(nolock)
left join tb_o_wg wg(nolock) on w.c_id=wg.c_id
left join tb_gds gds(nolock) on wg.c_gcode=gds.c_gcode
left join tb_barcode bar(nolock) on gds.c_gcode=bar.c_gcode
left join tb_cust_orderg odg(nolock) on odg.c_id=wg.c_order_id and odg.c_gcode=wg.c_gcode and wg.c_in_guid=odg.c_guid
left join tb_cust_order od(nolock) on odg.c_id=od.c_id
left join #a on bar.c_barcode=#a.南菲编码 and left(CONVERT(varchar(100),w.c_au_dt,112),6)=#a.库存年月
where (wg.c_number<>0 or wg.c_number_free<>0)
and isnull(od.c_order_type,\'\')<>\'物料订单\'
and w.c_status=\'已审核\'
and (isnull(@开始日期,\'\')=\'\' or DATEDIFF(day,w.c_au_Dt,@开始日期)<=0)
and (isnull(@结束日期,\'\')=\'\' or DATEDIFF(day,w.c_au_Dt,@结束日期)>=0)
group by bar.c_barcode,gds.c_name,left(CONVERT(varchar(100),w.c_au_dt,112),6)
|