你的位置:首页 > 数据库

[数据库]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数字辅助表的实现这边文章的内联表值函数ufn_GetNums。

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





办理希腊签证费用出发去希腊旅游希腊旅游报价到希腊旅游团购去希腊旅游攻略2015清溪赏花节活动介绍?清溪赏花旅游节有哪些好玩的? 乳源天景山仙人桥介绍?天景山仙人桥游玩需要多长时间? 土家恩施鱼木寨被遗世的千年古寨 乳源天景山仙人桥在哪里?韶关乳源天景山仙人桥特色是什么? 信宜天马山一日游费用?天马山门票多少钱? 信宜天马山小孩多高免费?天马山儿童票价格? 信宜天马山停车方便吗?天马山怎么收费? 解密西安个性景点陕西历史博物馆 蜜月旅游 新婚蜜月旅游 夫妻蜜月旅游 旅游 国庆去塞班岛体验不一样的蜜月之旅 新马泰旅游 新加坡旅游 马来西亚旅游 泰国旅游 东南亚旅游 2013新加坡旅游 新加坡旅游 2013新加坡美食旅游 2013新加坡购物旅游 新加坡购物旅游 英德宝晶宫图片?清远英德宝晶宫有什么好玩的? 英德宝晶宫自驾游攻略?清远英德宝晶宫自驾路线? 英德宝晶宫风景名胜区怎么走?清远英德宝晶宫游玩攻略? 英德宝晶宫位置?清远英德宝晶宫详细地址? MSL-204TG Datasheet MSL-204TG Datasheet MSL-204TIWA Datasheet MSL-204TIWA Datasheet MSL-204TIWC Datasheet MSL-204TIWC Datasheet 重庆风景图片 重庆风景图片 重庆风景图片 广东温泉 广东温泉 广东温泉 白俄罗斯旅游 白俄罗斯旅游 白俄罗斯旅游