本帖最后由 丁学杰 于 2018-9-18 20:36 编辑
生成连续的日期总结
网上很多例子都是通过Sql Server 的内置表 master..spt_values 的利用来实现,在快表软件中的表间公式中不支持使用Sql Server 的内置表调用,这种方式也就空谈了。本文根据笔者在实际中使用的两种方式分享给大家。
一、建立测试模板
1. 新建模板,命名[连续日期示例]
2. 定义单一数据项[开始日期]和[结束日期]
3. 定义重复数据项[连续日期]
二、利用ROW_NUMBER实现
1. 添加按钮:“利用ROW_NUMBER实现”
2. 新建表间公式,名称 “利用 ROW_NUMBER 实现”
基本信息:
(1)执行时机勾选 “按钮/标签执行”,选择 “利用ROW_NUMBER实现”
(2)扩展控制勾选“执行时删除空行”、“执行时清空所有区域”
筛选填充:
(1)填充数据表选择“连续日期示例_明细表”(注意,否则不会填充明细表)
(2)勾选开启SQL,开启表间公式SQL模式,将下列代码录入编辑框内
declare @d1 datetime /* 定义开始日期变量 */
declare @d2 datetime /* 定义结束日期变量 */
set @d1 = 本报表.[连续日期示例_主表].[开始日期]
set @d2 = 本报表.[连续日期示例_主表].[结束日期]
SELECT
TOP( DATEDIFF(DAY, @d1, @d2 ) + 1 )
DATEADD( DAY,ROW_NUMBER() OVER(ORDER BY LID) - 1 ,@d1 ) AS 本报表.[连续日期示例_明细表].[连续日期]x填充不锁定
FROM SYS_LOG
注:本例使用了系统日志表SYS_LOG和列LID排序得到的,用户也可以使用其他辅助表,但辅助表的行数应超过预计生成的行数
三、利用XML实现
1. 添加按钮:“利用XML实现”
2. 新建表间公式,名称 “利用XML实现”
将下列代码录入编辑框内
DECLARE @d1 DATETIME /* 定义开始日期变量 */
DECLARE @d2 DATETIME /* 定义结束日期变量 */
DECLARE @num INTEGER
DECLARE @Data VARCHAR(max)
DECLARE @XML xml
SET @d1 = 本报表.[连续日期示例_主表].[开始日期]
SET @d2 = 本报表.[连续日期示例_主表].[结束日期]
SET @num = 0;
SET @Data = '';
WHILE @num<(日期间隔(DAY,@d1,@d2)+1)
BEGIN
SET @Data = @Data + '<row num="' + 日期转字符(VARCHAR(10), 日期加减(DAY,@num,@d1),120) +'" />';
SET @num = @num + 1;
END
SET @XML = 数值转字符(@Data as xml);
SELECT T.C.value('./@num','VARCHAR(30)') AS 本报表.[连续日期示例_明细表].[连续日期]x填充不锁定
FROM @XML.nodes('/row') T(C)
四、测试
保存后,改变开始时间和结束时间,可以得到连续的日期,如下图所示
五、总结
通过利用SQL SERVER ROW_NUMBER函数或XML数据库操作都能实现生成连续日期的功能,但有些区别:
1. 采用ROW_NUMBER方式,需先判断某个表应满足条件的个数和至少一个字段名,笔者在 常用年月固定行报表、动态行报表和分类报表分享 就采用这种方式
2. 采用XML较为麻烦,但可以适应较大行数的需求
最后
本例若没被删除已留在 快表软件开发平台演示系统 / 设计实例 / 连续日期示例 模板 (完)
|
|
丁学杰