直接SQL报表查询时报错，从数据类型varchar转换为datetime时出错,如图：
存储过程语句为：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description: <采购汇总表,,>
-- =============================================
alter PROCEDURE hy_cghzb
@gys varchar(100),
@FDateks datetime,
@FDatejs datetime
AS
BEGIN
SET NOCOUNT ON;
create table #hycghz
( zzdm varchar(100),
cgzz varchar(100),
wldm varchar(100),
wlmc varchar(100),
ggxh varchar(100),
cgdw varchar(100),
slsl decimal(18,4) default(0),
bhgtlsl decimal(18,4) default(0),
rksl decimal(18,4) default(0),
rkje decimal(18,2) default(0),
tlsl decimal(18,4) default(0),
tlje decimal(18,2) default(0)
)
--取收料通知单数据为送料数量
insert into #hycghz(zzdm,cgzz,wldm,wlmc,ggxh,cgdw,slsl)
select o.fnumber,o1.fname,a.fnumber,b.fname,b.fspecification,c.fname,sum(u1.FACTRECEIVEQTY )
from T_PUR_Receive v inner join T_PUR_ReceiveENTRY u1 on v.fid=u1.fid
--left join T_STK_INSTOCKENTRY_F u2 on u1.fid=u2.fid and u1.FENTRYID=u2.FENTRYID
left join T_ORG_Organizations o on o.forgid=v.FStockOrgId
left join T_ORG_Organizations_L o1 on o.forgid=o1.forgid
left join T_BD_MATERIAL a on a.FMATERIALID=u1.FMATERIALID
inner join T_BD_MATERIAL_L b on a.FMATERIALID=b.FMATERIALID
left join T_BD_MATERIALSTOCK d on d.FMATERIALID=a.FMATERIALID
left join t_BD_Unit_L c on c.FUNITID=d.fstoreunitid
left join t_BD_Supplier g on g.FSUPPLIERID=v.FSUPPLIERID
left join t_BD_Supplier_l g1 on g.fsupplierid=g1.fsupplierid
--left join t_BD_Stock_L k on k.fstockid=u1.fstockid
where 1=1 and o.fdocumentstatus='C'
and v.fdate>=@FDateks
and v.fdate<=@FDatejs
and g1.fname like '%'+@gys+'%'
--and g.fnumber<=''
--and a.fnumber>=''
--and a.fnumber<=''
group by o.fnumber,o1.fname,a.fnumber,b.fname,b.fname,b.fspecification,c.fname
--取退料单（退料补料）数量为不合格退料数量
insert into #hycghz(zzdm,cgzz,wldm,wlmc,ggxh,cgdw,bhgtlsl)
select o.fnumber,o1.fname,a.fnumber,b.fname,b.fspecification,c.fname,sum(u1.FRMREALQTY)
from t_PUR_MRB v inner join T_PUR_MRBENTRY u1 on v.fid=u1.fid
left join T_PUR_MRBFIN u2 on u1.fid=u2.fid and u1.FENTRYID=u2.FENTRYID
left join T_ORG_Organizations o on o.forgid=v.FStockOrgId
left join T_ORG_Organizations_L o1 on o.forgid=o1.forgid
left join T_BD_MATERIAL a on a.FMATERIALID=u1.FMATERIALID
inner join T_BD_MATERIAL_L b on a.FMATERIALID=b.FMATERIALID
left join T_BD_MATERIALSTOCK d on d.FMATERIALID=a.FMATERIALID
left join t_BD_Unit_L c on c.FUNITID=d.fstoreunitid
left join t_BD_Supplier g on g.FSUPPLIERID=v.FSUPPLIERID
left join t_BD_Supplier_l g1 on g.fsupplierid=g1.fsupplierid
--left join t_BD_Stock_L k on k.fstockid=u1.fstockid
where 1=1 and o.fdocumentstatus='C'
and v.FMRMODE='A'
and v.fdate>=@FDateks
and v.fdate<=@FDatejs
and g1.fname like '%'+@gys+'%'
--and g.fnumber<=''
--and a.fnumber>=''
--and a.fnumber<=''
group by o.fnumber,o1.fname,a.fnumber,b.fname,b.fname,b.fspecification,c.fname
--取采购入库单数量为入库数量
insert into #hycghz(zzdm,cgzz,wldm,wlmc,ggxh,cgdw,rksl,rkje)
select o.fnumber,o1.fname,a.fnumber,b.fname,b.fspecification,c.fname,sum(u1.FREALQTY),sum(isnull(u2.FAMOUNT,0))
from T_STK_INSTOCK v inner join T_STK_INSTOCKENTRY u1 on v.fid=u1.fid
left join T_STK_INSTOCKENTRY_F u2 on u1.fid=u2.fid and u1.FENTRYID=u2.FENTRYID
left join T_ORG_Organizations o on o.forgid=v.FStockOrgId
left join T_ORG_Organizations_L o1 on o.forgid=o1.forgid
left join T_BD_MATERIAL a on a.FMATERIALID=u1.FMATERIALID
inner join T_BD_MATERIAL_L b on a.FMATERIALID=b.FMATERIALID
left join T_BD_MATERIALSTOCK d on d.FMATERIALID=a.FMATERIALID
left join t_BD_Unit_L c on c.FUNITID=d.fstoreunitid
left join t_BD_Supplier g on g.FSUPPLIERID=v.FSUPPLIERID
left join t_BD_Supplier_l g1 on g.fsupplierid=g1.fsupplierid
--left join t_BD_Stock_L k on k.fstockid=u1.fstockid
where 1=1 and o.fdocumentstatus='C'
and v.fdate>=@FDateks
and v.fdate<=@FDatejs
and g1.fname like '%'+@gys+'%'
--and g.fnumber<=''
--and a.fnumber>=''
--and a.fnumber<=''
group by o.fnumber,o1.fname,a.fnumber,b.fname,b.fname,b.fspecification,c.fname
--取退料单（退料并扣款）数量为不合格退料数量
insert into #hycghz(zzdm,cgzz,wldm,wlmc,ggxh,cgdw,tlsl,tlje)
select o.fnumber,o1.fname,a.fnumber,b.fname,b.fspecification,c.fname,sum(u1.FRMREALQTY),sum(isnull(u2.FBILLCOSTAMOUNT,0))
from t_PUR_MRB v inner join T_PUR_MRBENTRY u1 on v.fid=u1.fid
left join T_PUR_MRBFIN u2 on u1.fid=u2.fid and u1.FENTRYID=u2.FENTRYID
left join T_ORG_Organizations o on o.forgid=v.FStockOrgId
left join T_ORG_Organizations_L o1 on o.forgid=o1.forgid
left join T_BD_MATERIAL a on a.FMATERIALID=u1.FMATERIALID
inner join T_BD_MATERIAL_L b on a.FMATERIALID=b.FMATERIALID
left join T_BD_MATERIALSTOCK d on d.FMATERIALID=a.FMATERIALID
left join t_BD_Unit_L c on c.FUNITID=d.fstoreunitid
left join t_BD_Supplier g on g.FSUPPLIERID=v.FSUPPLIERID
left join t_BD_Supplier_l g1 on g.fsupplierid=g1.fsupplierid
--left join t_BD_Stock_L k on k.fstockid=u1.fstockid
where 1=1 and o.fdocumentstatus='C'
and v.FMRMODE='B'
and v.fdate>=@FDateks
and v.fdate<=@FDatejs
and g1.fname like '%'+@gys+'%'
--and g.fnumber<=''
--and a.fnumber>=''
--and a.fnumber<=''
group by o.fnumber,o1.fname,a.fnumber,b.fname,b.fname,b.fspecification,c.fname
create table #hycghz1
( nid varchar(100),
zzdm varchar(100),
cgzz varchar(100),
wldm varchar(100),
wlmc varchar(100),
ggxh varchar(100),
cgdw varchar(100),
slsl decimal(18,4) default(0),
bhgtlsl decimal(18,4) default(0),
rksl decimal(18,4) default(0),
rkje decimal(18,2) default(0),
tlsl decimal(18,4) default(0),
tlje decimal(18,2) default(0),
sssl decimal(18,2) default(0),
ssje decimal(18,2) default(0)
)
insert into #hycghz1(nid,zzdm,cgzz,wldm,wlmc,ggxh,cgdw,slsl,bhgtlsl,rksl,rkje,tlsl,tlje)
select '01',zzdm,cgzz,wldm,wlmc,ggxh,cgdw,sum(slsl),sum(bhgtlsl),sum(rksl),sum(rkje),sum(tlsl),sum(tlje)
from #hycghz
group by zzdm,cgzz,wldm,wlmc,ggxh,cgdw
insert into #hycghz1(nid,zzdm,cgzz,wldm,wlmc,ggxh,cgdw,slsl,bhgtlsl,rksl,rkje,tlsl,tlje)
select '02',zzdm,cgzz,'合计','','','',sum(slsl),sum(bhgtlsl),sum(rksl),sum(rkje),sum(tlsl),sum(tlje)
from #hycghz
group by zzdm,cgzz
update #hycghz1 set sssl=rksl-tlsl
update #hycghz1 set ssje=rkje-tlje
select zzdm 组织代码,cgzz 组织名称,wldm 物料代码,wlmc 物料名称,ggxh 规格型号,cgdw 单位,slsl 送料数量, bhgtlsl 不合格退料数量,rksl 入库数量,rkje 入库金额,tlsl 退料数量,tlje 退料金额, sssl 实收数量,ssje 实收金额
from #hycghz1 order by nid,zzdm,wldm
drop table #hycghz
drop table #hycghz1
END
GO
推荐阅读