星空网 > 软件开发 > 数据库

sql server实现自定义分割月功能

本文目录列表:
1、为何出现自定义分割月需求
2、sql server实现自定义分割月功能
3、测试验证效果
4、总结语
5、参考清单列表

 
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 NULLBEGIN  DROP FUNCTION [dbo].[ufn_SegMonths];ENDGO --==================================-- 功能:根据自定义月开始索引值获取指定日期所在的自定义月数。-- 说明:自定义分割月数 = 年整数值*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 INTASBEGIN    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;ENDGO IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULLBEGIN  DROP FUNCTION [dbo].[ufn_SegMonths2Date];ENDGO --==================================-- 功能:获取自定义分割月数对应的自定义分割月日期。-- 说明:自定义分割月日期 = 自定义分割月数/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 DATETIMEASBEGIN      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));ENDGO

 


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 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

效果截图如下:


sql server实现自定义分割月功能
sql server实现自定义分割月功能
 
注意:以上测试代码使用了SQL Server数字辅助表的实现这边文章的内联表值函数ufn_GetNums。

 
4、总结语
 
这次是梳理平台的功能性函数所进行的重构简化以及扩展的实现。尽量将日期有关的功能函数梳理出来,便于直接在sql server用户数据库中来使用, 也便于BI仓库中使用。国庆一来已经过去一周,原来打算一周一遍的计划还是延期啦,再次严重检讨自己。
 
继续精进SQL Server,继续进发。
 
5、参考清单列表
  • SQL Server数字辅助表的实现
  • SQL Server时间粒度系列----第3节旬、月时间粒度详解 





原标题:sql server实现自定义分割月功能

关键词:sql

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流