Detailed description and implementation code of the custom month separation function of SQL server, SQL server

Detailed description and implementation code of the custom month separation function of SQL server, SQL server

In the recent project development process, I encountered the SQL server automatic month division function requirement. Here I will sort out the documents online.

1. Why is there a custom month Division requirement?

Today, when combing all functions of a platform, we found a custom split-month function, that is, specifying the start day index value of the split month (any value within the Closed range from 1 to 31) to obtain the value of the split month corresponding to the specified date. This function was then used to obtain statistical summary data from a business department for a non-standard month (the standard month forms a standard month from the first day of each month to the last day. For example, if the index value of the start day of the specified split month is 5, it indicates that the number 5 of a month to the number 4 of the next month is used as a complete split month; similarly, if the index value of the start day of the specified month is 1, it indicates the standard month and so on.

I have carefully reviewed this function for simplified reconstruction and expansion, the difference between the implementation of the custom split-month function the SQL Server time granularity series written earlier-the 3rd S, the monthly time granularity detailed article will show an integer and the month and date conversion function, this is implemented according to the standard month. Although the idea is roughly the same, it is not extended for the previous month and date and the Integer Conversion Function. Instead, it is developed independently. It is also designed to ensure uniformity, stability, maintainability, and scalability of function functions.

2. SQL server supports custom month Segmentation

The custom month division function includes two scalar functions: ufn_SegMonths and ufn_SegMonth2Date. Ufn_SegMonths gets the value of the month corresponding to the specified date in the Custom split month; ufn_SegMonth2Date gets the month date corresponding to the specified split month value bet.

SQL server version of the implementation of T-SQL code is as follows:

IF OBJECT_ID (n' [dbo]. [ufn_SegMonths] ', 'fn') is not nullbegin drop function [dbo]. [ufn_SegMonths]; ENDGO -- ======================================================== -- function: obtains the number of custom months of the specified date based on the index value starting from the custom month. -- Note: The number of custom split months = the annual integer x 100 + the value of the current split month. -- Environment: SQL Server 2005 +. -- Call: SET @ intSegMonths = dbo. fn_SegMonths ('2017-01-14 ', 15 ). -- Create: XXXX-XX-XX XX: XX-XX: xx xxx create function implementation. -- Modify: XXXX-XX-XX XX: XX-XX: xx xxx xxxxxxxx. -- ====================================== Create function [dbo]. [ufn_SegMonths] (@ dtmDate as datetime -- date, @ tntSegStartIndexOfMonth as int = 15 -- custom split month start index value (1-31 )) returns intasbegin if (@ tntSegStartIndexOfMonth = 0 OR @ expiration> = 32) begin set @ expiration = 15; end declare @ intYears as int, @ tntMonth as tinyint, @ sntDay as smallint; SELECT @ intYears = DATEDIFF (YEAR, '20140901' -01-01 ', @ dtmDate), @ tntMonth = DATEPART (MONTH, @ dtmDate), @ sntDay = DATEPART (DAY, @ dtmDate); IF (@ sntDay >=@ tntSegStartIndexOfMonth) begin set @ tntMonth = @ tntMonth + 1; end if (@ tntMonth> 12) begin select @ intYears = @ intYears + 1, @ tntMonth = @ tntMonth-12; end return @ intYears * 100 + @ tntMonth; endgo if OBJECT_ID (n' [dbo]. [ufn_SegMonths2Date] ', 'fn') is not nullbegin drop function [dbo]. [ufn _ SegMonths2Date]; ENDGO -- ======================================================== -- function: obtain the date of the custom split month corresponding to the number of custom split months. -- Description: Custom split month date = custom split month number/100 corresponding to the year integer date "Combination" currently in the split month value. -- Environment: SQL Server 2005 +. -- Call: SET @ dtmSegMonthDate = dbo. fn_SegMonths2Date (11602 ). -- Create: XXXX-XX-XX XX: XX-XX: xx xxx create function implementation. -- Modify: XXXX-XX-XX XX: XX-XX: xx xxx xxxxxxxx .; -- ====================================== Create function [dbo]. [intervals] (@ intSegMonths as int -- custom Number of split months) returns datetimeasbegin declare @ dtmdefabasebasedate as datetime; SET @ dtmdefabasebasedate = '2017-01-01 '; IF (@ intSegMonths is null) OR (@ intSegMonths <= 0) begin return @ dtmdefabasebasedate; end declare @ intYears as int and @ intMonth as int; SELECT @ intYears = @ intSegMonths/100, @ intMonth = @ intSegMonths % 100; return dateadd (MONTH, @ intMonth-1, DATEADD (YEAR, @ intYears, @ dtmdefabasebasedate); ENDGO

3. test and verify the results

The simple test code is as follows:

DECLARE   @dtmStartDate AS DATETIME  ,@dtmEndDate AS DATETIME; SELECT   @dtmStartDate = '2000-01-01'  ,@dtmEndDate = '2016-12-31'; SELECT  [T1].*  ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDateFROM (  SELECT    [T].[CDate]    ,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths   FROM (    SELECT      DATEADD(DAY, [Num], @dtmStartDate) AS CDate    FROM      [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))  ) AS T  WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31') AS T1WHERE DATEPART(DAY, [T1].[CDate]) >= 27GO

The effect is as follows:

Note: The above test code uses the SQL Server digital auxiliary table implementation. The Inline Table value function ufn_GetNums in this article.

4. Summary

This is a simplified reconstruction and expansion Implementation of functional functions of the Organization platform. Sort out date-related functions as much as possible to facilitate use in SQL server user databases and BI warehouses. It has been the past week since the National Day. The original plan for one week is postponed, and I will seriously review myself again.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!