sql server 自界說朋分月功效詳解及完成代碼

sql server 自界說朋分月功效詳解及完成代碼。本站提示廣大學習愛好者：（sql server 自界說朋分月功效詳解及完成代碼）文章只能為提供參考，不一定能成為您想要的結果。以下是sql server 自界說朋分月功效詳解及完成代碼正文

在比來的項目開辟進程中，碰到了Sql server主動朋分月的功效需求，這裡在網上整頓下材料.      

1、為什麼湧現自界說朋分月的需求

明天梳理一個平台的一切函數時，發明了一個自界說朋分月函數，也就是指定朋分月的開端日索引值（可以從1-31閉區間內的任何一個值）來獲得指定日期所對應的朋分月數值。這個函數其時是為懂得決營業部分獲得非尺度月（尺度月就是從每一個月的第一天到最初一天構成一個完成的尺度月份）的統計匯總數據的。例如：假如指定朋分月的開端日索引值為5則表現某個月的5號到下個月的4號之間作為一個完全的朋分月；異樣地假如指定朋分月的開端日索引值為1則表現尺度月等等。 

我細心梳理了這個函數停止了重構簡化和擴大，該自界說朋分月函數的完成差別之前寫的SQL Server時光粒度系列----第3節旬、月時光粒度詳解文章中將一個整數值和月份日期互相轉換功效，這個是依照尺度月來完成的，固然思緒年夜致雷同，然則並沒有針對之前的月份日期和整數值轉換函數對來停止擴大而是自力開辟新的功效函數。也是為了盡可能做到函數功效職責單一性、穩固性、可保護性和可擴大性。 

2、sql server完成自界說朋分月功效 

自界說朋分月功效函數包含兩個標量函數：ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths獲得指定的日期在自界說朋分月對應的朋分月數值；ufn_SegMonth2Date獲得指定一個朋分月數值賭對應的月份日期。 

sql server 版本的完成T-SQL代碼以下：

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths];
END
GO
 
--==================================
-- 功效：依據自界說月開端索引值獲得指定日期地點的自界說月數。
-- 解釋：自界說朋分月數 = 年整數值*100 + 以後地點朋分月值。
-- 情況：SQL Server 2005+。
-- 挪用：SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。
-- 創立：XXXX-XX-XX XX:XX-XX:XX XXX 創立函數完成。
-- 修正：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths]
(
   @dtmDate AS DATETIME            -- 日期
  ,@tntSegStartIndexOfMonth AS INT = 15    -- 自界說朋分月開端索引值（1-31)
)
RETURNS INT
AS
BEGIN  
  IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)
  BEGIN
    SET @tntSegStartIndexOfMonth = 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 = 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;
END
GO
 
IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL
BEGIN
  DROP FUNCTION [dbo].[ufn_SegMonths2Date];
END
GO
 
--==================================
-- 功效：獲得自界說朋分月數對應的自界說朋分月日期。
-- 解釋：自界說朋分月日期 = 自界說朋分月數/100對應的年整很多天期“組合”以後地點朋分月值。
-- 情況：SQL Server 2005+。
-- 挪用：SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。
-- 創立：XXXX-XX-XX XX:XX-XX:XX XXX 創立函數完成。
-- 修正：XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths2Date]
(
   @intSegMonths AS INT            -- 自界說朋分月數
)
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、測實驗證後果

 針對以上簡略的測試代碼以下：

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], 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 T1
WHERE DATEPART(DAY, [T1].[CDate]) >= 27
GO

後果截圖以下：

 留意：以上測試代碼應用了SQL Server數字幫助表的完成這邊文章的內聯表值函數ufn_GetNums。

 4、總結語

此次是梳理平台的功效性函數所停止的重構簡化和擴大的完成。盡可能將日期有關的功效函數梳理出來，便於直接在sql server用戶數據庫中來應用， 也便於BI倉庫中應用。國慶一來曾經曩昔一周，本來盤算一周一遍的籌劃照樣延期啦，再次嚴重檢查本身。

  感激浏覽，願望能贊助到年夜家，感謝年夜家對本站的支撐！