Week Commencing Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Week Commencing Count

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-05-05 : 05:49:43

Hi Guys

I have a table containing order information.

An example is shown below (trimmed down version):

OrderID, DateEntered
1, 2009-04-06
2, 2009-04-06
3, 2009-04-08
4, 2009-04 10
5, 2009-04-12
6, 2009-04-13
7, 2009-04-15
8,2009-04-16
Etc.

What I need to do is produce a query that will return a count of orders by week commencing date. With the first day being a Monday.

Eg.
WeekCommencing, Count
2009-04-06, 4
2009-04-13, 20
2009-04-20, 25
2009-04-27, 33
Etc.

Is this possible to do?

Thanking you in advance!!!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-05-05 : 06:04:58

Try this:


select count(1) CountPerWeek, DATEADD(wk, DATEDIFF(wk,0,DateEntered), 0) WeekCommencing
from Table
Group by DATEADD(wk, DATEDIFF(wk,0,DateEntered), 0)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-05-05 : 06:19:03

Hey

Thanks for that!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources