※※※※※※关于某月份消耗统计※※※※※※※※

[LWWL]

要生成某段日期的消耗表 格式如下：
TTMP2
部门 本月止累计 当月消耗 设备备件 生产备件 轧辊 钢材 润滑油 轴承 化工 劳保 五金工具 汽油柴油 其它
（设备备件，生产备件是产品大类里记录，轧辊至其它是属于辅材产品大类里的产品子类）

数据根据下面表生成：
部门管理
id 部门名称

出库表
部门名称 产品大类 产品子类 总金额

如何根据上面2个表生成 TTMP2?

[LWWL]

zjcxc(邹建)
这个本月止累计指的是各个部门的累计 不是所有部门的累计 怎作?

[sdhdy]

按部门进行分组，用group by 部门

[LWWL]

按楼上的例子 因改加在哪里？

[zarge]

declare @d1 datetime,@d2 datetime
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 部门名称

[sdhdy]

try:
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 部门名称

[zjcxc]

大家改的没错,只不过忘了加逗号

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 部门名称

[zjcxc]

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 部门名称

[zjcxc]

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 部门名称

[txlicenhe]

1: 出库表中存的是部门名称？建议存id
2: 没有一个日期字段？
3: 就算有日期字段,本月止累计怎么来？

[zarge]

出库表应该有个出库时间吧
本月止累计不包括本月吧

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 部门名称

[liuyun2003]

那个大类和小类的分配不清楚。

[LWWL]

出库表是有个出库日期 忘记加了

[LWWL]

zarge(鲨至) 本月止包括本月的
比如统计2003.08.20--2003.09.30 得到的就是这段日期的累计

[zjcxc]

declare @d1 datetime,@d2 datetime
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 部门名称

[zjcxc]

declare @d1 datetime,@d2 datetime
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 部门名称

[zjcxc]

改一下,少写了一个逗号
select @d1='2003.08.20' --统计的开始时间
,@d2='2003.09.30' --统计的结束时间