//input 明细
SELECT PARTSINPUTBODY.BILLCODE, PARTSINPUTBODY.PARTNAME,
PARTSINPUTBODY.PARTCODE, PARTSINPUTHAND.PROVIDER,
PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM,
PARTSINPUTBODY.QUANTITY
FROM PARTSINPUTHAND INNER JOIN
PARTSINPUTBODY ON
PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01002%') AND
(PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')
//output 明细
SELECT PARTSOUTPUTHAND.BILLCODE, PARTSOUTPUTHAND.CLIENTNAME,
PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTBODY.TOTALSUM
FROM PARTSOUTPUTBODY INNER JOIN
PARTSOUTPUTHAND ON
PARTSOUTPUTBODY.BILLCODE = PARTSOUTPUTHAND.BILLCODE
//********************
//小计 某商品的金额INPUT
SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS Expr1,
PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
FROM PARTSINPUTBODY CROSS JOIN
PARTSINPUTHAND
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
GROUP BY PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
//小计 某 类 商品的金额(INPUT)
SELECT SUM(E) AS Expr1
FROM (SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS E,
PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
FROM PARTSINPUTBODY CROSS JOIN
PARTSINPUTHAND
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
GROUP BY PARTSINPUTBODY.PARTNAME,
PARTSINPUTBODY.PARTCODE) DERIVEDTBL
//*******************************
//小计 某商品的金额(OUTPUT)
SELECT SUM(PARTSOUTPUTBODY.TOTALSUM) AS Expr1,
PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE
FROM PARTSOUTPUTBODY CROSS JOIN
PARTSOUTPUTHAND
where PARTSOUTPUTBODY.PARTCODE like '01%'
GROUP BY PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE
//小计 某 类 商品的金额(OUTPUT)
SELECT SUM(E) AS Expr1
FROM (SELECT SUM(PARTSINPUTBODY.TOTALSUM) AS E,
PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE
FROM PARTSINPUTBODY CROSS JOIN
PARTSINPUTHAND
WHERE (PARTSINPUTBODY.PARTCODE LIKE '01%')
GROUP BY PARTSINPUTBODY.PARTNAME,
PARTSINPUTBODY.PARTCODE) DERIVEDTBL
//****************************************************
WHERE (onlyid NOT IN
(SELECT Onlyid
FROM customer))
private
{ Private declarations }
// FId:string;
public
{ Public declarations }
property Id read Fid write Fid ;
CREATE PROCEDURE sp_operator
@name varchar(30),
@Department varchar(10)
AS
select *
from operator
where name=@name and Department=@Department
Parameters.CreateParameter(Name,DataType,Direction,Size,Value)
tmpsp.Parameters.CreateParameter('@jgfl',ftstring,pdinput,12,EditText);
par.value
pre
execproc
//ParamCheck := False;
Parameters.Clear;
//Parameters.createParameter('@BgnTime',ftdate,pdinput,10,now);
//Parameters.createParameter('@EndTime',ftdate,pdinput,10,now);
with MyDM.ADOSP do
begin
ProcedureName := 'sp_spaceused'; // 设置存储过程名
Close; // 关闭以前的数据集
Parameters.Clear; // 清空以前的参数
Parameters.CreateParameter('@objname',ftString,pdInput,100,'table1'); //生成新的参数
Open;// 或者ExecProc,不返回数据集
end;
错误:parameter 无法从子选择的查询语句中获得 在准备命令前 请设置参数信息
function TFrmQueryX.getinfoname(id:string):string;
var
TmpQuery:Tadoquery;
sT:string;
begin
TmpQuery:=Tadoquery.Create(self);
with TmpQuery do
begin
close;
ConnectionString:=OceanQueryXpartsinput.ConnectionString;
sql.Clear;
sql.Add('select name from partinfo where code='+QuotedStr(FId));
open ;//exe error
sT:=fieldbyname('name').Value;
free;
end;
Result:=sT;
end;
库存统计表
序号 项目名称 上月结存 本月入库 本月领用 本月库存
上月结存=上月库存量-上月领用量 [月份不同]
本月库存=本月入库-本月领用
假设 现在是 12月 商品名是A
上月结存 应该是 11月份的结存
select partinputbody.quantity
SELECT PARTSINPUTBODY.PARTNAME AS 商品名称,
PARTSINPUTBODY.PARTCODE AS 商品编码, PARTSINPUTHAND.PROVIDER,
PARTSINPUTHAND.BILLDATE, PARTSINPUTHAND.TOTALSUM,
PARTSINPUTBODY.QUANTITY AS 十一月入库量
FROM PARTSINPUTHAND INNER JOIN
PARTSINPUTBODY ON
PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')
SELECT PARTSOUTPUTBODY.PARTNAME AS 商品名称,
PARTSOUTPUTBODY.PARTCODE AS 商品编码,
PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
PARTSOUTPUTBODY.QUANTITY AS 十一月领用量,
PARTSOUTPUTHAND.CLIENTNAME
FROM PARTSOUTPUTHAND INNER JOIN
PARTSOUTPUTBODY ON
PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
WHERE (PARTSOUTPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31')
SELECT VIEW1.十一月入库量 - VIEW2.十一月领用量 AS 十一月结存,
VIEW2.十一月领用量, VIEW1.十一月入库量
FROM VIEW1 INNER JOIN
VIEW2 ON VIEW1.十一月入库量 > VIEW2.十一月领用量
//***************
SELECT a.入库量 - b.领用量 AS 本月结存, b.领用量,
a.入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
PARTSINPUTHAND.TOTALSUM,
PARTSINPUTBODY.QUANTITY AS 入库量
FROM PARTSINPUTHAND INNER JOIN
PARTSINPUTBODY ON
PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @MonthDateBegin AND
@MonthDateEnd)) a CROSS JOIN
(SELECT PARTSOUTPUTBODY.PARTNAME,
PARTSOUTPUTBODY.PARTCODE, PARTSOUTPUTHAND.BILLDATE,
PARTSOUTPUTHAND.TOTALSUM,
PARTSOUTPUTBODY.QUANTITY AS 领用量,
PARTSOUTPUTHAND.CLIENTNAME
FROM PARTSOUTPUTHAND INNER JOIN
PARTSOUTPUTBODY ON
PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN @MonthDateBegin AND
@MonthDateEnd)) b
WHERE (a.PARTCODE = '01001')
某商品或某类商品月 统计 含每次操作数量
SELECT a.入库量 - b.领用量 AS 本月结存, b.领用量, a.入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
PARTSINPUTHAND.TOTALSUM,
PARTSINPUTBODY.QUANTITY AS 入库量
FROM PARTSINPUTHAND INNER JOIN
PARTSINPUTBODY ON
PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
a INNER JOIN
(SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
PARTSOUTPUTBODY.QUANTITY AS 领用量,
PARTSOUTPUTHAND.CLIENTNAME
FROM PARTSOUTPUTHAND INNER JOIN
PARTSOUTPUTBODY ON
PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
WHERE (PARTSoutPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
b ON a.入库量 > b.领用量
WHERE (a.PARTCODE = '01001')
//某商品或某类商品月 统计小计
SELECT a.PARTNAME, SUM(a.入库量 - b.领用量) AS 本月结存, SUM(b.领用量) AS 本月领用量,
SUM(a.入库量) AS 本月入库量
FROM (SELECT PARTSINPUTBODY.PARTNAME, PARTSINPUTBODY.PARTCODE,
PARTSINPUTHAND.PROVIDER, PARTSINPUTHAND.BILLDATE,
PARTSINPUTHAND.TOTALSUM,
PARTSINPUTBODY.QUANTITY AS 入库量
FROM PARTSINPUTHAND INNER JOIN
PARTSINPUTBODY ON
PARTSINPUTHAND.BILLCODE = PARTSINPUTBODY.BILLCODE
WHERE (PARTSINPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
a INNER JOIN
(SELECT PARTSOUTPUTBODY.PARTNAME, PARTSOUTPUTBODY.PARTCODE,
PARTSOUTPUTHAND.BILLDATE, PARTSOUTPUTHAND.TOTALSUM,
PARTSOUTPUTBODY.QUANTITY AS 领用量,
PARTSOUTPUTHAND.CLIENTNAME
FROM PARTSOUTPUTHAND INNER JOIN
PARTSOUTPUTBODY ON
PARTSOUTPUTHAND.BILLCODE = PARTSOUTPUTBODY.BILLCODE
WHERE (PARTSoutPUTHAND.BILLDATE BETWEEN '2004-12-01' AND '2004-12-31'))
b ON a.入库量 > b.领用量
WHERE (a.PARTCODE LIKE '01001%')
GROUP BY a.PARTNAME
//***************
引用 fastreport 设计器
uses
FR_Class, FR_DSet, FR_DBSet, Db, DBTables, FR_Chart,FR_Desgn;
.
frReport1.DesignReport ;
//某商品 当前获得审批 但是 没有入库
SELECT PARTCODE, PARTNAME
FROM dbo.STOCKBILLBODY
WHERE (PARTCODE NOT IN
(SELECT partcode
FROM partsinputbody INNER JOIN
partsinputhand ON partsinputbody.billcode = partsinputhand.billcode
WHERE partsinputhand.billdate BETWEEN '2004-12-01' AND '2004-12-31'))
//某商品 当前获得审批 库存有/库存没有 没有领用
SELECT PARTCODE, PARTNAME
FROM dbo.STOCKBILLBODY
WHERE (PARTCODE NOT IN
(SELECT partcode
FROM partsoutputbody INNER JOIN
partsoutputhand ON partsoutputbody.billcode = partsoutputhand.billcode
WHERE partsoutputhand.billdate BETWEEN '2004-12-01' AND '2004-12-31'))
//(_'
--0-09000000000000000000000000000000000000000000000000000000000000000000000000
---- 要创建向数据表中输入数据的存储过程,代码编写举例如下:
CREATE PROC ProcTest1
@inttest smallint
,@strtest char(4) output
AS
INSERT INTO TEST_User.TRANS_TEST
valueS(@inttest,@strtest)
SELECT @strtest,* FROM TEST_User.TRANS_TEST
---- 以上代码向数据表 TEST_User.TRANS_TEST 添加数据,该数据表包括两个字段:第一个字段为 smallint 类型的数据,参数类型定为 Input(在存储过程中可缺省);第二个字段数据为长度为4 的字符类型,参数类型为 Output。
---- 其模块程序的编写与 DataInsert 过程相似,区别主要在于模块的中间部分,对应的主要代码如下:
StoredProc1.Close;
{关闭存储过程}
StoredProc1.Params[1].ParamType := ptInput;
{设置存储过程的参数类型}
StoredProc1.Params[1].AsInteger := 1;
{设置存储过程中参数的数据}
StoredProc1.Params[2].ParamType := ptOutput;
StoredProc1.Params[2].AsString := ’abcd’;
StoredProc1.Open; {打开存储过程}
//0-9-090