問題描述
我目前正在編寫查詢以生成每周一到下周日的每周報告.
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'
上面的查詢有什么問題.第一個和第二個查詢中的計數(shù)加起來是不同的數(shù)字.
What is wrong with the above query. The counts add up to a different number in the first and second queries.
推薦答案
在沒有特別理解查詢的情況下(因為我通常使用比 tsql 使用的更標準的 SQL 變體編寫)，我在查看查詢時的直覺反應(yīng)是這可能是時區(qū)問題.但是，您需要做的是驗證您得到的答案是否符合您的預(yù)期.為此，請運行:
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'
如果這是太多數(shù)據(jù)，這里有另一種選擇:
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
及相關(guān):
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
運行這些查詢應(yīng)該可以幫助您了解EveryMonday"和EverySunday"查詢是否生成了您期望的值.查看最小/最大日期將有助于您了解何時發(fā)生不匹配.
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.
這篇關(guān)于查詢周報的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！