T-SQL recipes customized Database Objects

The problem

Creating flexible custom objects is by no means a simple task. For example, HR wants to see the cumulative status of each job title in all years of employment

The solution

Let's take a step-by-step break:

• Get a set of entry years, such as 1999,2000,2001...etc
• Counting the number of recruits per year based on unique values, of course, dynamic pivot
• and write it into the SP.

Example 1: Getting the Age collection

DECLARE @hire_date_years TABLE(Hire_date_yearNVARCHAR( -)    );INSERT   into @hire_date_years(hire_date_year)SELECT DISTINCT                DATEPART( Year, Employee.hiredate) fromHumanResources.Employee;DECLARE @sql_yearlist NVARCHAR(MAX);SELECT  @sql_yearlist = ISNULL(@sql_yearlist,"')+ ','        + QUOTENAME(hire_date_year) from    @hire_date_yearsSET @sql_yearlist = STUFF(@sql_yearlist,1,1,"')SELECT  @sql_yearlist

View Code

Example 2 statistics based on unique values

DECLARE @hire_date_years TABLE(Hire_date_yearNVARCHAR( -)    );DECLARE @sql_yearlist NVARCHAR(MAX);DECLARE @sql_command NVARCHAR(MAX);INSERT   into @hire_date_years(hire_date_year)SELECT DISTINCT                DATEPART( Year, Employee.hiredate) fromHumanResources.Employee;SELECT  @sql_yearlist = ISNULL(@sql_yearlist,"')+N','        + QUOTENAME(hire_date_year) from    @hire_date_yearsSET @sql_yearlist = STUFF(@sql_yearlist,1,1,"')SET @sql_command =N'With Employee_data as (SELECT Employee.businessentityid, Employee.jobtitle,             DATEPART (year, employee.hiredate) as Hiredate_year from HumanResources.Employee ) SELECT JobTitle,' + @sql_yearlist +N'From Employee_datapivot (COUNT (BusinessEntityID) for hiredate_year in ('  + @sql_yearlist +N')) Pivot_data'PRINT @sql_command

View Code

Example 3: Writing into the SP

IF object_id(N'dbo.job_title_year_summary','P') is  not NULLBEGIN    DROP PROCEDUREdbo.job_title_year_summary;ENDSETAnsi_nulls onGOSETQuoted_identifier onGOCREATE PROCEDUREjob_title_year_summary asBEGIN    --SET NOCOUNT on added to prevent extra result sets from    --interfering with SELECT statements.    SETNOCOUNT on; DECLARE @hire_date_years TABLE(Hire_date_yearNVARCHAR( -)        ); DECLARE @sql_yearlist NVARCHAR(MAX); DECLARE @sql_command NVARCHAR(MAX); INSERT   into @hire_date_years(hire_date_year)SELECT DISTINCT                    DATEPART( Year, Employee.hiredate) fromHumanResources.Employee; SELECT  @sql_yearlist = ISNULL(@sql_yearlist,"')+N','            + QUOTENAME(hire_date_year) from    @hire_date_years    SET @sql_yearlist = STUFF(@sql_yearlist,1,1,"')    SET @sql_command =N'with Employee_data as (SELECT Employee.businessentityid, EMPLOYEE.J Obtitle, DATEPART (year, employee.hiredate) as Hiredate_year from Humanre Sources. Employee) SELECT JobTitle,' + @sql_yearlist +N'From employee_data PIVOT (COUNT (BusinessEntityID) for hiredate_year in ('  + @sql_yearlist +N')) Pivot_data'    PRINT @sql_command; EXECsp_executesql@sql_command;ENDGO

View Code

With this stored procedure, you can use it later to store its results with the previous table, and do the next step of processing.

T-SQL recipes customized Database Objects