SQL Tips by Namwar Rizvi

June 8, 2007

Finding Start date of the week for the given date

Filed under: Query,tips,TSQL — namwar @ 9:31 PM

Counting total orders of each week is a common requirement of sales applications. In a normalized database you always have OrderHeader table which contains a row for each new order. OrderDate column is normally used to record the date of the order. In this scenario, if you want to find the total no. of orders in a given week then you need to calculate the WeekStartDate for each order date then only you can group them on weekly basis.
Following example shows you the quick way to find the start date of the week for the given date:

Use Adventureworks
Go

Set datefirst 1 –Monday is the first day of the week.
Go

Select
dateadd(day,-1 * ( DATEPART(weekday, OrderDate)-1 ),OrderDate) WeekStartDate,
count(OrderDate) TotalOrders
from Sales.SalesOrderHeader
Group by dateadd(day,-1 * ( DATEPART(weekday, OrderDate)-1 ),OrderDate)
Order by dateadd(day,-1 * ( DATEPART(weekday, OrderDate)-1 ),OrderDate)

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: