分期收款发出模式下，查询截止日期前的发出商品明细

供应链-仓库管理-查询分析-查询分析工具-新建-直接sql报表，建立关键字：@enddate@  标题：出库日期小于   数据类型：日期型  数据来源：手工录入；sql语句如下：

--删除临时表

if OBJECT_ID('tempdb..#kehu') is not null drop table #kehu;

if OBJECT_ID('tempdb..#xiaoshou') is not null drop table #xiaoshou;

if OBJECT_ID('tempdb..#fachu') is not null drop table #fachu;

--根据基础资料建立客户临时表

select * into #kehu from t_Item where FItemClassID='1';

--根据出库单、基础资料表建临时表

select ICStockBill.FBillNo as 'FBillNo',

#kehu.fnumber as 'khfnumber',

#kehu.fname as 'khfname',

CONVERT(varchar(10), ICStockBill.FDate, 23) as 'FDate',

t_Item.FName as 'wlFName',

ICStockBillEntry.FQty as'FQty',

ICStockBillEntry.FPrice as'FPrice',

ICStockBillEntry.FAmount as 'FAmount',

ICStockBill.fhookinterid as'fhookinterid',

icstockbillentry.fentryid as'fentryid'

into #xiaoshou

from ICStockBillEntry

left join ICStockBill on ICStockBill.FInterID=ICStockBillEntry.FInterID

left join #kehu on #kehu.fitemid=ICStockBill.FSupplyID

left join t_Item on t_Item.FItemID=ICStockBillEntry.FItemID

where ICStockBill.FTranType=21;

--根据勾稽单据查询发出商品明细，建立临时表

select #xiaoshou.fbillno as'出库单号',

 #xiaoshou.khfnumber as'客户代码',

 #xiaoshou.khfname as'客户名称',

 #xiaoshou.fdate as'出库日期',

 ICHookRelations.FIBNo as'发票号码',

 cONVERT(varchar(10),ICHookRelations.FDate,23) as'签收日期',

 #xiaoshou.wlfname as'商品名称',

 #xiaoshou.fqty as'数量',

 #xiaoshou.fprice as'单价',

 #xiaoshou.famount as'金额'

 into #fachu

 from #xiaoshou

left join ICHookRelations on ICHookRelations.FGroupNo=#xiaoshou.fhookinterid 

and ICHookRelations.FEntryID=#xiaoshou.fentryid

where (ICHookRelations.FIBNo  not like'XSCK%' 

and ICHookRelations.FHookType=1

and #xiaoshou.FDate<'@enddate@')

and  CONVERT(varchar(10),ICHookRelations.FDate,23)>='@enddate@'

or #xiaoshou.FDate<'@enddate@' and ICHookRelations.FDate is null

--查询临时表的记录并关联查询合计行

select * from #fachu 

union all

select '合计','0','0','0','0','0','0',SUM(数量),'0',SUM(金额)from #fachu