CREATE PROCEDURE sp_testyuejie @ date1 datetime, @ date2 datetime ASSELECT isnull (tmp1.sumquantity, 0) - isnull (tmp2.sumquantity, 0) - isnull (tmp3.sumquantity, 0) isnull (tmp4.sumquantity, 0) AS Expr1, TMP1 .PARTNAMEFROM (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 (SUM (A.quantity), 0 AS SUMQUANTITY, A.Partid from ReimbürseBillBody A, ReimbursebillHand B Where A.Billcode = B.Billcode A Nd 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 (SUM (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.PartNameFrom (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 (A.quantity), 0) As Sumquantity, A.Partid from ReimbursebillBody A, ReimbursebillHand B Where A.Billcode = B.Billcode and B.ReimbursType = '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.PARTIDGO
CREATE PROCEDURE sp_stockSFC @Bgntime datetime, @Endtime datetimeASSELECT TOP 100 PERCENT texjc.id, texin.price, texjc.SORTTYPE, texjc.ownerid, texin.depotcode, ISNULL (texout. Recipients actual, 0) AS actual recipients, ISNULL ( TEXOUT. Applying ticket, 0) AS acquisition notes, isnull (TEXIN. 入 库 实,, 0) AS Introduction Realism, isnull (Texin2. Introduction Bill, 0) AS Branch Bill, TEXJC. Save Quantity, ISNULL TEXRT. 入 库 退, 0) AS Introduction Return Bill, ISNULL (TEXRT. Returns Renewal, 0) AS Intrinsic Return Real, ISNULL (TEXLT. Acquisition Return Real, 0) AS Acquisition Return Real, ISNULL (TEXLT2. Requirement return bill, 0) AS acquisition return bill, isnull (Texin2. Introduction Bill, 0) - Isnull (Texout. Acquisition Bill, 0) - Isnull (TEXRT. 入 库 退 退, 0) ISNULL (TEXLT2. Requirements Return Bill, 0) AS Current Save Notes, ISNULL (TEXIN. Introduction Terminal, 0) - ISNULL (TEXOUT. Using Real, 0) - Isnull (Texrt. Initial Return Real, 0) ISNULL (TEXLT. Recent return reality, 0) AS Current Survival Real, 0 AS period early bill, 0 AS stage actual FROM (
SELECT SUM (BILLQUANTITY) AS balances bills, SUM (STOCKSUM) AS balance amount, the number of AS balance SUM (STOCKQUANTITY), 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 Used Return Real, ISNULL (SUM (A. TOTALSUM), 0) AS Used Return Return, A.Partid from ReimbürsbillBody 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 Used Return Bill, ISNULL (SUM (a.totalsum), 0) AS Used Return, A.Partid from ReimbusebillBody 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 (A.quantity), 0) AS Introduction Return Bill, ISNULL (SUM (A.outputQry), 0) AS Infinitive Return Realistic, ISNULL (SUM (A. TOTALSUM), 0) AS Intrinsic Return A.Partid from ReimbursebillBody A, ReimbursebillHand B Where a.billcode = B.Billcode and b.reimburStype = '1'and B.BILDATETYEN' 2005-02-01 'and' 2005-02-28 'Group by A .partid
) Text on texjc.id = Texrt.Partid Left Outer Join (SUM (a.outputqry), 0) AS Used Real, ISNULL (SUM (A.quantity), 0) AS Acquisition Bill, Isnull (SUM (a.totalsum), 0) AS, B. DEPOTCODE, A.PARTID from PartsoutPutbody a, partsoutputhand b Where b.Billcode = B.Billcode and B.BILDATE 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 Intrinsically, ISNULL (SUM (A. TOTALSUM), 0) AS Intrinsically, 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 Branch Bill, ISNULL (SUM (a.totalsum), 0) AS Branch Amount, 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
Real, consumption, early days, name
Select a.EXPR1, B.EXPR1, B.EXPR1 A.EXPR1, B.PartNameFrom (Select Isnull (Tmp1.Sumquantity, 0) - Isnull (Tmp2.Sumquantity, 0) - Isnull (Tmp3.Sumquantity, 0) Isnull (tmp4.sumquantity, 0) AS Expr1, TMP1.PARTNAME, tmp1.partidFROM (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) aright outer join (SELECT isnull (tmp1.sumquantity, 0) - isnull (tmp2.sumquantity, 0) - isnull (tmp3.sumquantity, 0) isnull (tmp4.sumquantity, 0) AS Expr1, TMP1.PARTNAME , tmp1.partidFROM (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 outhand 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 (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 . cktfrom (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 (Bildate <= '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' 2 005-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 (SUM (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 setr 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 l d dbo.partsoutputHand B on a.billcode =
B.Billcode WHERE (Bildate <= '2005-02-28') Group by a.partid, a.partname) TMP2 on Tmp1.PartId = Tmp2.Partid Left Outer Join (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 @date 1 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.cktfrom (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.SUMQUANTINAME, 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 @date 2) Group by a.partid, a.partname) TMP1 ON TMP.PARTID = TMP1.PARTID LEFT OUTER JOIN (SELECT A.PARTID, A.PartName, Sum (A.quantity) AS SUMQUANTINTITY 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 outr (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.PARTSOUTSOUTPUTHAND B on A.BILLCODE = B.BILLCODE WHERE (B.BILDATE <= @ 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.Reimburstype = '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) TMMMGO
}
IF exists (select * from sysobjects where id = Object_id (n '[tmpkk)) And ObjectProperty (ID, n'susertable') = 1) DROP TABLE [TMPKK]
Declare @rc intDeclare @ Date1 DateTimedeclare @ Date2 DateTimeSelect @ Date1 = '2005-02-25 #select @ Date2 =' 2005-02-28'Exec @rc = [wy]. [Dbo]. [Sp_stocksfc] @ date1, @ Date2Declare @PRNLINE NVARCHAR (4000) Print 'stored procedure: wy.dbo.sp_stocksfc'select @prnline =' Return code = ' convert (nvarchar, @rc) print @PRNLINE
SELECT * from TMPKK
