/**
 統(tǒng)計某月員工生日
*/

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 --當(dāng)月天數(shù)
 DECLARE @V_FIRST_DAY_M NVARCHAR(10) --當(dāng)月第一天
 DECLARE @V_YEAR NVARCHAR(4) --當(dāng)前年份
 DECLARE @V_SQL NVARCHAR(4000) --最后執(zhí)行的sql
 DECLARE @V_I INT --計數(shù)
 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'