Count of records for past week.

[TamedTech]

Hello Guys,

I have a query which looks like this:

				Select	COUNT(SaleOrder_ID) AS ThisWeekCount				
				From	SaleOrder
				Where	Placed IS NOT NULL
				And		Placed >= DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)

I thought that this would count the number of sale orders placed in the past week. With Monday being the first day of the week, and Sunday being the last.

However, on a Sunday, it reads 0 all day, regardless of how many orders have been placed in the week leading up, or even on the Sunday itself. The count only appears to work Monday through Saturday.

Can anyone shed any light on this?

Thanks,

Robert

 

[markros]

Try running

select DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)

for me it returns today's Monday at midnight.

PluralSight Learning Library

 

[gmmastros]

It depends on your @@DateFirst setting.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SimonSellick]

It doesn't seem to change with @@DateFirst:

set datefirst 1	-- Mon
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 2
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 3
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 4
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 5
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 6
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum
set datefirst 7 -- Sunday (default)
select @@datefirst FirstDay, DateDiff(Week, 0, '09-Jan-2011') WeekNum

My results always show 5793 week boundaries (this is SQL Server 2008 R2). Similarly, I always get 0 weeks between two weekdays but one week if a Sat/Sun boundary is crossed. The help text talks about calendar weeks - perhaps the 'week' interval doesn't take any notice of @@DateFirst?

 

[markros]

I got the same results also

set datefirst 1
select @@DATEFIRST, DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)

for any datefirst.

PluralSight Learning Library

 

[gmmastros]

Apparently I was wrong. I know that the DatePart function uses the DateFirst setting when you use the week argument.

set datefirst 1    -- Mon
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 2
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 3
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 4
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 5
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 6
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum
set datefirst 7 -- Sunday (default)
select @@datefirst FirstDay, DatePart(Week, '09-Jan-2011') WeekNum

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SimonSellick]

This seems OK:

set datefirst 1
select @@datefirst, datepart(weekday, @d), dateadd(day, - datepart(weekday, getdate()) + 1, getdate()) WeekStarted

...to get the date of the Monday in which the week started, so you could perhaps use

Select COUNT(SaleOrder_ID) AS ThisWeekCount                
From   SaleOrder
Where  Placed IS NOT NULL
And    Placed >= dateadd(day, - datepart(weekday, getdate()) + 1, getdate())

You might need to cast the getdate() and Placed values to date to truncate the time part.

 

[TamedTech]

Hello Everyone,

Thanks very much for all the discussion, I always find working with dates a bit of a mind bender so enjoy learning this stuff.

It seems that when I run

Select DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)

I do indeed seem to get Midnight on Monday of the current week. So quite why my current query doesn't seem to work I'm not quite sure.

When I used your example @SimonSellick I seem to not get the expected result, I get a much higher count number than expected which leads me to believe it's not quite right.

Robert

 

[SimonSellick]

I don't get Monday of the current week when I run your code for a Sunday; I get Monday of the next week:

set datefirst 1
DateAdd(Week, DateDiff(Week, 0, '09-Jan-2011'), 0)

...returns 10-Jan-2011. That would explain why you cannot see the Sunday orders on Sunday.

 

[TamedTech]

Hey @SimonSellick,

Yeah I get the same thing, which would explain things. How very strange. I guess that SQL Server runs it's week from Sunday through Sunday rather than Monday through Monday as I'm trying to do.

Hmmmm.

Robert