Grouping data per week instead of month

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

General SQL Server Forums

New to SQL Server Programming

Grouping data per week instead of month

Author

Topic

viguro
Starting Member

12 Posts

Posted - 2010-09-03 : 12:31:54

Hello All,

I need to create a report for HR that involves grouping the hours par week instead of monthly. the Month() function works well for monthly grouping but how to efficiently group per week.

any help is appreciated,
thanks.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-09-03 : 13:16:51

instead of using:

select month(getdate()), sum(hours) from MyTable group by month(getdate())

use

select datepart(week, getdate()), sum(hours) from MyTable group by datepart(week,getdate())

viguro
Starting Member

12 Posts

Posted - 2010-09-03 : 16:13:12

thank you Slimt_slimt, I can work from this.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-04 : 01:09:25

quote:
Originally posted by viguro

Hello All,

I need to create a report for HR that involves grouping the hours per week instead of monthly. the Month() function works well for monthly grouping but how to efficiently group per week.

Quit thinking like a procedural programmer and think like a DATA BASE PROGRAMMER! Build a table of reporting periods (period_name, start_date, end_date) and use a BETWEEN predicate to find where an event falls.

Using proprietary functions prevents indexes, portability, etc.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources