Query for weekly report(查询周报)
问题描述
我目前正在编写查询以生成每周一到下周日的每周报告.
I am currently writing a query to generate a weekly report from every Monday to the next Sunday.
SELECT top 10
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday, count(items)
FROM myitemtable
GROUP BY
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
对比
SELECT count(items)
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'
上面的查询有什么问题.第一个和第二个查询中的计数加起来是不同的数字.
What is wrong with the above query. The counts add up to a different number in the first and second queries.
推荐答案
在没有特别理解查询的情况下(因为我通常使用比 tsql 使用的更标准的 SQL 变体编写),我在查看查询时的直觉反应是这可能是时区问题.但是,您需要做的是验证您得到的答案是否符合您的预期.为此,请运行:
Without particularly understanding the query (since I normally write in a far more standard variant of SQL than tsql uses), my gut reaction when looking at your query was that this could be a timezone problem. But, what you need to do is verify the answers you are getting are what you expect. To do this, run:
SELECT mydatefield,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'
如果这是太多数据,这里有另一种选择:
if this is too much data, here is another option:
SELECT min(mydatefield),max(mydatefield),count(*),
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
WHERE mydatefield >= '2011/05/30 00.00.000' and mydatefield <= '2011/06/05 23.59.59'
GROUP BY
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
及相关:
SELECT min(mydatefield),max(mydatefield),count(*),
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
FROM myitemtable
GROUP BY
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield), '19000101') as EveryMonday,
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday
运行这些查询应该可以帮助您了解EveryMonday"和EverySunday"查询是否生成了您期望的值.查看最小/最大日期将有助于您了解何时发生不匹配.
Running these queries should help you understand if the "EveryMonday" and "EverySunday" queries are generating the values you expect. Seeing the min/max dates will help you understand when the mismatches are occurring.
这篇关于查询周报的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:查询周报
基础教程推荐
- Sql Server 字符串到日期的转换 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01