出库表应该有个出库时间吧
本月止累计不包括本月吧select 部门名称, (select sum(总金额) from 出库表 where datediff(month, 出库时间, getdate()) > 0) as 本月止累计, (select sum(总金额) from 出库表 where datediff(month, 出库时间, getdate()) = 0) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表
Where Datediff(month, 出库时间, getdate()) = 0
Group By 部门名称
本月止累计不包括本月吧select 部门名称, (select sum(总金额) from 出库表 where datediff(month, 出库时间, getdate()) > 0) as 本月止累计, (select sum(总金额) from 出库表 where datediff(month, 出库时间, getdate()) = 0) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表
Where Datediff(month, 出库时间, getdate()) = 0
Group By 部门名称
比如统计2003.08.20--2003.09.30 得到的就是这段日期的累计
select @d1='2003.08.20' --统计的开始时间
@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表
Where 出库时间 between @d1 and @d2
Group By 部门名称
select @d1='2003.08.20' --统计的开始时间
@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表
Where 出库时间 between @d1 and @d2
Group By 部门名称
select @d1='2003.08.20' --统计的开始时间
,@d2='2003.09.30' --统计的结束时间
这个本月止累计指的是各个部门的累计 不是所有部门的累计 怎作?
select @d1='2003.08.20', --统计的开始时间
@d2='2003.09.30' --统计的结束时间select 部门名称,
sum(总金额) as 本月止累计,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0 and 部门名称 = a.部门名称) as 本月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表 a
Where 出库时间 between @d1 and @d2
Group By 部门名称
declare @d1 datetime,@d2 datetime
select @d1='2003.08.20', --统计的开始时间
@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0 and 部门名称=a.部门名称) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表 a
Where 出库时间 between @d1 and @d2
Group By 部门名称
select @d1='2003.08.20' --统计的开始时间
,@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0 and 部门名称=a.部门名称) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表 a
Where 出库时间 between @d1 and @d2
Group By 部门名称
select @d1='2003.08.20' --统计的开始时间
,@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0 and 部门名称=a.部门名称) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表 a
Where 出库时间 between @d1 and @d2
Group By 部门名称
select @d1='2003.08.20' --统计的开始时间
,@d2='2003.09.30' --统计的结束时间select 部门名称,
(select sum(总金额) from 出库表 where datediff(month,出库时间,@d2)>=0 and 部门名称=a.部门名称) as 本月累计,
sum(总金额) as 当月消耗,
sum(case when 产品大类 = '设备备件' then 总金额 else 0 end) as 设备备件,
sum(case when 产品大类 = '生产备件' then 总金额 else 0 end) as 生产备件,
sum(case when 产品小类 = '轧辊' then 总金额 else 0 end) as 轧辊,
sum(case when 产品小类 = '钢材' then 总金额 else 0 end) as 钢材,
sum(case when 产品小类 = '润滑油' then 总金额 else 0 end) as 润滑油,
sum(case when 产品小类 = '轴承' then 总金额 else 0 end) as 轴承,
sum(case when 产品小类 = '化工' then 总金额 else 0 end) as 化工,
sum(case when 产品小类 = '劳保' then 总金额 else 0 end) as 劳保,
sum(case when 产品小类 = '五金工具' then 总金额 else 0 end) as 五金工具,
sum(case when 产品小类 = '汽油柴油' then 总金额 else 0 end) as 汽油柴油,
sum(case when 产品小类 = '其他' then 总金额 else 0 end) as 其他
From 出库表 a
Where 出库时间 between @d1 and @d2
Group By 部门名称