资源描述：
《sql库存明细帐处理示例包含结存数.doc》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、sql库存明细帐处理示例(包含结存数)--结存表CREATETABLEStocks(Itemvarchar(10),Periodint,Balanceint)INSERTStocksSELECT'aa',200501,100UNIONALLSELECT'cc',200501,100--明细账数据CREATETABLEtb(IDintIDENTITYPRIMARYKEY,Itemvarchar(10),--产品编号Quantityint,--交易数量Flagbit,--交易标志,1代表入库,0代表出库,这样
2、可以有效区分退货(负数)Datedatetime)--交易日期INSERTtbSELECT'aa',100,1,'2005-1-1'UNIONALLSELECT'aa',90,1,'2005-2-1'UNIONALLSELECT'aa',55,0,'2005-2-1'UNIONALLSELECT'aa',-10,1,'2005-2-2'UNIONALLSELECT'aa',-5,0,'2
3、005-2-3'UNIONALLSELECT'aa',200,1,'2005-2-2'UNIONALLSELECT'aa',90,1,'2005-2-1'UNIONALLSELECT'bb',95,1,'2005-2-2'UNIONALLSELECT'bb',65,0,'2005-2-3'UNIONALLSELECT'bb',-15,1,'2005-2-5'UNIONAL
4、LSELECT'bb',-20,0,'2005-2-5'UNIONALLSELECT'bb',100,1,'2005-2-7'UNIONALLSELECT'cc',100,1,'2005-1-7'GO--查询时间段定义DECLARE@dt1datetime,@dt2datetimeSELECT@dt1='2005-2-2',@dt2='2005-2-10'--查询--期初库存年月及计算期初数的开始时间)DECL
5、ARE@Periodint,@dtdatetimeSELECT@Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112),@dt=DATEADD(Day,1-Day(@dt1),@dt1)--查询期初库存SELECTItem=ISNULL(a.Item,b.Item),Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),[IN]=ISNULL(b.[IN],
6、0),[IN_Retrun]=ISNULL(b.[IN_Retrun],0),[OUT]=ISNULL(b.[OUT],0),[OUT_Return]=ISNULL(b.[OUT_Return],0),Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)FROM(--期初数SELECTItem,BalanceFROMStocksWHEREPeriod=@Period)aFULLJOIN(--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)S
7、ELECTItem,Date=CONVERT(char(10),@dt1,120),Opening=SUM(CASEWHENFlag=1THENQuantityELSE-QuantityEND),[IN]=0,[IN_Retrun]=0,[OUT]=0,[OUT_Return]=0,Amount=0FROMtbaWHEREDate>=@dtANDDate<@dt1ANDNOTEXISTS(SELECT*FROMtbWHEREItem=a.ItemANDDate>@dt1ANDDate<DATEADD(Day,1,
8、@dt2))GROUPBYItemUNIONALL--指定时间段内有交易发生的数据SELECTItem,Date=CONVERT(char(10),Date,120),Opening=(SELECTSUM(
