库存台帐报表存储过程

最新推荐文章于 2023-02-23 11:29:25 发布

weixin_30508241

最新推荐文章于 2023-02-23 11:29:25 发布

阅读量313

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/davidathena/archive/2007/06/04/770912.html

库存台帐报表存储过程，包含While语句，两次循环，原理很简单，但是提供了两次循环的操作方法。

------------------------------------
2

--用途：查询，统计,用于库存台帐
3

------------------------------------
4

CREATE PROCEDURE dbo.Jmeport_get_storage_price
5

@maxGoodsCode varchar(10), --商品编码大
7

@minGoodsCode varchar(10), --商品编码小
8

@StartTime datetime, --日期大
9

@EndTime datetime, --日期小
10

@maxStorage varchar(10), --仓库编码大
11

@minStorage varchar(10) --仓库编码小
12

AS
14

SET NOCOUNT ON
15

declare @SQL nvarchar(2000)
16

CREATE TABLE #A(goodsnumber varchar(10),goodsname varchar(20),goodstype varchar(20),dept varchar(10),storageid varchar(10),storage varchar(36),incount decimal(10,2),
18

outcount decimal(10,2),remaincount decimal(10,2),averageprice decimal(10,2),countprice decimal(10,2))
19

SET @SQL=' AND(1=1)'
21

IF @maxGoodsCode<>''
23

SET @SQL=@SQL+' AND( BB05.BB0501<=@maxGoodsCode)'
24

IF @minGoodsCode<>''
25

SET @SQL=@SQL+' AND( BB05.BB0501>=@minGoodsCode)'
26

IF @maxStorage<>''
27

SET @SQL=@SQL+' AND( BA05.BA0501>=@maxStorage)'
28

IF @minStorage<>''
29

SET @SQL=@SQL+' AND( BA05.BA0501<=@minStorage)'
30

--因为没有涉及到出入库内容，所以需要显示全部仓库和商品信息，如果出入库内容不存在，就把那两列设为0
33

--SET @SQL=' AND(1=1)'
34

SET @SQL=N'
35

select BB05.BB0501,BB05.BB0503,BB03.BB0303,BB05.BB0506,BA05.BA0501,BA05.BA0502,BA07.BA0703 as 期末结存数量,BA07.BA0704/case when BA07.BA0703=0 then 1 else BA07.BA0703 end as 期末平均成本,BA07.BA0704 as 期末总金额
36

from BA07 left join BB05 on BA07.BA0702=BB05.BB0501 left join BB03 on BB05.BB0505=BB03.BB0301 left join BA05 on BA07.BA0701=BA05.BA0501
37

'
38

+@SQL
39

INSERT INTO #A(goodsnumber,goodsname ,goodstype ,dept ,storageid,storage ,remaincount ,averageprice ,countprice)
42

EXEC sp_executesql @SQL,
43

N'
44

@maxGoodsCode varchar(10),
45

@minGoodsCode varchar(10),
46

@StartTime datetime,
47

@EndTime datetime,
48

@maxStorage varchar(10),
49

@minStorage varchar(10)
50

',
51

@maxGoodsCode, --商品编码大
52

@minGoodsCode, --商品编码小
53

@StartTime, --日期大
54

@EndTime, --日期小
55

@maxStorage, --仓库编码大
56

@minStorage --仓库编码小
57

--向临时表中加入incount列的值
61

declare @MinGoods varchar(10),@MaxGoods varchar(10),@MinStorageid varchar(10),@MaxStorageid varchar(10),@flag varchar(36),@incount decimal(10,2)
62

select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
63

while(@MinGoods<=@MaxGoods)
64

Begin
65

select @MinStorageid=min(storageid),@MaxStorageid=max(storageid) from #A where goodsnumber=@MinGoods
67

while(@MinStorageid<=@MaxStorageid)
68

begin
69

select @flag=count(*) from #A where goodsnumber=@MinGoods and storageid=@MinStorageid
70

if(@flag<>'0')
71

begin
72

select @incount=sum(BA09.BA0904) from BA09 left join BA10 on BA09.BA0902=BA10.BA0902
73

where (BA10.BA1002='+') and (BA09.BB0501=@MinGoods) and (BA10.BA0501=@MinStorageid)
74

and (BA10.AA9902>=case when @StartTime<>'' then @StartTime else '1755-2-6' end )
75

and (BA10.AA9902<=case when @EndTime<>'' then @EndTime else '2599-6-7' end )
76

update #A set incount=@incount where goodsnumber=@MinGoods and storageid=@MinStorageid
78

end
79

select @MinStorageid=min(Storageid) from #A where goodsnumber=@MinGoods and storageid>@MinStorageid
80

end
81

select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
82

END
84

--向临时表中加入outcount列的值
85

select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
86

while(@MinGoods<=@MaxGoods)
87

Begin
88

select @MinStorageid=min(storageid),@MaxStorageid=max(storageid) from #A where goodsnumber=@MinGoods
90

while(@MinStorageid<=@MaxStorageid)
91

begin
92

select @flag=count(*) from #A where goodsnumber=@MinGoods and storageid=@MinStorageid
93

if(@flag<>'0')
94

begin
95

select @incount=sum(BA09.BA0904) from BA09 left join BA10 on BA09.BA0902=BA10.BA0902
96

where (BA10.BA1002='-') and (BA09.BB0501=@MinGoods) and (BA10.BA0501=@MinStorageid)
97

and (BA10.AA9902>=case when @StartTime<>'' then @StartTime else '1755-2-6' end )
98

and (BA10.AA9902<=case when @EndTime<>'' then @EndTime else '2599-6-7' end )
99

100

update #A set outcount=@incount where goodsnumber=@MinGoods and storageid=@MinStorageid
101

end
102

select @MinStorageid=min(Storageid) from #A where goodsnumber=@MinGoods and storageid>@MinStorageid
103

end
104

select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
105

106

END
107

108

--select storage,goodsnumber ,goodsname ,sheetdate ,sheetabout ,sheetnumber,outcount ,outprice ,outcountprice ,incount ,inprice ,incountprice from #A
109

--select * from #A
110

set @SQL='select * from #A'
111

exec(@SQL)
112

113

114

drop table #A
115

116

117

GO
118

转载于:https://www.cnblogs.com/davidathena/archive/2007/06/04/770912.html