22,210
社区成员
发帖
与我相关
我的任务
分享
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop FUNCTION [R_I_INOUTSTOR]
Go
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case ISIND when 1 then QT else -QT end),0,0,
sum(case ISIND when 1 then QT else -QT end)
FROM [IO_STORE]
WHERE [BD]<@FROMDATE
GROUP BY STORE,GOODS
INSERT @TempTable
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,BD,BC,GOODS
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT
FROM @TempTable t
UPDATE @TempTable SET
CQT=SQT+IQT-OQT
WHERE CQT=0
RETURN
END
GO
--为了方便查询,再创建一个函数
If not object_id('[R_I_STOCK]') is null
Drop FUNCTION [R_I_STOCK]
Go
CREATE FUNCTION [R_I_STOCK](@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS TABLE
AS
RETURN(
select TOP 100 PERCENT STORE,BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR(@FROMDATE,@TODATE)
order by STORE,GOODSID,ID
)
GO
--调用查询
SELECT * FROM [R_I_STOCK]('2009-07-03','2009-07-28')
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 00:00:00.000 期初 16081D 350 0 0 350
成品仓 2009-07-03 00:00:00.000 期初 16081D 50 0 0 50
半成品仓 2009-07-03 00:00:00.000 期初 16082A 50 0 0 50
半成品仓 2009-07-05 00:00:00.000 090705001 16081D 350 0 30 320
半成品仓 2009-07-06 00:00:00.000 090706002 16082A 50 0 20 30
半成品仓 2009-07-15 00:00:00.000 090715007 16083K 0 80 0 80
半成品仓 2009-07-18 00:00:00.000 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 00:00:00.000 090718004 16081D 410 200 0 610
半成品仓 2009-07-18 00:00:00.000 090718006 16082A 30 0 20 10
半成品仓 2009-07-23 00:00:00.000 090723007 16083K 80 0 35 45
成品仓 2009-07-03 00:00:00.000 090703008 16081D 50 77 0 127
成品仓 2009-07-28 00:00:00.000 090728026 16081D 127 0 20 107
*/
--调用查询
select STORE,BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR('2009-07-18','2009-07-28')
order by STORE,GOODSID,ID
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-18 00:00:00.000 期初 16081D 320 0 0 320
半成品仓 2009-07-18 00:00:00.000 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 00:00:00.000 090718004 16081D 410 200 0 610
半成品仓 2009-07-18 00:00:00.000 期初 16082A 30 0 0 30
半成品仓 2009-07-18 00:00:00.000 090718006 16082A 30 0 20 10
半成品仓 2009-07-18 00:00:00.000 期初 16083K 80 0 0 80
半成品仓 2009-07-23 00:00:00.000 090723007 16083K 80 0 35 45
成品仓 2009-07-18 00:00:00.000 期初 16081D 127 0 0 127
成品仓 2009-07-28 00:00:00.000 090728026 16081D 127 0 20 107
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop FUNCTION [R_I_INOUTSTOR]
Go
CREATE FUNCTION R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME)
RETURNS @TempTable TABLE
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case ISIND when 1 then QT else -QT end),0,0,
sum(case ISIND when 1 then QT else -QT end)
FROM [IO_STORE]
WHERE [BD]<@FROMDATE
GROUP BY STORE,GOODS
INSERT @TempTable
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,BD,BC,GOODS
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT
FROM @TempTable t
UPDATE @TempTable SET
CQT=SQT+IQT-OQT
WHERE CQT=0
RETURN
END
GO
--调用查询
select STORE,BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR('2009-07-03','2009-07-28')
order by STORE,GOODSID,ID
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 00:00:00.000 期初 16081D 350 0 0 350
半成品仓 2009-07-05 00:00:00.000 090705001 16081D 350 0 30 320
半成品仓 2009-07-18 00:00:00.000 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 00:00:00.000 090718004 16081D 410 200 0 610
半成品仓 2009-07-03 00:00:00.000 期初 16082A 50 0 0 50
半成品仓 2009-07-06 00:00:00.000 090706002 16082A 50 0 20 30
半成品仓 2009-07-18 00:00:00.000 090718006 16082A 30 0 20 10
半成品仓 2009-07-15 00:00:00.000 090715007 16083K 0 80 0 80
半成品仓 2009-07-23 00:00:00.000 090723007 16083K 80 0 35 45
成品仓 2009-07-03 00:00:00.000 期初 16081D 50 0 0 50
成品仓 2009-07-03 00:00:00.000 090703008 16081D 50 77 0 127
成品仓 2009-07-28 00:00:00.000 090728026 16081D 127 0 20 107
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop FUNCTION [R_I_INOUTSTOR]
Go
CREATE FUNCTION R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@GOODSID varchar(80)
)
RETURNS @TempTable TABLE
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
AS
BEGIN
IF @GOODSID='' or @GOODSID is null
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case when BD<@FROMDATE then case ISIND when 1 then QT else -QT end else 0 end),0,0,
sum(case when BD<@FROMDATE then case ISIND when 1 then QT else -QT end else 0 end)
FROM [IO_STORE]
GROUP BY STORE,GOODS
UNION ALL
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,BD,BC,GOODS
ELSE
INSERT @TempTable
SELECT STORE,@FROMDATE,'期初',GOODS,
sum(case when BD<@FROMDATE then case ISIND when 1 then QT else -QT end else 0 end),0,0,
sum(case when BD<@FROMDATE then case ISIND when 1 then QT else -QT end else 0 end)
FROM [IO_STORE]
WHERE GOODS=@GOODSID
GROUP BY STORE,GOODS
UNION ALL
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
AND GOODS=@GOODSID
GROUP BY STORE,BD,BC,GOODS
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT,
CQT=CASE WHEN SQT=0 THEN (SELECT SUM(CQT+IQT-OQT) from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END
FROM @TempTable t
-- UPDATE @TempTable SET
-- CQT=SQT+IQT-OQT
-- WHERE CQT=0
INSERT @TempTable
SELECT STORE,@TODATE,'结存',GOODSID,
(select SQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID and BC='期初'),sum(IQT),sum(OQT),
(select top 1 CQT from @TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC)
FROM @TempTable t
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,GOODSID
RETURN
END
GO
--调用查询所有物品
select STORE,convert(varchar(10),BD,23) BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR('2009-07-03','2009-07-28','')
order by STORE,GOODSID,ID
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 期初 16081D 350 0 0 350
半成品仓 2009-07-05 090705001 16081D 350 0 30 320
半成品仓 2009-07-18 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 090718004 16081D 410 200 0 610
半成品仓 2009-07-28 结存 16081D 350 290 30 610
半成品仓 2009-07-03 期初 16082A 50 0 0 50
半成品仓 2009-07-06 090706002 16082A 50 0 20 30
半成品仓 2009-07-18 090718006 16082A 30 0 20 10
半成品仓 2009-07-28 结存 16082A 50 0 40 10
半成品仓 2009-07-03 期初 16083K 0 0 0 0
半成品仓 2009-07-15 090715007 16083K 0 80 0 80
半成品仓 2009-07-23 090723007 16083K 80 0 35 45
半成品仓 2009-07-28 结存 16083K 0 80 35 45
成品仓 2009-07-03 期初 16081D 50 0 0 50
成品仓 2009-07-03 090703008 16081D 50 77 0 127
成品仓 2009-07-28 090728026 16081D 127 0 20 107
成品仓 2009-07-28 结存 16081D 50 77 20 107
*/
--查询指定物品
select STORE,convert(varchar(10),BD,23) BD,BC,GOODSID,SQT,IQT,OQT,CQT
from R_I_INOUTSTOR('2009-07-03','2009-07-28','16083K')
order by STORE,GOODSID,ID
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 期初 16083K 0 0 0 0
半成品仓 2009-07-15 090715007 16083K 0 80 0 80
半成品仓 2009-07-23 090723007 16083K 80 0 35 45
半成品仓 2009-07-28 结存 16083K 0 80 35 45
*/
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop PROC [R_I_INOUTSTOR]
Go
CREATE PROC R_I_INOUTSTOR
(
@FROMDATE DATETIME,
@TODATE DATETIME,
@GOODSID VARCHAR(80)=''
)
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #TempTable
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
declare @s1 varchar(8000),@s2 varchar(8000)
set @s1='
INSERT #TempTable
SELECT STORE,'''+convert(varchar,@FROMDATE,23)+''',''期初'',GOODS,
sum(CASE WHEN BD<'''+convert(varchar,@FROMDATE,23)+''' THEN CASE WHEN ISIND = 1 then ISNULL(QT,0) else ISNULL(-QT,0) end ELSE 0 END),0,0,
sum(CASE WHEN BD<'''+convert(varchar,@FROMDATE,23)+''' THEN CASE WHEN ISIND = 1 then ISNULL(QT,0) else ISNULL(-QT,0) end ELSE 0 END)
FROM [IO_STORE]
WHERE 1=1 '
set @s2='
GROUP BY STORE,GOODS
UNION ALL
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN '''+convert(varchar,@FROMDATE,23)+''' AND '''+convert(varchar,@TODATE,23)+''''
if @GOODSID=''
EXEC(@s1+@s2+' GROUP BY STORE,BD,BC,GOODS')
else
EXEC (@s1+' AND GOODS='''+@GOODSID+''''+@s2+' AND GOODS='''+@GOODSID+''' GROUP BY STORE,BD,BC,GOODS')
CREATE INDEX temp_index ON #TempTable(STORE,GOODSID,ID)
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (
SELECT SUM(CQT+IQT-OQT)
FROM #TempTable
WHERE STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT,
CQT=CASE WHEN SQT=0 THEN (
SELECT SUM(CQT+IQT-OQT)
FROM #TempTable
WHERE STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END
FROM #TempTable t
-- UPDATE #TempTable SET
-- CQT=SQT+IQT-OQT
-- WHERE CQT=0
INSERT #TempTable
SELECT STORE,
@TODATE,'结存',GOODSID,
ISNULL((select SQT from #TempTable where STORE=t.STORE AND GOODSID=t.GOODSID and BC='期初'),0),sum(IQT),sum(OQT),
(select top 1 CQT from #TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC)
FROM #TempTable t
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,GOODSID
SET NOCOUNT OFF
SELECT STORE,convert(varchar(10),BD,23) BD,BC,GOODSID,SQT,IQT,OQT,CQT
FROM #TempTable
ORDER BY STORE,GOODSID,ID
DROP TABLE #TempTable
END
GO
--调用查询
--查询全部:
EXEC R_I_INOUTSTOR '2009-07-03','2009-07-28'
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 期初 16081D 350 0 0 350
半成品仓 2009-07-05 090705001 16081D 350 0 30 320
半成品仓 2009-07-18 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 090718004 16081D 410 200 0 610
半成品仓 2009-07-28 结存 16081D 350 290 30 610
半成品仓 2009-07-03 期初 16082A 50 0 0 50
半成品仓 2009-07-06 090706002 16082A 50 0 20 30
半成品仓 2009-07-18 090718006 16082A 30 0 20 10
半成品仓 2009-07-28 结存 16082A 50 0 40 10
半成品仓 2009-07-03 期初 16083K 0 0 0 0
半成品仓 2009-07-15 090715007 16083K 0 80 0 80
半成品仓 2009-07-23 090723007 16083K 80 0 35 45
半成品仓 2009-07-28 结存 16083K 0 80 35 45
成品仓 2009-07-03 期初 16081D 50 0 0 50
成品仓 2009-07-03 090703008 16081D 50 77 0 127
成品仓 2009-07-28 090728026 16081D 127 0 20 107
成品仓 2009-07-28 结存 16081D 50 77 20 107
*/
--查询指定物品:
EXEC R_I_INOUTSTOR '2009-07-03','2009-07-28','16083K'
/*
STORE BD BC GOODSID SQT IQT OQT CQT
半成品仓 2009-07-03 期初 16083K 0 0 0 0
半成品仓 2009-07-15 090715007 16083K 0 80 0 80
半成品仓 2009-07-23 090723007 16083K 80 0 35 45
半成品仓 2009-07-28 结存 16083K 0 80 35 45
*/
完善一下。/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-08-31 11:00:29
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:IO_STORE
If not object_id('[IO_STORE]') is null
Drop table [IO_STORE]
Go
Create table [IO_STORE]([STORE] nvarchar(4),[BD] Datetime,[BC] nvarchar(10),[GOODS] nvarchar(6),[QT] int,[ISIND] int)
Insert [IO_STORE]
Select N'半成品仓','2009-07-01',N'090701001',N'16081D',300,1 union all
Select N'半成品仓','2009-07-02',N'090702003',N'16081D',50,1 union all
Select N'半成品仓','2009-07-18',N'090718003',N'16081D',90,1 union all
Select N'半成品仓','2009-07-18',N'090718004',N'16081D',200,1 union all
Select N'半成品仓','2009-07-01',N'090701002',N'16082A',50,1 union all
Select N'半成品仓','2009-07-15',N'090715007',N'16083K',80,1 union all
Select N'成品仓','2009-07-02',N'090702007',N'16081D',50,1 union all
Select N'成品仓','2009-07-03',N'090703008',N'16081D',77,1 union all
Select N'半成品仓','2009-07-05',N'090705001',N'16081D',30,0 union all
Select N'半成品仓','2009-07-06',N'090706002',N'16082A',20,0 union all
Select N'半成品仓','2009-07-18',N'090718006',N'16082A',20,0 union all
Select N'半成品仓','2009-07-23',N'090723007',N'16083K',35,0 union all
Select N'成品仓','2009-07-28',N'090728026',N'16081D',20,0
Go
--Select * from [IO_STORE]
-->SQL查询如下:
If not object_id('[R_I_INOUTSTOR]') is null
Drop PROC [R_I_INOUTSTOR]
Go
CREATE PROC R_I_INOUTSTOR(@FROMDATE DATETIME, @TODATE DATETIME,@GOODSID VARCHAR(80))
AS
SET NOCOUNT ON
BEGIN
CREATE TABLE #TempTable
(
ID int identity,
STORE VARCHAR(80), --仓库
BD DATETIME, --日期
BC VARCHAR(80),--单号
GOODSID VARCHAR(80) ,--货品
SQT FLOAT , --期初
IQT FLOAT , --收入
OQT FLOAT ,--发出
CQT FLOAT --结存
)
declare @s1 varchar(8000),@s2 varchar(8000)
set @s1='
INSERT #TempTable
SELECT STORE,'''+convert(varchar,@FROMDATE,23)+''',''期初'',GOODS,
sum(case ISIND when 1 then QT else -QT end),0,0,
sum(case ISIND when 1 then QT else -QT end)
FROM [IO_STORE]
WHERE [BD]<'''+convert(varchar,@FROMDATE,23)+''''
set @s2='
GROUP BY STORE,GOODS
UNION ALL
SELECT STORE,BD,BC,GOODS,0,
SUM(case ISIND WHEN 1 then QT else 0 end) IQT,
SUM(case ISIND WHEN 0 then QT else 0 end) OQT,
0
FROM [IO_STORE]
WHERE [BD] BETWEEN '''+convert(varchar,@FROMDATE,23)+''' AND '''+convert(varchar,@TODATE,23)+''''
if @GOODSID is null or @GOODSID=''
EXEC(@s1+@s2+' GROUP BY STORE,BD,BC,GOODS')
else
EXEC (@s1+' AND GOODS='''+@GOODSID+''''+@s2+' AND GOODS='''+@GOODSID+''' GROUP BY STORE,BD,BC,GOODS')
CREATE INDEX temp_index ON #TempTable(STORE,GOODSID,ID)
UPDATE t SET
SQT=CASE WHEN SQT=0 THEN (
SELECT SUM(CQT+IQT-OQT)
FROM #TempTable
WHERE STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END-IQT+OQT,
CQT=CASE WHEN SQT=0 THEN (
SELECT SUM(CQT+IQT-OQT)
FROM #TempTable
WHERE STORE=t.STORE AND GOODSID=t.GOODSID AND ID<=t.ID)
ELSE SQT
END
FROM #TempTable t
-- UPDATE #TempTable SET
-- CQT=SQT+IQT-OQT
-- WHERE CQT=0
INSERT #TempTable
SELECT STORE,
@TODATE,'结存',GOODSID,
ISNULL((select SQT from #TempTable where STORE=t.STORE AND GOODSID=t.GOODSID and BC='期初'),0),sum(IQT),sum(OQT),
(select top 1 CQT from #TempTable where STORE=t.STORE AND GOODSID=t.GOODSID order by ID DESC)
FROM #TempTable t
WHERE [BD] BETWEEN @FROMDATE AND @TODATE
GROUP BY STORE,GOODSID
SET NOCOUNT OFF
SELECT STORE,convert(varchar(10),BD,23) BD,BC,GOODSID,SQT,IQT,OQT,CQT
FROM #TempTable
ORDER BY STORE,GOODSID,ID
DROP TABLE #TempTable
END
GO
--调用查询
EXEC R_I_INOUTSTOR '2009-07-03','2009-07-28','16081D'
/*
半成品仓 2009-07-03 期初 16081D 350 0 0 350
半成品仓 2009-07-05 090705001 16081D 350 0 30 320
半成品仓 2009-07-18 090718003 16081D 320 90 0 410
半成品仓 2009-07-18 090718004 16081D 410 200 0 610
半成品仓 2009-07-28 结存 16081D 350 290 30 610
成品仓 2009-07-03 期初 16081D 50 0 0 50
成品仓 2009-07-03 090703008 16081D 50 77 0 127
成品仓 2009-07-28 090728026 16081D 127 0 20 107
成品仓 2009-07-28 结存 16081D 50 77 20 107
*/