if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Cal_JieCun]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Cal_JieCun]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Cal_JieCun( @JCMonth char(7) )
as
--@JCMonth结存月
declare @intemp numeric(18,2)
declare @outtemp numeric(18,2)
declare @avgprice numeric(18,2)--COPY上月数据,本月期初数据=上月结存数据
insert into jiecuntemp(JieCunMonth,BomID,BomName,STANDARD,UNITS,QC_QTY,QC_AMOUNT,INQTY,OUTQTY,AVGPRICE,OUTAMOUNT,JIECUN,JIECUNAMOUNT)
select @JCMonth,bomid,bomname,standard,units,jiecun,jiecunamount,0,0,0,0,0,0
from StockJieCun where JieCunMonth=@JCMonth
--修改数据,入库,出库数据及入库金额,出库金额等update jiecuntemp set
inqty=(select isnull(sum(qty),0) from Indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomid ), --本月入库数
outqty=(select isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate,120)=@JCMonth and status='2' and bomid=a.bomid),
inamount=(select isnull(sum(amount),0) from Indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomid),
/*平均单价*/
avgprice=(select case when QC_QTY=0 and inqty=0 then 0 else (QC_AMOUNT+INAMOUNT)/(QC_QTY+INQTY) end ),
/*出库金额*/
outamount=outqty*avgprice,
/*结存数*/
jiecun=qc_qty+inqty-outqty,
/*结存金额*/
jiecunamount=qc_amount+inamount-outamount
from jiecuntemp a--处理不在结存表中的新物料
// 下面的语句太长了我相精简一点。赋给变量现错!
--(select @intemp=Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and
--bomid=a.bomxh) 这样也不行!
insert into jiecuntemp(JieCunMonth,BomID,BomName,STANDARD,UNITS,QC_QTY,QC_AMOUNT,INQTY,OUTQTY,AVGPRICE,OUTAMOUNT,JIECUN,JIECUNAMOUNT)
select @JCMonth,Bomxh,bomname,standard,unit,0,0,
--入库数量
(select Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--出库数量
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--平均单价
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh),
--出库金额
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)*
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--结存数
(select Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and bomid=a.bomxh)-
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--结存金额
(select sum(amount) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)-
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)*
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh)
from bominfo a where bomxh not in( select bomid from jiecuntemp )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[Cal_JieCun]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE Cal_JieCun( @JCMonth char(7) )
as
--@JCMonth结存月
declare @intemp numeric(18,2)
declare @outtemp numeric(18,2)
declare @avgprice numeric(18,2)--COPY上月数据,本月期初数据=上月结存数据
insert into jiecuntemp(JieCunMonth,BomID,BomName,STANDARD,UNITS,QC_QTY,QC_AMOUNT,INQTY,OUTQTY,AVGPRICE,OUTAMOUNT,JIECUN,JIECUNAMOUNT)
select @JCMonth,bomid,bomname,standard,units,jiecun,jiecunamount,0,0,0,0,0,0
from StockJieCun where JieCunMonth=@JCMonth
--修改数据,入库,出库数据及入库金额,出库金额等update jiecuntemp set
inqty=(select isnull(sum(qty),0) from Indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomid ), --本月入库数
outqty=(select isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate,120)=@JCMonth and status='2' and bomid=a.bomid),
inamount=(select isnull(sum(amount),0) from Indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomid),
/*平均单价*/
avgprice=(select case when QC_QTY=0 and inqty=0 then 0 else (QC_AMOUNT+INAMOUNT)/(QC_QTY+INQTY) end ),
/*出库金额*/
outamount=outqty*avgprice,
/*结存数*/
jiecun=qc_qty+inqty-outqty,
/*结存金额*/
jiecunamount=qc_amount+inamount-outamount
from jiecuntemp a--处理不在结存表中的新物料
// 下面的语句太长了我相精简一点。赋给变量现错!
--(select @intemp=Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and
--bomid=a.bomxh) 这样也不行!
insert into jiecuntemp(JieCunMonth,BomID,BomName,STANDARD,UNITS,QC_QTY,QC_AMOUNT,INQTY,OUTQTY,AVGPRICE,OUTAMOUNT,JIECUN,JIECUNAMOUNT)
select @JCMonth,Bomxh,bomname,standard,unit,0,0,
--入库数量
(select Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--出库数量
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--平均单价
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh),
--出库金额
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)*
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--结存数
(select Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate ,120)=@JCMonth and status='2' and bomid=a.bomxh)-
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh),
--结存金额
(select sum(amount) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)-
(select Isnull(sum(amount),0)/Isnull(sum(qty),0) from indelivery_d1 where convert(char(7),rkdate,120)=@JCMonth and status='2' and bomid=a.bomxh)*
(select Isnull(sum(qty),0) from outdelivery_d1 where convert(char(7),ckdate ,120)=@JCMonth and status='2' and bomid=a.bomxh)
from bominfo a where bomxh not in( select bomid from jiecuntemp )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
解决方案 »
- 如何在网页中调用动态库?
- 使用ehlib,如何才能同时显示流水号?
- 一个关于PC Anywhere 的问题,高手请进,谢谢 ......
- 正则表达式的问题?
- DGBrid很菜的问题
- How to be a programer?大家请进
- 为什么我的程序在98下关闭时会出现非法操作的错误?在2k下没有的,我是在2k下编译的
- 关于access数据库中的日期/时间字段的查询??(在线等待)
- 怎样设置edit控件只能输入时间型数据(要有固定格式)?
- 记录型二进制文件如何从中删除指定记录??
- 急,怎么对用户自定义对象怎么持久化 其中有私有成员??最好有源码.
- delphi中dbgrid输入新数据时判断是否与以前内容重复的问题
不知道是怎么意思?