这个存储过程传入的开始时间是 @BeginTime = ‘2009-3-1’ @EndTime = ‘2009-4-1’
CREATE PROCEDURE rptCount
@BeginTime datetime,--开始时间
@EndTime datetime --结束时间AS
set nocount on
SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > @BeginTime) AND (dtInto < @EndTime) and (dtCheckOut is null) and (strroomname='医工库房' or strroomname='导管室' or strroomname='眼科库房' or strroomname='西区骨科库房' or strroomname='介入放射库房' or strroomname='口腔科库房')
GROUP BY strRoomName WITH rollup
GO但我想改成如果条件是‘医工库房’,时间就变成@BeginTime = ‘2009-2-1’ @EndTime = ‘2009-3-1’,如果是其他库房不变,请问怎么改?
CREATE PROCEDURE rptCount
@BeginTime datetime,--开始时间
@EndTime datetime --结束时间AS
set nocount on
SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > @BeginTime) AND (dtInto < @EndTime) and (dtCheckOut is null) and (strroomname='医工库房' or strroomname='导管室' or strroomname='眼科库房' or strroomname='西区骨科库房' or strroomname='介入放射库房' or strroomname='口腔科库房')
GROUP BY strRoomName WITH rollup
GO但我想改成如果条件是‘医工库房’,时间就变成@BeginTime = ‘2009-2-1’ @EndTime = ‘2009-3-1’,如果是其他库房不变,请问怎么改?
@BeginTime datetime,--开始时间
@EndTime datetime --结束时间AS
set nocount on
SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > @BeginTime) AND (dtInto < @EndTime) and (dtCheckOut is null) and (strroomname='医工库房' or strroomname='导管室' or strroomname='眼科库房' or strroomname='西区骨科库房' or strroomname='介入放射库房' or strroomname='口腔科库房')union all
SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > ‘2009-2-1’) AND (dtInto < ‘2009-3-1’) and (dtCheckOut is null) and (strroomname='医工库房' )
GROUP BY strRoomName WITH rollup
GO
@BeginTime datetime,--开始时间
@EndTime datetime --结束时间 AS
DECLARE @SDATE datetime
SELECT @SDATE = DATEADD(mm,0, DATEADD(mm,DATEDIFF(m,0,@BeginTime)-1, 0) )SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > @BeginTime) AND (dtInto < @EndTime) and (dtCheckOut is null) and (strroomname='导管室' or strroomname='眼科库房' or strroomname='西区骨科库房' or strroomname='介入放射库房' or strroomname='口腔科库房')
GROUP BY strRoomName
UNION ALL
SELECT ISNULL(strRoomName, '合计') AS 库房名称,
SUM(CASE substring(strGoodsCode, 1, 2) WHEN '02' THEN dmoney ELSE 0 END) AS 消耗品,
sum(case substring(strGoodsCode, 1, 2) when '01' then dmoney else null end) as 低值易耗品,
sum(case substring(strGoodsCode, 1, 2) when '07' then dmoney else null end) as 维修,
sum(case substring(strGoodsCode, 1, 2) when '06' then dmoney else null end) as 卫生材料,
sum(case substring(strGoodsCode, 1, 2) when '04' then dmoney else null end) as 计算机耗材,
SUM(CASE WHEN substring(strGoodsCode, 1, 2) IN ('02', '01', '07', '04','06') THEN dMoney ELSE 0 END) AS 合计,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strStoreMan) END) AS 库房经手人,
(CASE ISNULL(strRoomName, '合计') WHEN '合计' THEN NULL ELSE MAX(strFinalMan) END) AS 库房会计
FROM tblBills
where (( iBillKind=1 ) or (ibillkind=99)) and (iminus=0) and (dtInto > @SDATE) AND (dtInto < @BeginTime) and (dtCheckOut is null) and (strroomname='医工库房')
GROUP BY strRoomName GO
你这个语法不对union all 条件必须一样。union all 上面的没有GROUP BY strRoomName WITH rollup,会报错。即使有,会出现两个合计,但他们是分开的。没有总的计算
也很感谢,这个也没有算出合计。如把GROUP BY strRoomName改成GROUP BY strRoomName WITH rollup还是没有算出总的合计