我要达到的目的是:显示效果如下 备件名称,备件图号,备件规格,备件价格,期初数量,期初金额,本期收入数量,本期收入金额,本期使用数量,本期使用金额,期末数量,期末金额
表HBX_BJKCB(BJMC,BJTH,BJGG,BJDJ,RKSL,CKSL,RKJE,CKJE,CKSJ)存储过程如下:
CREATE procedure HBX_BJKCB_NDTJ
@NDDTIME1 DATETIME,
@NDDTIME2 DATETIME
AS
BEGIN
SELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价, SUM(RKSL) - SUM(CKSL) AS 期初数量, SUM(RKJE) - SUM(CKJE) AS 期初金额 FROM HBX_BJKCB WHERE CKSJ <=@NDDTIME1
GROUP BY BJMC, BJTH, BJDJ, BJGG
SELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价,SUM(RKSL)-SUM(CKSL) AS 本期收入数量,SUM(RKJE)-SUM(CKJE) AS 本期收入金额 FROM HBX_BJKCB WHERE CKSJ BETWEEN @NDDTIME1 AND @NDDTIME2 GROUP BY BJMC, BJTH, BJDJ, BJGGSELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价,SUM(RKSL)-SUM(CKSL) AS 期末数量,SUM(RKJE)-SUM(CKJE) AS 期末金额 FROM HBX_BJKCB WHERE CKSJ BETWEEN >= @NDDTIME2
GROUP BY BJMC, BJTH, BJDJ, BJGG
end
GO
这样写行吗?请帮忙。。
表HBX_BJKCB(BJMC,BJTH,BJGG,BJDJ,RKSL,CKSL,RKJE,CKJE,CKSJ)存储过程如下:
CREATE procedure HBX_BJKCB_NDTJ
@NDDTIME1 DATETIME,
@NDDTIME2 DATETIME
AS
BEGIN
SELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价, SUM(RKSL) - SUM(CKSL) AS 期初数量, SUM(RKJE) - SUM(CKJE) AS 期初金额 FROM HBX_BJKCB WHERE CKSJ <=@NDDTIME1
GROUP BY BJMC, BJTH, BJDJ, BJGG
SELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价,SUM(RKSL)-SUM(CKSL) AS 本期收入数量,SUM(RKJE)-SUM(CKJE) AS 本期收入金额 FROM HBX_BJKCB WHERE CKSJ BETWEEN @NDDTIME1 AND @NDDTIME2 GROUP BY BJMC, BJTH, BJDJ, BJGGSELECT BJMC AS 备件名称, BJTH AS 备件图号, BJGG AS 备件规格, BJDJ AS 备件单价,SUM(RKSL)-SUM(CKSL) AS 期末数量,SUM(RKJE)-SUM(CKJE) AS 期末金额 FROM HBX_BJKCB WHERE CKSJ BETWEEN >= @NDDTIME2
GROUP BY BJMC, BJTH, BJDJ, BJGG
end
GO
这样写行吗?请帮忙。。
这个是什么写法??这个地方是有问题的
@NDDTIME1 DATETIME,
@NDDTIME2 DATETIME
AS
BEGINSELECT
BJMC AS 备件名称,
BJTH AS 备件图号,
BJGG AS 备件规格,
BJDJ AS 备件单价,
isnull(SUM(RKSL)-SUM(CKSL),0) AS 本期收入数量,
isnull(SUM(RKJE)-SUM(CKJE),0) AS 本期收入金额
FROM
HBX_BJKCB
WHERE
CKSJ BETWEEN @NDDTIME1 AND @NDDTIME2
GROUP
BY BJMC, BJTH, BJDJ, BJGGend
GO
---看你的意思估计是这样
@NDDTIME1 DATETIME,
@NDDTIME2 DATETIME
AS
BEGIN
SELECT
BJMC AS 备件名称,
BJTH AS 备件图号,
BJGG AS 备件规格,
BJDJ AS 备件单价,
isnull(SUM(RKSL)-SUM(CKSL),0) AS 本期收入数量,
isnull(SUM(RKJE)-SUM(CKJE),0) AS 本期收入金额
FROM HBX_BJKCB
WHERE CKSJ BETWEEN @NDDTIME1 AND @NDDTIME2 GROUP BY BJMC, BJTH, BJDJ, BJGG
end