yuejie.sql
CREATE PROCEDURE sp_testyuejie
@date1 datetime,
@date2 datetime
AS
SELECT isnull(tmp1.sumquantity,0) - isnull(tmp2.sumquantity,0) - isnull(tmp3.sumquantity,0) + isnull(tmp4.sumquantity,0) AS Expr1,TMP1.PARTNAME
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 and @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp1 left outer JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 and @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='1'AND
b.isexecute = 1 AND
b.billdate BETWEEN @date1 and @date2
GROUP BY a.partid
) tmp3 ON tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate BETWEEN @date1 and @date2
GROUP BY a.partid
) tmp4 ON tmp1.PARTID = tmp4.PARTID
GO
SELECT isnull(tmp1.sumquantity,0) - isnull(tmp2.sumquantity,0) - isnull(tmp3.sumquantity,0) + isnull(tmp4.sumquantity,0) AS Expr1,TMP1.PARTNAME
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-01' AND '2005-02-26')
GROUP BY a.PARTID, a.PARTNAME) tmp1 left outer JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-01' AND '2005-02-26')
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='1'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-26'
GROUP BY a.partid
) tmp3 ON tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-26'
GROUP BY a.partid
) tmp4 ON tmp1.PARTID = tmp4.PARTID
GO
CREATE PROCEDURE sp_stockSFC
@Bgntime datetime,
@Endtime datetime
AS
SELECT TOP 100 PERCENT texjc.id, texin.price, texjc.SORTTYPE, texjc.ownerid,
texin.depotcode, ISNULL(texout.领用实际, 0) AS 领用实际, ISNULL(texout.领用票据, 0)
AS 领用票据, ISNULL(texin.入库实际, 0) AS 入库实际, ISNULL(texin2.入库票据, 0)
AS 入库票据, texjc.结存数量, ISNULL(texrt.入库退货票据, 0) AS 入库退货票据,
ISNULL(texrt.入库退货实际, 0) AS 入库退货实际, ISNULL(texlt.领用退货实际, 0)
AS 领用退货实际, ISNULL(texlt2.领用退货票据, 0) AS 领用退货票据,
ISNULL(texin2.入库票据, 0) - ISNULL(texout.领用票据, 0) - ISNULL(texrt.入库退货票据,
0) + ISNULL(texlt2.领用退货票据, 0) AS 当前结存票据, ISNULL(texin.入库实际, 0)
- ISNULL(texout.领用实际, 0) - ISNULL(texrt.入库退货实际, 0)
+ ISNULL(texlt.领用退货实际, 0) AS 当前结存实际, 0 AS 期初票据, 0 AS 期初实际
FROM (
SELECT SUM(BILLQUANTITY) AS 结存票据, SUM(STOCKSUM) AS 结存金额,
SUM(STOCKQUANTITY) AS 结存数量, SORTTYPE, ownerid, code, name, id,
stockprice
FROM PARTINFO
GROUP BY SORTTYPE, ownerid, code, name, id, stockprice
)
texjc LEFT OUTER JOIN
(
SELECT isnull(SUM(a.quantity), 0) AS 领用退货实际,
isnull(SUM(a.totalsum), 0) AS 领用退货金额,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-28'
GROUP BY a.partid
) texlt ON
texjc.id = texlt.partid LEFT OUTER JOIN
(
SELECT isnull(SUM(a.quantity), 0) AS 领用退货票据,
isnull(SUM(a.totalsum), 0) AS 领用退货金额,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-28'
GROUP BY a.partid
) texlt2 ON
texjc.id = texlt2.partid LEFT OUTER JOIN
(
SELECT isnull(SUM(a.quantity), 0) AS 入库退货票据,
isnull(SUM(a.outputqry), 0) AS 入库退货实际,
isnull(SUM(a.totalsum), 0) AS 入库退货金额,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='1'AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-28'
GROUP BY a.partid
) texrt ON
texjc.id = texrt.partid LEFT OUTER JOIN
(
SELECT isnull(SUM(a.outputqry), 0) AS 领用实际,
isnull(SUM(a.quantity), 0) AS 领用票据,
isnull(SUM(a.totalsum), 0) AS 领用金额,
b.depotcode, a.partid
FROM partsoutputbody a, partsoutputhand b
WHERE b.billcode = b.billcode AND b.billdate BETWEEN'2005-02-01' AND '2005-02-28'
GROUP BY b.depotcode, a.partid
) texout ON texjc.id = texout.partid LEFT OUTER JOIN
(
SELECT isnull(SUM(a.quantity), 0) AS 入库实际,
isnull(SUM(a.totalsum), 0) AS 入库金额,
b.depotcode, a.partid,a.price
FROM partsinputbody a, partsinputhand b
WHERE a.billcode = b.billcode AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-28'
GROUP BY b.depotcode,a.partid, a.price
) texin ON
texjc.id = texin.partid LEFT OUTER JOIN
(
SELECT isnull(SUM(a.quantity), 0) AS 入库票据,
isnull(SUM(a.totalsum), 0) AS 入库金额,
b.depotcode, a.partid
FROM partsinputbody a, partsinputhand b
WHERE a.billcode = b.billcode AND
b.billdate BETWEEN '2005-02-01' AND '2005-02-28'
GROUP BY b.depotcode, a.partid
) texin2 ON texjc.id = texin2.partid
GO
实际,消耗,期初 ,name
select a.expr1,b.expr1,b.expr1+a.expr1,b.partname
from
(SELECT isnull(tmp1.sumquantity,0) - isnull(tmp2.sumquantity,0) - isnull(tmp3.sumquantity,0) + isnull(tmp4.sumquantity,0) AS Expr1,TMP1.PARTNAME,tmp1.partid
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-25' AND '2005-02-26')
GROUP BY a.PARTID, a.PARTNAME) tmp1 left outer JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-25' AND '2005-02-26')
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='1'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-25' AND '2005-02-26'
GROUP BY a.partid
) tmp3 ON tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate BETWEEN '2005-02-25' AND '2005-02-26'
GROUP BY a.partid
) tmp4 ON tmp1.PARTID = tmp4.PARTID) a
right outer join
(
SELECT isnull(tmp1.sumquantity,0) - isnull(tmp2.sumquantity,0) - isnull(tmp3.sumquantity,0) + isnull(tmp4.sumquantity,0) AS Expr1,TMP1.PARTNAME,tmp1.partid
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp1 left outer JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='1'AND
b.isexecute = 1 AND
b.billdate <= '2005-02-28'
GROUP BY a.partid
) tmp3 ON tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity,
a.partid
FROM reimbursebillbody a , reimbursebillhand b
WHERE a.billcode = b.billcode AND
b.reimbursetype='2'AND
b.isexecute = 1 AND
b.billdate <= '2005-02-28'
GROUP BY a.partid
) tmp4 ON tmp1.PARTID = tmp4.PARTID) b
on a.partid=b.partid
//ok
SELECT ISNULL(a.Expr1, 0) AS xh, ISNULL(b.Expr1, 0) AS jc, b.Expr1 - a.Expr1 AS qc,
b.PARTNAME,a.rk,a.rkt,a.ck,a.ckt
FROM (SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
isnull(tmp1.sumquantity,0) as rk,isnull(tmp2.sumquantity,0) as ck,
isnull(tmp3.sumquantity,0) as rkt,isnull(tmp4.sumquantity,0) as ckt,
TMP.PARTNAME, tmp.partid
FROM (SELECT a.PARTID, a.PARTNAME
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-27' AND '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp1 ON
tmp.partid = tmp1.partid LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN '2005-02-27' AND '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON
tmp.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate BETWEEN '2005-02-27' AND
'2005-02-28'
GROUP BY a.partid) tmp3 ON
tmp.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate BETWEEN '2005-02-27' AND
'2005-02-28'
GROUP BY a.partid) tmp4 ON tmp.PARTID = tmp4.PARTID)
a RIGHT OUTER JOIN
(SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
TMP1.PARTNAME, tmp1.partid
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp1 LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY)
AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= '2005-02-28')
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON
tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate <= '2005-02-28'
GROUP BY a.partid) tmp3 ON
tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate <= '2005-02-28'
GROUP BY a.partid) tmp4 ON tmp1.PARTID = tmp4.PARTID) b ON
a.partid = b.partid
//
{
CREATE PROCEDURE sp_stockSFC
@date1 datetime,
@date2 datetime
AS
select * into tmpkk from
(
SELECT ISNULL(a.Expr1, 0) AS xh, ISNULL(b.Expr1, 0) AS jc, b.Expr1 - a.Expr1 AS qc,
b.PARTNAME,b.partid,a.rk,a.rkt,a.ck,a.ckt
FROM (SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
isnull(tmp1.sumquantity,0) as rk,isnull(tmp2.sumquantity,0) as ck,
isnull(tmp3.sumquantity,0) as rkt,isnull(tmp4.sumquantity,0) as ckt,
TMP.PARTNAME, tmp.partid
FROM (SELECT a.PARTID, a.PARTNAME
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 and @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp1 ON
tmp.partid = tmp1.partid LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE BETWEEN @date1 and @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON
tmp.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate BETWEEN @date1 AND @date2
GROUP BY a.partid) tmp3 ON
tmp.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate BETWEEN @date1 AND @date2
GROUP BY a.partid) tmp4 ON tmp.PARTID = tmp4.PARTID)
a RIGHT OUTER JOIN
(SELECT isnull(tmp1.sumquantity, 0) - isnull(tmp2.sumquantity, 0)
- isnull(tmp3.sumquantity, 0) + isnull(tmp4.sumquantity, 0) AS Expr1,
TMP1.PARTNAME, tmp1.partid
FROM (SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY) AS sumquantity
FROM dbo.PARTSINPUTBODY a LEFT OUTER JOIN
dbo.PARTSINPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp1 LEFT OUTER JOIN
(SELECT a.PARTID, a.PARTNAME, SUM(a.QUANTITY)
AS sumquantity
FROM dbo.PARTSOUTPUTBODY a LEFT OUTER JOIN
dbo.PARTSOUTPUTHAND b ON a.BILLCODE = b.BILLCODE
WHERE (b.BILLDATE <= @date2)
GROUP BY a.PARTID, a.PARTNAME) tmp2 ON
tmp1.PARTID = tmp2.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '1' AND
b.isexecute = 1 AND b.billdate <= @date2
GROUP BY a.partid) tmp3 ON
tmp1.PARTID = tmp3.PARTID LEFT OUTER JOIN
(SELECT isnull(SUM(a.quantity), 0) AS sumquantity, a.partid
FROM reimbursebillbody a, reimbursebillhand b
WHERE a.billcode = b.billcode AND b.reimbursetype = '2' AND
b.isexecute = 1 AND b.billdate <= @date2
GROUP BY a.partid) tmp4 ON tmp1.PARTID = tmp4.PARTID) b ON
a.partid = b.partid
) tmmm
GO
}
if exists (select * from sysobjects where id = object_id(N'[tmpkk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [tmpkk]
DECLARE @RC int
DECLARE @date1 datetime
DECLARE @date2 datetime
SELECT @date1 = '2005-02-25'
SELECT @date2 = '2005-02-28'
EXEC @RC = [wy].[dbo].[sp_StockSFC] @date1, @date2
DECLARE @PrnLine nvarchar(4000)
PRINT '存储过程: wy.dbo.sp_StockSFC'
SELECT @PrnLine = ' 返回代码 = ' + CONVERT(nvarchar, @RC)
PRINT @PrnLine
select * from tmpkk
