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', 开发者_JS百科mydatefield), '19000101') as EveryMonday, 
 DATEADD(WEEK, DATEDIFF(WEEK, '19000101', mydatefield)+1, '19000101')-1 as EverySunday    

vs

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.

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

and the related:

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

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.