Dynamic crosstab chart-statistics on the number of employees' birthdays every day

(Records) Dynamic cross tabulation-Statistical daily employee birthdays: 1. table Structure: Emp (id, empNo, empName, gender, birthday, deptId) Dept (id, deptNo, deptName) 2. key points: 2.1 days per month, calculate the date difference between the first two months; 2.2 subtotal and total, dynamic display using groupbywithROLLUP2.3

(Records) Dynamic cross tabulation-Statistical daily employee birthdays: 1. table Structure: Emp (id, empNo, empName, gender, birthday, deptId) Dept (id, deptNo, deptName) 2. key points: 2.1 days per month, calculate the date difference between the first two months; 2.2 subtotal and total, using group by with ROLLUP 2.3 Dynamic Display

(Records) Dynamic cross tabulation-statistics on the number of employees' birthdays per day:

1. Table Structure

Emp (id, empNo, empName, gender, birthday, deptId)

Dept (id, deptNo, deptName)

2. Key points:

2.1 days per month, calculate the date difference between the two months at the beginning of the month;

2.2 subtotal and total, using group by with ROLLUP

2.3 dynamic display of 1, 2, 3... 28, 29... , Using dynamic cross tabulation

3. The Code is as follows:

/** Calculate the employee's birthday in a month */if exists (select * from sysobjects where id = object_id ('SP _ count_birthday') and OBJECTPROPERTY (id, n' IsProcedure ') = 1) drop procedure ← Procedure sp_count_birthday @ P_MONTH NVARCHAR (2) -- month WITH encryption asbegin begin transaction T1 DECLARE @ V_DAYS INT -- current month day DECLARE @ V_FIRST_DAY_M NVARCHAR (10) -- DECLARE @ V_YEAR NVARCHAR (4) on the first day of the current month -- DECLARE @ V_ SQL NVARCHAR (4000) for the current year -- the last executed SQL DECLARE @ V_ I INT -- count DECLARE @ V_TOTAL NVARCHAR (10) -- total DECLARE @ V_SUBTOTAL NVARCHAR (10) -- subtotal SET @ V_TOTAL = n' total 'set @ V_SUBTOTAL = n' subtotal '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' Department '+ ''', 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' gender '+ ''', '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 ENDENDGO--EXEC sp_count_birthday '6'