Query from beginning of this calendar week

[HestonJames]

Hello Guys,

I have a query which looks something like this:

Select	COUNT(SaleOrder_ID) AS ThisWeekByMemberCount				
From	SaleOrder
Where	Placed IS NOT NULL
And	Placed [b]after start of current calendar week[/b]

Can someone help me complete the puzzle? I'm used to using DateAdd and could quite easily complete the query to grab orders placed in the last 7 days however I'm looking to grab orders placed since the start of the current calendar week, something of which I'm not familiar.

Thanks,

Heston

 

[gmmastros]

Try this:


And SaleOrder_Date >= DateAdd(Week, DateDiff(Week, 0, GetDate()), 0)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

George:

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

returns
2010-03-22 00:00:00.000

What about sunday Sales

 

[djj55]

From faq183-6628

-- Start of the week (this depends on your @@DateFirst setting)
SELECT DATEADD(DAY, -(DATEPART(dw,GETDATE())-1),GETDATE())

returned 2010-03-21 13:21:50.910


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[r937]

... since the start of the current calendar week"

what day of the week does your calendar week start on?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[sparkbyte]

Haven't test this yet but should be close.

SET DATEFIRST 1 -- Monday
Select    COUNT(SaleOrder_ID) AS ThisWeekByMemberCount                
From    SaleOrder
Where    Placed IS NOT NULL
And    Placed between @@DATEFIRST and DATEPART(dw, GETDATE())

Value First day of the week is
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday (default, U.S. English)

http://msdn.microsoft.com/en-us/library/ms181598(SQL.90).aspx

Thanks

John Fuhrman

 

[sparkbyte]

this should do it.

SET DATEFIRST 1 -- Monday
Select	COUNT(SaleOrder_ID) AS ThisWeekByMemberCount                
From	SaleOrder
Where	Placed IS NOT NULL
And		Placed between dateadd(dd, (datepart(dw, GetDate()) * -1) + 2, GetDate())
And		getdate()

Set the DATEFIRST to reflect your workweek.


Thanks

John Fuhrman