资源描述:
《zgh_产品日报表》由会员上传分享,免费在线阅读,更多相关内容在应用文档-天天文库。
1、--产品日报表--execGet_Product_Day'',0,'2010-10-01',0alterprocGet_Product_Day@NodeKeynvarchar(100),@IDint,@FDatevarchar(10),@FStoreIDint=0assetnocountondeclare@Countintdeclare@FSQEDatedatetime--上期结存时间declare@strSqlvarchar(8000)ifexists(select*fromdbo.sysobjectswhereid=ob
2、ject_id(N'[dbo].[Temp_Product_Day]')andOBJECTPROPERTY(id,N'IsUserTable')=1)droptable[dbo].[Temp_Product_Day]CREATETABLE[dbo].[Temp_Product_Day]([FID][int]IDENTITY(1,1)NOTNULL,[FProductID][int]NULL,[FDayInNumber][float]default(0)NULL,[FDayInPiece][float]default(0)NU
3、LL,[FDayInMoney][float]default(0)NULL,[FMonthInNumber][float]default(0)NULL,[FMonthInPiece][float]default(0)NULL,[FMonthInMoney][float]default(0)NULL,[FDayOutNumber][float]default(0)NULL,[FDayOutPiece][float]default(0)NULL,[FDayOutMoney][float]default(0)NULL,[FMont
4、hOutNumber][float]default(0)NULL,[FMonthOutPiece][float]default(0)NULL,[FMonthOutMoney][float]default(0)NULL,[FNowNumber][float]default(0)NULL,[FNowPiece][float]default(0)NULL,[FNowMoney][float]default(0)NULL)ON[PRIMARY]--1、得到成品IDif(@NodeKey=''and@ID<>0)insertintoT
5、emp_Product_Day(FProductID)values(@ID)elseinsertintoTemp_Product_Day(FProductID)selecti.FIDfromBase_CProductileftouterjoint_Itemoni.FParentID=t_Item.FIDwheret_Item.FItemClassID=35andt_Item.FNodeKeylike@NodeKey+'%'--2.1、组合期初数,直接计算到这天位置的库存数--set@FSQEDate=(selectmax(F
6、EDate)fromFMonth_ProductwhereFEDate7、FNowMoney=isnull(FQC.FMoney,0)from'set@strSql=@strSql+'(selectFFID,sum(FInitNumber)asFNumber,sum(FInitPiece)asFPiece,sum(FInitMoney)asFMoneyfromFInit_Product'set@strSql=@strSql+'whereFFIDin(selectFProductIDfromTemp_Product_Day)'set@strSql=@strSql+Casewhen@FStoreID=
8、0then''else'AndFStorageID='+rtrim(cast(@FStoreIDaschar(8)))endset@strSql=@strSql+'groupbyFFID)FQC'set@strSql=@strSql+'joinTemp_Product_DayTemp_Pr