在做统计报表时的问题,
现有表A 如下: ID ProjectName CompanyName InDate
1 p1 c1 2009-10-1
2 p2 c1 2009-11-5
3 p3 c3 2009-12-6
4 p4 c1 2009-05-6
ProjectName 代表项目名称, CompanyName代表公司名称,一个公司可对应多个项目 现在要根据查询日期和公司名称统计如下:
传入时间2009年和c1,则查询出2009年所有月份每月每项目的记录
date p1 p2 p4
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 0 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 0
2009-11 0 1 0
2009-12 0 0 0
即传入年份的所有月份对应的各项目记录数,没有记录的显示 0 请高手帮忙~~~~
上午答案:--> Title : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date : 2009-12-01
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,ProjectName nvarchar(4),CompanyName nvarchar(4),InDate datetime)
insert into [tb]
select 1,'p1','c1','2009-10-1' union all
select 2,'p2','c1','2009-11-5' union all
select 3,'p3','c3','2009-12-6' union all
select 4,'p4','c1','2009-05-6'
if object_id('proc_test') is not null drop proc proc_test
go
create proc proc_test
@CompanyName varchar(10),
@year varchar(4)
as
begin
declare @sql varchar(8000)
set @sql='select date=convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120) '
select @sql=isnull(@sql+',','')+' sum(case when ProjectName='''+ProjectName +''' then 1 else 0 end)['+ProjectName+']' from tb where CompanyName=@CompanyName
set @sql=@sql+'from master..spt_values a left join [tb] b
on convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)=convert(varchar(7),b.InDate,120)
where number<12 and type=''P'' group by convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)'
exec(@sql)
end
go
exec proc_test 'c1','2009'
/*
date p1 p2 p4
------- ----------- ----------- -----------
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 0 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 0
2009-11 0 1 0
2009-12 0 0 0(12 個資料列受到影響)*/但如果测试记录为
ID ProjectName CompanyName InDate
1 p1 c1 2009-10-1
2 p2 c1 2009-11-5
3 p3 c3 2009-12-6
4 p1 c1 2009-05-6
则出现了重复列date p1 p2 p1
------- ----------- ----------- -----------
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 1 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 1
2009-11 0 1 0
2009-12 0 0 0(12 行受影响)
现有表A 如下: ID ProjectName CompanyName InDate
1 p1 c1 2009-10-1
2 p2 c1 2009-11-5
3 p3 c3 2009-12-6
4 p4 c1 2009-05-6
ProjectName 代表项目名称, CompanyName代表公司名称,一个公司可对应多个项目 现在要根据查询日期和公司名称统计如下:
传入时间2009年和c1,则查询出2009年所有月份每月每项目的记录
date p1 p2 p4
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 0 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 0
2009-11 0 1 0
2009-12 0 0 0
即传入年份的所有月份对应的各项目记录数,没有记录的显示 0 请高手帮忙~~~~
上午答案:--> Title : Generating test data [tb]
--> Author : 各位大大,本大俠只想要顆星星
--> Date : 2009-12-01
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,ProjectName nvarchar(4),CompanyName nvarchar(4),InDate datetime)
insert into [tb]
select 1,'p1','c1','2009-10-1' union all
select 2,'p2','c1','2009-11-5' union all
select 3,'p3','c3','2009-12-6' union all
select 4,'p4','c1','2009-05-6'
if object_id('proc_test') is not null drop proc proc_test
go
create proc proc_test
@CompanyName varchar(10),
@year varchar(4)
as
begin
declare @sql varchar(8000)
set @sql='select date=convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120) '
select @sql=isnull(@sql+',','')+' sum(case when ProjectName='''+ProjectName +''' then 1 else 0 end)['+ProjectName+']' from tb where CompanyName=@CompanyName
set @sql=@sql+'from master..spt_values a left join [tb] b
on convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)=convert(varchar(7),b.InDate,120)
where number<12 and type=''P'' group by convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)'
exec(@sql)
end
go
exec proc_test 'c1','2009'
/*
date p1 p2 p4
------- ----------- ----------- -----------
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 0 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 0
2009-11 0 1 0
2009-12 0 0 0(12 個資料列受到影響)*/但如果测试记录为
ID ProjectName CompanyName InDate
1 p1 c1 2009-10-1
2 p2 c1 2009-11-5
3 p3 c3 2009-12-6
4 p1 c1 2009-05-6
则出现了重复列date p1 p2 p1
------- ----------- ----------- -----------
2009-01 0 0 0
2009-02 0 0 0
2009-03 0 0 0
2009-04 0 0 0
2009-05 1 0 1
2009-06 0 0 0
2009-07 0 0 0
2009-08 0 0 0
2009-09 0 0 0
2009-10 1 0 1
2009-11 0 1 0
2009-12 0 0 0(12 行受影响)
--> Author : happy_stone
--> Date : 2009-12-01 14:34:49
if object_id('[tb]') is not null drop table [tb]
go
create table [tb] (ID int,ProjectName nvarchar(4),CompanyName nvarchar(4),InDate datetime)
insert into [tb]
select 1,'p1','c1','2009-10-1' union all
select 2,'p2','c1','2009-11-5' union all
select 3,'p3','c3','2009-12-6' union all
select 4,'p1','c1','2009-05-6'
if object_id('proc_test') is not null drop proc proc_test
go
create proc proc_test
@CompanyName varchar(10),
@year varchar(4)
as
begin
declare @sql varchar(8000)
set @sql='select date=convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120) '
select @sql=isnull(@sql+',','')+' sum(case when ProjectName='''+ProjectName +''' then 1 else 0 end)['+ProjectName+']' from (select distinct ProjectName,CompanyName from tb)tb where CompanyName=@CompanyName
set @sql=@sql+'from master..spt_values a left join [tb] b
on convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)=convert(varchar(7),b.InDate,120)
where number<12 and type=''P'' group by convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)'
exec( @sql)
end
go
exec proc_test 'c1','2009'
/*
date p1 p2
------- ----------- -----------
2009-01 0 0
2009-02 0 0
2009-03 0 0
2009-04 0 0
2009-05 1 0
2009-06 0 0
2009-07 0 0
2009-08 0 0
2009-09 0 0
2009-10 1 0
2009-11 0 1
2009-12 0 0(12 個資料列受到影響)
*/
go
create table [tb] (ID int,ProjectName nvarchar(4),CompanyName nvarchar(4),InDate datetime)
insert into [tb]
select 1,'p1','c1','2009-10-1' union all
select 2,'p2','c1','2009-11-5' union all
select 3,'p3','c3','2009-12-6' union all
select 4,'p1','c1','2009-05-6'
if object_id('proc_test') is not null drop proc proc_test
go
create proc proc_test
@CompanyName varchar(10),
@year varchar(4)
as
begin
declare @sql varchar(8000)
set @sql='select date=convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120) '
select @sql=isnull(@sql+',','')+' sum(case when ProjectName='''+ProjectName +''' then 1 else 0 end)['+ProjectName+']'
from (select distinct ProjectName,CompanyName from tb) t where CompanyName=@CompanyName
set @sql=@sql+'from master..spt_values a left join [tb] b
on convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)=convert(varchar(7),b.InDate,120)
where number<12 and type=''P'' group by convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)'
exec(@sql)
end
go
exec proc_test 'c1','2009'
-------------------
2009-01 0 0
2009-02 0 0
2009-03 0 0
2009-04 0 0
2009-05 1 0
2009-06 0 0
2009-07 0 0
2009-08 0 0
2009-09 0 0
2009-10 1 0
2009-11 0 1
2009-12 0 0
@CompanyName varchar(10),
@year varchar(4)
as
begin
declare @sql varchar(8000)
set @sql='select date=convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120) '
select @sql=isnull(@sql+',','')+' sum(case when ProjectName='''+ProjectName +''' then 1 else 0 end)['+ProjectName+']'
from (select distinct ProjectName,CompanyName from tb) t where CompanyName=@CompanyName
set @sql=@sql+'from master..spt_values a left join [tb] b
on convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)=convert(varchar(7),b.InDate,120)
where number<12 and type=''P'' group by convert(varchar(7),dateadd(mm,a.number,'''+@year+'-01-01''),120)'
exec(@sql)
end
go
exec proc_test 'c1','2009'
如何得到
date p1
2009-05 ..
2009-06 ..
2009-07 ..
2009-08 ..
2009-09 ..
2009-10 ..
2009-11 ..
时间范围之间的月份数据呢??? 急啊~~~~~~~~~~~