如何得到按月生成的统计数据?
有表如下:
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
--查看结果
/*
ID workdate ondutyName
1 2006-1-1 a
1 2006-1-1 b
2 2006-1-2 b
2 2006-1-2 c
3 2006-1-3 a
3 2006-1-3 c
ondutyName 1 2 3...
-----------------------------------
a 1 0 1...
b 1 1 0...
c 0 1 1...
*/
------解决方案--------------------用用我的~~动态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