每月日期范围替换 [英] Monthly date range substitution
问题描述
我想在两年内每个月发一份报告。我目前使用这样的日期范围
。然后手动替换每个月的error_time
边界并重新运行查询。如何编写脚本以便我可以以编程方式在循环中执行替换
。提前完成。
从application_errors中选择计数(*)
其中error_message喜欢''Time%''
和error_time> =' '1Apr2004''和error_time< ''1May2004''
I would like to run a report for each month over two years. I am currently
using a date range like this. Then manually substitute the error_time
bounds for each month and rerun the query. How can I script this so I can
programmatically perform the substitution in a loop. Thanx in advance.
select count(*) from application_errors
where error_message like ''Time%''
and error_time >= ''1Apr2004'' and error_time < ''1May2004''
推荐答案
Robert(ro*********** @ boeing.com)写道:
Robert (ro***********@boeing.com) writes:
也许这比我最初想的要容易得多:
从application_errors中选择count(*)
其中error_message如''Time%''
和error_time> =''1Jan2004''和error_time< ''JanJan2005''
逐月(error_time)
这为我节省了大量的工作。现在,如果我能弄清楚如何跨越几年
并且仍然按月分组......
Maybe this is a lot easier to do than I first thought:
select count(*) from application_errors
where error_message like ''Time%''
and error_time >= ''1Jan2004'' and error_time < ''1Jan2005''
group by month (error_time)
This saves me a lot of work. Now, if I could figure out how to span years
and still group by months...
select convert(char(6),error_time,112),count( *)来自application_errors
其中error_message喜欢''时间%''
和error_time> =''1Jan2004''
group by convert (char(6),error_time,112)
-
Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
select convert(char(6), error_time, 112), count(*) from application_errors
where error_message like ''Time%''
and error_time >= ''1Jan2004''
group by convert(char(6), error_time, 112)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Thanx埃兰,我不值得!!
E rland Sommarskog < ES **** @ sommarskog.se>在消息中写道
news:Xn ********************* @ 127.0.0.1 ...
Thanx Erland, I am not worthy!!
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Robert(ro*********** @ boeing.com)写道:
Robert (ro***********@boeing.com) writes:
也许这比我初想的要容易得多:
从application_errors中选择count(*)
其中error_message如''Time%''
和error_time> =''1Jan2004''和error_time< ''JanJan2005''
逐月(error_time)
这为我节省了大量的工作。现在,如果我能弄清楚如何跨越
年数
仍然按月分组......
Maybe this is a lot easier to do than I first thought:
select count(*) from application_errors
where error_message like ''Time%''
and error_time >= ''1Jan2004'' and error_time < ''1Jan2005''
group by month (error_time)
This saves me a lot of work. Now, if I could figure out how to span
years
and still group by months...
select convert(char(6),error_time,112 ),count(*)来自
application_errors
其中error_message喜欢''Time%''
和error_time> =''1Jan2004''
分组转换(char(6) ),error_time,112)
-
Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se
SQL Server 2005的在线书籍
http://www.microsoft.com/technet/pro...ads/books.mspx
SQL联机丛书服务器2000在
http://www.microsoft .com / sql / prodinf ... ons / books.mspx
Robert写道:
比x埃兰,我不配!
您也可以使用DATEPART从时间戳中提取年份和月份
列。
robert
Erland Sommarskog < ES **** @ sommarskog.se>在消息中写道
新闻:Xn ********************* @ 127.0.0.1 ...
Thanx Erland, I am not worthy!!
You can as well use DATEPART to extract year and month from the timestamp
column.
robert
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...
Robert(ro * **********@boeing.com)写道:
Robert (ro***********@boeing.com) writes:
也许这比我最初想的要容易得多:
选择计数(* )from application_errors
其中error_message喜欢''Time%''
和error_time> =''1Jan2004''和error_time< ''JanJan2005''
逐月(error_time)
这为我节省了大量的工作。现在,如果我能弄清楚如何跨越
年数
仍然按月分组......
Maybe this is a lot easier to do than I first thought:
select count(*) from application_errors
where error_message like ''Time%''
and error_time >= ''1Jan2004'' and error_time < ''1Jan2005''
group by month (error_time)
This saves me a lot of work. Now, if I could figure out how to span
years
and still group by months...
select convert(char(6),error_time,112 ),count(*)来自
application_errors
其中error_message喜欢''Time%''
和error_time> =''1Jan2004''
分组转换(char(6) ),error_time,112)
-
Erland Sommarskog,SQL Server MVP, es **** @ sommarskog.se
SQL Server 2005的在线书籍
http://www.microsoft.com/technet/pro...ads/books.mspx 适用于SQL Server 2000的联机丛书
http://www.microsoft.com/sql /prodinf...ons/books.mspx
select convert(char(6), error_time, 112), count(*) from
application_errors
where error_message like ''Time%''
and error_time >= ''1Jan2004''
group by convert(char(6), error_time, 112)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
这篇关于每月日期范围替换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!