如何得到按月生成的统计数据?
有表如下:
id workdate ondutyName
1 2006-1-1 a;b;
2 2006-1-2 b;c;
3 2006-1-3 a;c;
....
workdate表示值班日期,ondutyName表示值班人员,以 '; '隔开不同的人员,
现在要得到这样的统计结果:
比如统计2006年1月每天的值班情况,得到以下的数据:
Name 1 2 3 ....31
a 1 0 1 .......
b 1 1 0 .......
c 0 1 1 .......
如果a这天值班了,就用1表示,不值班,就用0表示
如果统计的是2006年2月数据,因为2月只有28天,那么,生成的统计表的格式就如下:
Name 1 2 3 ......28
a .............
b .............
c .............
请问该怎样写这样的存储过程?
[解决办法]
--建立测试数据
CREATE TABLE A(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100))--声明表变量
INSERT A
SELECT 1 , '2006-1-1 ', 'a;b ' UNION ALL
SELECT 2 , '2006-1-2 ', 'b;c ' UNION ALL
SELECT 3 , '2006-1-3 ', 'a;c '
GO
--建立辅助临时表1
SELECT TOP 8000 id = identity(int,1,1)
INTO Tmp1 FROM syscolumns a, syscolumns b
--建立辅助临时表2
SELECT
A.ID, A.workdate,
ondutyName = SUBSTRING(A.ondutyName, B.ID, CHARINDEX( '; ', A.ondutyName + '; ', B.ID) - B.ID)
INTO Tmp2
FROM A , Tmp1 B
WHERE SUBSTRING( '; ' + a.ondutyName, B.id, 1) = '; '
GO
SELECT * FROM Tmp2
GO
--建立存储过程(参数:年、月)
CREATE PROC Report_Details
@YEAR INT,
@MONTH INT
AS
DECLARE @SQL VARCHAR(8000)
DECLARE @DayOfMonth INT
DECLARE @I INT
SET @DayOfMonth=DATEDIFF(day,cast(@YEAR as varchar)+ '- '+cast(@MONTH as varchar)+ '-01 ',cast(@YEAR as varchar)+ '- '+cast(@MONTH+1 as varchar)+ '-01 ')
SET @SQL= 'SELECT ondutyName '
SET @I=1
WHILE @I <=@DayOfMonth
BEGIN
SET @SQL=@SQL+ ',[ '+CAST(@I AS VARCHAR(10))+ ']=0 '
SET @I=@I+1
END
SET @SQL=@SQL+ ' INTO Tmp3 FROM (SELECT DISTINCT ondutyName FROM Tmp2) X '
EXEC(@SQL)
SET @SQL= ' '
SET @I=1
WHILE @I <=@DayOfMonth
BEGIN
SET @SQL=@SQL+ 'UPDATE Tmp3 SET [ '+CAST(@I AS VARCHAR(10))+ ']=1 FROM Tmp2 a,Tmp3 b WHERE a.ondutyName=b.ondutyName AND DAY(a.workdate)= '+CAST(@I AS VARCHAR(10))+CHAR(10)
SET @I=@I+1
END
EXEC(@SQL)
SELECT * FROM Tmp3
GO
--调用过程
EXEC Report_Details '2006 ', '1 '
--删除测试环境
DROP TABLE A,Tmp1,Tmp2,Tmp3
DROP PROC Report_Details
--查看结果
/*
IDworkdateondutyName
12006-1-1a
12006-1-1b
22006-1-2b
22006-1-2c
32006-1-3a
32006-1-3c
ondutyName123...
-----------------------------------
a101...
b110...
c011...
*/
[解决办法]
用用我的~~动态sql语句
CREATE TABLE AAA(ID INT , workdate VARCHAR(10), ondutyName VARCHAR(100))--声明表变量
INSERT AAA
SELECT 1 , '2006-1-1 ', 'a;b ' UNION ALL
SELECT 2 , '2006-1-2 ', 'b;c ' UNION ALL
SELECT 3 , '2006-1-3 ', 'a;c '
go
select top 3000 identity(int,1,1)[id] into # from sysobjects a, sysobjects b
go
select substring(ondutyname+ '; ',b.id,CHARINDEX( '; ',ondutyname+ '; ',b.id)-b.id)Name,
day(workdate)workdate
into #t
from (select * from aaa where workdate BETWEEN '2006-1-1 ' and '2006-1-31 ') a,# b
where substring( '; '+ondutyname,b.id,1)= '; '
go
declare @table varchar(1000)
set @table= 'select Name, '
select @table=@table+ '(case when((select workdate from #t b where b.name=a.name and b.workdate= '+
QUOTENAME(workdate, ' ' ' ')+ '))is null then 0 else 1 end) as '+QUOTENAME(workdate)+ ', '
from #t
group by workdate
set @table = left(@table,len(@table)-1)+ ' from #t a '
exec (@table)
drop table #t
drop table #