ReportServer 2008 R2从SSRS DB获取人类可读的日程安排信息
ReportServer 2008 R2从SSRS DB获取人类可读的日程安排信息
提问于 2011-12-01 07:39:54
我希望使用t-sql从ReportServer.dbo.Schedule表中提取“人类可读”的时间表信息。
下面是一个“人类可读”的例子。
At 6:02 AM every Sun, Mon, Tue, Wed, Thu, Fri, Sat of every week, starting 2/28/2011
表中有一堆用于存储时间表的数字字段，但我想将这些字段转换为单词，如我的示例所示。
有没有人用报告服务做到了这一点？
回答 4
Stack Overflow用户
回答已采纳
发布于 2011-12-01 08:31:35
SQL对于字符串操作或按位操作不是很好，解析这个表需要适度的这两者。我确信SSRS在SQL中不能做到这一点:我可能只用了C#一半的时间和一半的代码行。
USE ReportServer;
WITH EnhancedSched
AS (
SELECT
dbo.Schedule.ScheduleID ,
dbo.Schedule.Name ,
dbo.Schedule.StartDate ,
dbo.Schedule.Flags ,
dbo.Schedule.NextRunTime ,
dbo.Schedule.LastRunTime ,
dbo.Schedule.EndDate ,
dbo.Schedule.RecurrenceType ,
dbo.Schedule.MinutesInterval ,
dbo.Schedule.DaysInterval ,
dbo.Schedule.WeeksInterval ,
dbo.Schedule.DaysOfWeek ,
dbo.Schedule.DaysOfMonth ,
dbo.Schedule.Month ,
dbo.Schedule.MonthlyWeek ,
dbo.Schedule.State ,
dbo.Schedule.LastRunStatus ,
dbo.Schedule.ScheduledRunTimeout ,
dbo.Schedule.CreatedById ,
dbo.Schedule.EventType ,
dbo.Schedule.EventData ,
dbo.Schedule.Type ,
dbo.Schedule.ConsistancyCheck ,
dbo.Schedule.Path ,
CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, '
ELSE ''
END + CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, '
ELSE ''
END + CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, '
ELSE ''
END + CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, '
ELSE ''
END
+ CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, '
ELSE ''
END + CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, '
ELSE ''
END + CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, '
ELSE ''
END AS DaysOfWeekString ,
CASE WHEN DaysOfMonth & 1 <> 0 THEN '1,'
ELSE ''
END + CASE WHEN DaysOfMonth & 2 <> 0 THEN '2,'
ELSE ''
END + CASE WHEN DaysOfMonth & 4 <> 0 THEN '3,'
ELSE ''
END + CASE WHEN DaysOfMonth & 8 <> 0 THEN '4,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 16 <> 0 THEN '5,'
ELSE ''
END + CASE WHEN DaysOfMonth & 32 <> 0 THEN '6,'
ELSE ''
END + CASE WHEN DaysOfMonth & 64 <> 0 THEN '7,'
ELSE ''
END + CASE WHEN DaysOfMonth & 128 <> 0 THEN '8,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 256 <> 0 THEN '9,'
ELSE ''
END + CASE WHEN DaysOfMonth & 512 <> 0 THEN '10,'
ELSE ''
END + CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12,'
ELSE ''
END + CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13,'
ELSE ''
END + CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15,'
ELSE ''
END + CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16,'
ELSE ''
END + CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18,'
ELSE ''
END + CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19,'
ELSE ''
END + CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21,'
ELSE ''
END + CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22,'
ELSE ''
END + CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24,'
ELSE ''
END + CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25,'
ELSE ''
END + CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27,'
ELSE ''
END + CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29,'
ELSE ''
END + CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30,'
ELSE ''
END
+ CASE WHEN DaysOfMonth & 1073741824 <> 0 THEN '31,'
ELSE ''
END AS DaysOfMonthString ,
CASE WHEN Month = 4095 THEN 'every month, '
ELSE CASE WHEN Month & 1 <> 0 THEN 'Jan, '
ELSE ''
END + CASE WHEN Month & 2 <> 0 THEN 'Feb, '
ELSE ''
END + CASE WHEN Month & 4 <> 0 THEN 'Mar, '
ELSE ''
END
+ CASE WHEN Month & 8 <> 0 THEN 'Apr, '
ELSE ''
END + CASE WHEN Month & 16 <> 0 THEN 'May, '
ELSE ''
END + CASE WHEN Month & 32 <> 0 THEN 'Jun, '
ELSE ''
END
+ CASE WHEN Month & 64 <> 0 THEN 'Jul, '
ELSE ''
END + CASE WHEN Month & 128 <> 0 THEN 'Aug, '
ELSE ''
END
+ CASE WHEN Month & 256 <> 0 THEN 'Sep, '
ELSE ''
END + CASE WHEN Month & 512 <> 0 THEN 'Oct, '
ELSE ''
END
+ CASE WHEN Month & 1024 <> 0 THEN 'Nov, '
ELSE ''
END + CASE WHEN Month & 2048 <> 0 THEN 'Dec, '
ELSE ''
END
END AS MonthString ,
CASE MonthlyWeek
WHEN 1 THEN 'first'
WHEN 2 THEN 'second'
WHEN 3 THEN 'third'
WHEN 4 THEN 'fourth'
WHEN 5 THEN 'last'
END AS MonthlyWeekString ,
' starting ' + CONVERT (VARCHAR, StartDate, 101)
+ CASE WHEN EndDate IS NOT NULL
THEN ' and ending ' + CONVERT (VARCHAR, EndDate, 101)
ELSE ''
END AS StartEndString ,
CASE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12)
WHEN 0 THEN '12'
ELSE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12)
END + ':'
+ CASE WHEN DATEPART(MINUTE, StartDate) < 10
THEN '0' + CONVERT(VARCHAR(2), DATEPART(MINUTE,
StartDate))
ELSE CONVERT(VARCHAR(2), DATEPART(MINUTE, StartDate))
END + CASE WHEN DATEPART(HOUR, StartDate) >= 12 THEN ' PM'
ELSE ' AM'
END AS StartTime
FROM
Schedule
),
SuperEnhancedSchedule
AS (
SELECT
EnhancedSched.ScheduleID ,
EnhancedSched.Name ,
EnhancedSched.StartDate ,
EnhancedSched.Flags ,
EnhancedSched.NextRunTime ,
EnhancedSched.LastRunTime ,
EnhancedSched.EndDate ,
EnhancedSched.RecurrenceType ,
EnhancedSched.MinutesInterval ,
EnhancedSched.DaysInterval ,
EnhancedSched.WeeksInterval ,
EnhancedSched.DaysOfWeek ,
EnhancedSched.DaysOfMonth ,
EnhancedSched.Month ,
EnhancedSched.MonthlyWeek ,
EnhancedSched.State ,
EnhancedSched.LastRunStatus ,
EnhancedSched.ScheduledRunTimeout ,
EnhancedSched.CreatedById ,
EnhancedSched.EventType ,
EnhancedSched.EventData ,
EnhancedSched.Type ,
EnhancedSched.ConsistancyCheck ,
EnhancedSched.Path , -- spec what you need.
CASE WHEN RecurrenceType = 1
THEN 'At ' + StartTime + ' on '
+ CONVERT(VARCHAR, StartDate, 101)
WHEN RecurrenceType = 2
THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 ))
+ ' hour(s) and '
+ CONVERT(VARCHAR, ( MinutesInterval % 60 ))
+ ' minute(s), ' + 'starting '
+ CONVERT (VARCHAR, StartDate, 101) + ' at '
+ SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6)
+ ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109),
25, 2)
+ CASE WHEN EndDate IS NOT NULL
THEN ' and ending '
+ CONVERT (VARCHAR, EndDate, 101)
ELSE ''
END
WHEN RecurrenceType = 3
THEN 'At ' + StartTime + ' every '
+ CASE DaysInterval
WHEN 1 THEN 'day, '
ELSE CONVERT(VARCHAR, DaysInterval) + ' days, '
END + StartEndString
WHEN RecurrenceType = 4
THEN 'At ' + StartTime + ' every '
+ CASE WHEN LEN(DaysOfWeekString) > 1
THEN LEFT(DaysOfWeekString,
LEN(DaysOfWeekString) - 1)
ELSE ''
END + ' of every '
+ CASE WHEN WeeksInterval = 1 THEN ' week,'
ELSE CONVERT(VARCHAR, WeeksInterval)
+ ' weeks,'
END + StartEndString
WHEN RecurrenceType = 5
THEN 'At ' + StartTime + ' on day(s) '
+ CASE WHEN LEN(DaysOfMonthString) > 1
THEN LEFT(DaysOfMonthString,
LEN(DaysOfMonthString) - 1)
ELSE ''
END + ' of ' + MonthString + StartEndString
WHEN RecurrenceType = 6
THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString
+ ' '
+ CASE WHEN LEN(DaysOfWeekString) > 1
THEN LEFT(DaysOfWeekString,
LEN(DaysOfWeekString) - 1)
ELSE ''
END + ' of ' + MonthString + StartEndString
ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0,
6) + ' '
+ SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2)
+ StartEndString
END ScheduleTextDefinition
FROM
EnhancedSched
)
SELECT
*
-- This has the same columns as the native [dbo].Schedule table plus a field called "SheduleTextDefinition"
-- You can use "SuperEnhancedSchedule" in place of the usual SSRS.Schedule table, joining to subscriptions and such.
FROM
SuperEnhancedScheduleStack Overflow用户
发布于 2012-02-02 17:03:07
我有一个解决方案，因为它出现在我正在撰写的报告中。
create function [dbo].[calendarlist](@Value_in as int,@Type as int) returns varchar(200)
as
begin
/*
This code is to work out either the day of the week or the name of a month when given a value
Wrriten by S Manson.
31/01/2012
*/
declare @strings as varchar(200)
declare @Count int
if @Type = 2 --Months
Begin
set @Count =12
end
else if @Type = 1 --Days of Week
Begin
Set @Count = 7
End
else --Days of Month
Begin
Set @Count = 31
End
set @strings = ''
while @Count<>0
begin
if @Value_in>=(select power(2,@count-1))
begin
set @Value_in = @Value_in - (select power(2,@count-1))
If @Type=2
Begin
set @strings = (SELECT DATENAME(mm, DATEADD(month, @count-1, CAST('2008-01-01' AS datetime)))) + ',' + @strings
end
else if @Type = 1
begin
set @strings = (SELECT DATENAME(dw, DATEADD(day, @count-1, CAST('2012-01-01' AS datetime)))) + ',' + @strings
end
else
begin
set @strings = convert(varchar(2),@Count) + ', ' + @strings
end
end
set @count = @count-1
end
if right(@strings,1)=','
set @strings = left(@strings,len(@strings)-1)
return @strings
endStack Overflow用户
发布于 2014-07-08 03:13:38
这篇文章可能很老了，但今天它帮了我大忙！我找到了两个项目，我想在上面由Jamie F为他的CTE出色的帖子中添加。
缺少第31天的条目，需要将其作为EnhancedSched CTE的一部分添加，需要将以下内容添加到'DaysOfMonthString‘的末尾
+ CASE WHEN DaysOfMonth & 1073741824 <> 0 THEN '31,' ELSE '' END此外，模数为12的'StartTime‘列定义使从12开始的任何时间都是零，因此简单的英语结果显示应该是12:30 pm的开始时间为0:30 PM替换
CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12)使用
CASE CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) WHEN 0 THEN '12' ELSE CONVERT(VARCHAR, DATEPART(hour, StartDate) % 12) END为了让简单的英语开始时间正确地阅读。
非常感谢Jamie F上面的帖子，救了我一命。+1个互联网给你，好先生。
很抱歉发了额外的“答案”帖子，没有代表对上面杰米·F的优秀帖子进行评论。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/8334642
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号