집 >데이터 베이스 >MySQL 튜토리얼 >动态交叉表-统计每天员工生日数
(2010-8-27 记录)动态交叉表-统计每天员工生日数: 1.表结构 Emp ( id , empNo,empName,gender,birthday,deptId ) Dept(id,deptNo,deptName) 2.要点: 2.1 每月天数,计算两个月初的日期差即可; 2.2 小计和合计,利用 group by with ROLLUP 2.3 动态显
(2010-8-27 记录)动态交叉表-统计每天员工生日数:
1.表结构
Emp ( id , empNo,empName,gender,birthday,deptId )
Dept(id,deptNo,deptName)
2.要点:
2.1 每月天数,计算两个月初的日期差即可;
2.2 小计和合计,利用 group by with ROLLUP
2.3 动态显示 1 号, 2 号, 3 号 …28 号, 29 号 … ,利用动态交叉表实现
3 .代码如下:
/** 统计某月员工生日 */ IF EXISTS (select * from sysobjects where id = object_id('sp_count_birthday') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP PROCEDURE sp_count_birthday GO CREATE Procedure sp_count_birthday @P_MONTH NVARCHAR(2) --月份 WITH encryption AS BEGIN BEGIN TRANSACTION T1 DECLARE @V_DAYS INT --当月天数 DECLARE @V_FIRST_DAY_M NVARCHAR(10) --当月第一天 DECLARE @V_YEAR NVARCHAR(4) --当前年份 DECLARE @V_SQL NVARCHAR(4000) --最后执行的sql DECLARE @V_I INT --计数 DECLARE @V_TOTAL NVARCHAR(10) --合计 DECLARE @V_SUBTOTAL NVARCHAR(10) --小计 SET @V_TOTAL = N'合计' SET @V_SUBTOTAL = N'小计' SET @V_YEAR = datepart(yyyy,GETDATE()) SET @V_FIRST_DAY_M = @V_YEAR + '-' + @P_MONTH + '-' + '01' SET @V_DAYS = DATEDIFF(dd,@V_FIRST_DAY_M,DATEADD(mm,1,@V_FIRST_DAY_M)) CREATE TABLE #tmp_date(emp_birth datetime) SET @V_I = 0 WHILE(@V_I < @V_DAYS) BEGIN INSERT INTO #tmp_date(emp_birth) values (DATEADD(dd,@V_I,@V_FIRST_DAY_M)) SET @V_I = @V_I + 1 END SET @V_SQL = 'SELECT case when(grouping(org_dpt_name) = 1) then N''' + @V_TOTAL + ''' else isNULL(org_dpt_name, '''') end as ''' + N'部门' + ''', case when(grouping(org_dpt_name) <> 1 and grouping(emp_sex) = 1) then N'''+@V_SUBTOTAL+''' else isNULL(dbo.fun_get_lang(emp_sex,''pla_lan_001'',''1'') , '''' ) end as ''' + N'性别' + ''',' select @V_SQL = @V_SQL + 'sum(case when right(CONVERT(NVARCHAR(8),emp_birth,112),4) = ''' + right(CONVERT(NVARCHAR(8),emp_birth,112),4) + ''' then 1 else 0 end) as ''' + cast(datepart(d,emp_birth) as varchar(2)) + N'号' + ''',' from (select emp_birth from #tmp_date) a select @V_SQL = left(@V_SQL,len(@V_SQL)-1) + ' from emp_info e,org_dept d where e.emp_dptcd=d.org_dpt_levcd group by org_dpt_name,emp_sex WITH ROLLUP order by org_dpt_name DESC,emp_sex DESC' --print @V_SQL exec(@V_SQL) IF @@ERROR > 0 BEGIN ROLLBACK TRANSACTION T1 END ELSE BEGIN COMMIT TRANSACTION T1 END END GO --EXEC sp_count_birthday '6'