例如设备有 A1,A2,A3,A4 其中A4没加油 现有一表数据如下
设备 加油量 加油日期
A1 10 2009-10-1 A2 2 2009-10-2 A3 3 2009-10-1 现在想得到10月份每天每台设备的加油汇总表，如下 日期
设备 1 2 3 4 5 。 31 1-31 18-17 说明（18-17是指上月18号到17号数据的合计） A1 10 10 。。 A2 2 2 。。 A3 3 3 。。 A4 0 。。 日统计 13 2 15 。。
存储过程参数 如 ‘2009-08’ 关键是怎么取出最后一列的合计，即上月18号到这月17号的合计
请高手帮忙，分不够再加
设备 加油量 加油日期
A1 10 2009-10-1 A2 2 2009-10-2 A3 3 2009-10-1 现在想得到10月份每天每台设备的加油汇总表，如下 日期
设备 1 2 3 4 5 。 31 1-31 18-17 说明（18-17是指上月18号到17号数据的合计） A1 10 10 。。 A2 2 2 。。 A3 3 3 。。 A4 0 。。 日统计 13 2 15 。。
存储过程参数 如 ‘2009-08’ 关键是怎么取出最后一列的合计，即上月18号到这月17号的合计
请高手帮忙，分不够再加
sum(case convert(varchar(10),加油日期,120) when '2009-10-01' then 加油量 else 0 end) [2009-10-01],
sum(case convert(varchar(10),加油日期,120) when '2009-10-02' then 加油量 else 0 end) [2009-10-02],
...
sum(case convert(varchar(10),加油日期,120) when '2009-10-31' then 加油量 else 0 end) [2009-10-31],
sum(case when convert(varchar(10),加油日期,120) in ('2009-09-17','2009-09-18') then 加油量 else 0 end) [18-17]
from tb
group by 设备
if object_id('[tb]') is not null drop table [tb]
create table [tb] (设备 varchar(2),加油量 int,加油日期 datetime)
insert into [tb]
select 'A1',10,'2009-10-1' union all
select 'A2',2,'2009-10-2' union all
select 'A3',3,'2009-10-1'
gocreate proc sp_wsp
@date varchar(7)
as
select * into #设备表 from (select 设备='A1' union all select 'A2' union all select 'A3' union all select 'A4')a
select top (datediff(dd,@date+'-01',dateadd(mm,1,@date+'-01'))) days=identity(int,1,1)
into # from master..spt_values
declare @sql varchar(8000)
set @sql='select a.设备'
select @sql=@sql+',['+ltrim(days)+']=sum(case datepart(dd,加油日期) when '''+ltrim(days)+''' then 加油量 else 0 end)'
from #
set @sql=@sql+',[18-17]=sum(case when 加油日期 between '''+@date+'-18'' and dateadd(mm,1,'''+@date+'-17'') then 加油量 else 0 end)
from #设备表 a left join tb b on a.设备=b.设备 group by a.设备'
exec(@sql)
go--测试：
exec sp_wsp '2009-10'--结果：
设备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 18-17
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
A2 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
A3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
A4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
set @d = '2009-10'
set @d1 = '2009-09'
select @s = isnull(@s+',','')+
'
['+convert(char(10),dateadd(d,number,@d+'-01'),120)+'] =max( case when
datediff(d,加油日期,'''+convert(char(10),dateadd(d,number,@d+'-01'),120)+
''') = 0 then 加油量 else 0 end)'from master..spt_values
where type = 'P' and dateadd(d,number,@d+'-01')<'2009-11-01'
exec('select 设备, '+ @s +' ,sum(加油量) ,[18-17] = (select sum(加油量) from ta where 设备 = a.设备 and 加油日期 between '''+@d1+'-18'+ ''' and '''+@d+'-17'') from ta a
group by 设备')
--刚才计算的是本月18号到下月17号，改下：
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (设备 varchar(2),加油量 int,加油日期 datetime)
insert into [tb]
select 'A1',10,'2009-10-1' union all
select 'A2',2,'2009-10-2' union all
select 'A3',3,'2009-10-1'
gocreate proc sp_wsp
@date varchar(7)
as
select * into #设备表 from (select 设备='A1' union all select 'A2' union all select 'A3' union all select 'A4')a
select top (datediff(dd,@date+'-01',dateadd(mm,1,@date+'-01'))) days=identity(int,1,1)
into # from master..spt_values
declare @sql varchar(8000)
set @sql='select a.设备'
select @sql=@sql+',['+ltrim(days)+']=sum(case datepart(dd,加油日期) when '''+ltrim(days)+''' then 加油量 else 0 end)'
from #
set @sql=@sql+',[18-17]=sum(case when 加油日期 between dateadd(mm,-1,'''+@date+'-18'') and '''+@date+'-17'' then 加油量 else 0 end)
from #设备表 a left join tb b on a.设备=b.设备 group by a.设备'
exec(@sql)
go--测试：
exec sp_wsp '2009-10'--结果：
设备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 18-17
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10
A2 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
A3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
A4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
--> 测试时间：2009-12-17 12:28:17--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([设备] varchar(10),[加油量] int,[加油日期] datetime)
insert [TB]
select 'A1',10,'2009-10-1' union all
select 'A2',2,'2009-10-2' union all
select 'A3',3,'2009-10-1'declare @s varchar(10),@sql varchar(4000)
set @s='2009-10'
select @sql=isnull(@sql+',','')+'['+rtrim(number+1)+'号]=sum(case when 设备=T.设备 and datediff(dd,加油日期,'''+convert(varchar(10),dateadd(dd,number,@s+'-01'),120)+''')=0 then 加油量 else 0 end) ' +char(10)
from spt_values where type='p' and dateadd(dd,number,@s+'-01')<dateadd(dd,-1,dateadd(mm,1,@s+'-01'))
set @sql=@sql+',[1-31号]=sum(case when 设备=T.设备 then 加油量 else 0 end),
[18-17]=sum(case when 设备=T.设备 and 加油日期 between '''+convert(varchar(10),dateadd(mm,-1,@s+'-16'),120)+''' and '''+convert(varchar(10),dateadd(mm,-1,@s+'-17'),120)+''' then 加油量 else 0 end)'
exec('select isnull(设备,''合计''),'+@sql+' from TB T group by 设备 with rollup')
/* 1号 2号 3号 4号 5号 6号 7号 8号 9号 10号 11号 12号 13号 14号 15号 16号 17号 18号 19号 20号 21号 22号 23号 24号 25号 26号 27号 28号 29号 30号 1-31号 18-17
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10 0
A2 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 0
A3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3 0
合计 13 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 15 0*/drop table [TB]
--忘记加日统计了。加上日统计：
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (设备 varchar(2),加油量 int,加油日期 datetime)
insert into [tb]
select 'A1',10,'2009-10-1' union all
select 'A2',2,'2009-10-2' union all
select 'A3',3,'2009-10-1'
gocreate proc sp_wsp
@date varchar(7)
as
select * into #设备表 from (select 设备='A1' union all select 'A2' union all select 'A3' union all select 'A4')a
select top (datediff(dd,@date+'-01',dateadd(mm,1,@date+'-01'))) days=identity(int,1,1)
into # from master..spt_values
declare @sql varchar(8000),@sql1 varchar(8000)
set @sql='select a.设备'
set @sql1='select ''日统计'''
select @sql=@sql+',['+ltrim(days)+']=sum(case datepart(dd,加油日期) when '''+ltrim(days)+''' then 加油量 else 0 end)',
@sql1=@sql1+',sum(case datepart(dd,加油日期) when '''+ltrim(days)+''' then 加油量 else 0 end)'
from #
set @sql=@sql+',[18-17]=sum(case when 加油日期 between dateadd(mm,-1,'''+@date+'-18'') and '''+@date+'-17'' then 加油量 else 0 end)
from #设备表 a left join tb b on a.设备=b.设备 group by a.设备'
set @sql1=@sql1+',[18-17]=sum(case when 加油日期 between dateadd(mm,-1,'''+@date+'-18'') and '''+@date+'-17'' then 加油量 else 0 end)
from tb '
exec(@sql+' union all '+@sql1)
go--测试：
exec sp_wsp '2009-10'--结果：
设备 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 18-17
------ ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A1 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 10
A2 0 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2
A3 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 3
A4 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
日统计 13 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 15
--大概就这样，动态的自己写了，忙。。
declare @equipment varchar(8000)--设备
declare @month varchar(10) --年月
select @month = '2009-10'
select @equipment = 'A1,A2,A3,A4'
--设备表
create table #equipment(code varchar(20))
--插入设备表
select @equipment = 'insert into #equipment select '''+replace(@equipment,',',''' union all select ''')+''''
exec(@equipment)select t.*,isnull(h.[加油量],0) as [加油量]
from
(
select convert(varchar(10),dateadd(day,number,@month+'-01'),120) as [date],
r.code
from master..spt_values,#equipment r
where type = 'P'
and convert(varchar(7),dateadd(day,number,@month+'-01'),120)='2009-10'
) t left join [tb] h on
t.[date] = h.[加油日期] and t.code = h.[设备]
--select * from #equipmentdrop table #equipment
-----------------------
2009-10-01 A1 10
2009-10-02 A1 0
2009-10-03 A1 0
2009-10-04 A1 0
2009-10-05 A1 0
2009-10-06 A1 0
2009-10-07 A1 0
2009-10-08 A1 0
2009-10-09 A1 0
2009-10-10 A1 0
2009-10-11 A1 0
2009-10-12 A1 0
2009-10-13 A1 0
2009-10-14 A1 0
2009-10-15 A1 0
2009-10-16 A1 0
2009-10-17 A1 0
2009-10-18 A1 0
2009-10-19 A1 0
2009-10-20 A1 0
2009-10-21 A1 0
2009-10-22 A1 0
2009-10-23 A1 0
2009-10-24 A1 0
2009-10-25 A1 0
2009-10-26 A1 0
2009-10-27 A1 0
2009-10-28 A1 0
2009-10-29 A1 0
2009-10-30 A1 0
2009-10-31 A1 0
2009-10-01 A2 0
2009-10-02 A2 2
2009-10-03 A2 0
2009-10-04 A2 0
2009-10-05 A2 0
2009-10-06 A2 0
2009-10-07 A2 0
2009-10-08 A2 0
2009-10-09 A2 0
2009-10-10 A2 0
2009-10-11 A2 0
2009-10-12 A2 0
2009-10-13 A2 0
2009-10-14 A2 0
2009-10-15 A2 0
2009-10-16 A2 0
2009-10-17 A2 0
2009-10-18 A2 0
2009-10-19 A2 0
2009-10-20 A2 0
2009-10-21 A2 0
2009-10-22 A2 0
2009-10-23 A2 0
2009-10-24 A2 0
2009-10-25 A2 0
2009-10-26 A2 0
2009-10-27 A2 0
2009-10-28 A2 0
2009-10-29 A2 0
2009-10-30 A2 0
2009-10-31 A2 0
2009-10-01 A3 3
2009-10-02 A3 0
2009-10-03 A3 0
2009-10-04 A3 0
2009-10-05 A3 0
2009-10-06 A3 0
2009-10-07 A3 0
2009-10-08 A3 0
2009-10-09 A3 0
2009-10-10 A3 0
2009-10-11 A3 0
2009-10-12 A3 0
2009-10-13 A3 0
2009-10-14 A3 0
2009-10-15 A3 0
2009-10-16 A3 0
2009-10-17 A3 0
2009-10-18 A3 0
2009-10-19 A3 0
2009-10-20 A3 0
2009-10-21 A3 0
2009-10-22 A3 0
2009-10-23 A3 0
2009-10-24 A3 0
2009-10-25 A3 0
2009-10-26 A3 0
2009-10-27 A3 0
2009-10-28 A3 0
2009-10-29 A3 0
2009-10-30 A3 0
2009-10-31 A3 0
2009-10-01 A4 0
2009-10-02 A4 0
2009-10-03 A4 0
2009-10-04 A4 0
2009-10-05 A4 0
2009-10-06 A4 0
2009-10-07 A4 0
2009-10-08 A4 0
2009-10-09 A4 0
2009-10-10 A4 0
2009-10-11 A4 0
2009-10-12 A4 0
2009-10-13 A4 0
2009-10-14 A4 0
2009-10-15 A4 0
2009-10-16 A4 0
2009-10-17 A4 0
2009-10-18 A4 0
2009-10-19 A4 0
2009-10-20 A4 0
2009-10-21 A4 0
2009-10-22 A4 0
2009-10-23 A4 0
2009-10-24 A4 0
2009-10-25 A4 0
2009-10-26 A4 0
2009-10-27 A4 0
2009-10-28 A4 0
2009-10-29 A4 0
2009-10-30 A4 0
2009-10-31 A4 0