SQL Server custom split month features detailed and implementation code _MSSQL

In the recent project development process, encountered SQL Server automatically split the monthly function requirements, here online collation of information.

1, why the need for custom split month

Today, when you comb through all the functions of a platform, you find a custom split-month function, which is to specify the date of the start of the split month (which can be from any value in the 1-31 closed range) to get the value of the split month for the specified dates. This function was designed to solve the statistical summary of a non-standard month (standard month from the first day of each month to the last day of a completed standard month) for the business unit. For example, if you specify that the start-day index of the split month is 5, then a month's 5th to the 4th of the next month as a complete split month, as well as a standard month and so on if the start date index of the specified split month is 1.

I combed through this function for refactoring simplification and scaling, the implementation of the custom split month function is written before the SQL Server time granularity series----the 3rd, month time granularity detailed article will be an integer value and the month date conversion function, this is according to the standard month to achieve, Although the idea is roughly the same, it does not extend to the previous month date and the integer value conversion function pair, but develops the new functional function independently. It is also to try to achieve the function of functional function singleness, stability, maintainability and scalability.

2. SQL Server implements custom split month function

The custom Split month function function includes two scalar functions: Ufn_segmonths and Ufn_segmonth2date. Ufn_segmonths gets the value of the split month corresponding to the custom split month for the specified date; Ufn_segmonth2date Gets the month date that specifies a split-month value bet.

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

IF object_id (N ' [dbo].[ Ufn_segmonths] ', ' FN ') is not NULL BEGIN DROP FUNCTION [dbo].
[Ufn_segmonths];
End Go--==================================-function: Gets the number of custom months that the specified date is based on the custom month start index value.
--Description: Custom Split month = year integer value *100 + current partition month value.
-Environment: SQL Server 2005+.
--Call: SET @intSegMonths = dbo.fn_segmonths (' 2008-01-14 ', 15).
--Create: xxxx-xx-xx xx:xx-xx:xx XXX create function implementation.
--Modification: 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 INT as begin IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >=) begin SET @tntSegSta
  Rtindexofmonth = 15;    
  End DECLARE @intYears as INT, @tntMonth as TINYINT, @sntDay as SMALLINT; SELECT @intYears = DATEDIFF (year, ' 1900-01-01 ', @dtmDate), @tntMonth = DATEPART (MONTH, @dtmDate), @sntDay = D
 
  Atepart (day, @dtmDate); IF (@sntDay >= @tntSegStartIndexOFmonth) BEGIN SET @tntMonth = @tntMonth + 1;
  End IF (@tntMonth >) BEGIN SELECT @intYears = @intYears + 1, @tntMonth = @tntMonth-12;
End return @intYears * + @tntMonth; End Go IF object_id (N ' [dbo].[ Ufn_segmonths2date] ', ' FN ') is not NULL BEGIN DROP FUNCTION [dbo].
[Ufn_segmonths2date];
End Go--==================================-function: Gets the custom split month date that corresponds to the custom split month number.
--Description: Custom Split Month date = Custom Split month/100 corresponding year integer date "combination" is the current 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.
--Modification: xxxx-xx-xx xx:xx-xx:xx XXX XXXXXXXX.; --================================== CREATE FUNCTION [dbo].
[Ufn_segmonths2date]
(@intSegMonths as INT--Customizing the number of split months)
  RETURNS datetime as BEGIN DECLARE @dtmDefaultBasedate as DATETIME;
 
  SET @dtmDefaultBasedate = ' 1900-01-01 ';
  IF (@intSegMonths is NULL) OR (@intSegMonths <= 0)), BEGIN return @dtmDefaultBasedate;
     End DECLARE@intYears as int, @intMonth as int;  
 
  SELECT @intYears = @intSegMonths/100, @intMonth = @intSegMonths% 100;
Return DATEADD (MONTH, @intMonth-1, DATEADD (year, @intYears, @dtmDefaultBasedate));
 

 End Go

3. Test verification Effect

For the above 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 Segmonthdate from
(
  SELECT
    [t].[ CDate]
    , [dbo].[ Ufn_segmonths] ([t].[ CDate] 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 T1 WHERE DATEPART (Day, [t1].[ CDate]) >= go

The effect screenshot is as follows:

Note: The above test code uses the SQL Server Digital Helper table to implement the inline table-valued function of this article ufn_getnums.

4. Summary language

This is the function of the carding platform to simplify the reconfiguration and the implementation of the extension. Try to sort out the functional functions related to the date so that they can be used directly in the SQL Server user database and also in the BI warehouse. National Day has been a week, the original plan to go over the schedule or postponed, again seriously review their own.

Thank you for reading, I hope to help you, thank you for your support for this site!