Grouping By 7 Day Periods - 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 2000 Forums

Transact-SQL (2000)

Grouping By 7 Day Periods

Author

Topic

gad
Starting Member

14 Posts

Posted - 2006-03-02 : 19:32:36

Previously had assistance in creating a query that grouped data into 7 day buckets (Mon - Sun) using the following method;

dateadd(day,(datediff(day, 0, mydatefield)/7)*7, 0)

I'm now attempting to modify this to group on ANY 7 day period (Fri - Thur etc.).

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-02 : 21:10:53

See this

select	Sun, datename(weekday, Sun),
	Mon, datename(weekday, Mon),
	Tue, datename(weekday, Tue),
	Wed, datename(weekday, Wed),
	Thu, datename(weekday, Thu),
	Fri, datename(weekday, Fri),
	Sat, datename(weekday, Sat)
from
(
	select	dateadd(day, -1 * (datepart(weekday, dte) - 1 - 0), dte) as Sun,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 1), dte) as Mon,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 2), dte) as Tue,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 3), dte) as Wed,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 4), dte) as Thu,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 5), dte) as Fri,
		dateadd(day, -1 * (datepart(weekday, dte) - 1 - 6), dte) as Sat
	from
	(
		select	convert(datetime, '2006-02-01') as dte	union all
		select	convert(datetime, '2006-02-02') as dte	union all
		select	convert(datetime, '2006-02-03') as dte	union all
		select	convert(datetime, '2006-02-04') as dte	union all
		select	convert(datetime, '2006-02-05') as dte	union all
		select	convert(datetime, '2006-02-06') as dte	union all
		select	convert(datetime, '2006-02-07') as dte	union all
		select	convert(datetime, '2006-02-08') as dte	union all
		select	convert(datetime, '2006-02-09') as dte	union all
		select	convert(datetime, '2006-02-10') as dte
	) dates
) dates1

----------------------------------
'KH'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-02 : 21:27:27

You can still use the same formula just pick a different starting point (@weekday)



declare @weekday int
set @weekday = 0 --monday
set @weekday = 2 --tuesday
--...
set @weekday = 6 --sunday

select	min(dte) startDate
	,max(dte) endDate
	,count(*) datecount
from
	(	select	convert(datetime, '2006-02-01') as dte	union all
		select	convert(datetime, '2006-02-02') as dte	union all
		select	convert(datetime, '2006-02-03') as dte	union all
		select	convert(datetime, '2006-02-04') as dte	union all
		select	convert(datetime, '2006-02-05') as dte	union all
		select	convert(datetime, '2006-02-06') as dte	union all
		select	convert(datetime, '2006-02-07') as dte	union all
		select	convert(datetime, '2006-02-08') as dte	union all
		select	convert(datetime, '2006-02-09') as dte	union all
		select	convert(datetime, '2006-02-10') as dte
	) dates
group by datediff(day, @weekday, dte)/7


output:
startDate                                              endDate                                                datecount   
------------------------------------------------------ ------------------------------------------------------ ----------- 
2006-02-01 00:00:00.000                                2006-02-04 00:00:00.000                                4
2006-02-05 00:00:00.000                                2006-02-10 00:00:00.000                                6

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-02 : 21:52:33

You can use the Date Table Function F_TABLE_DATE in this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

You can join the output from this function to any date or datetime column in a table.

There are columns for weeks starting any day of the week. You can also look in the code to see how they are calculated:

START_OF_WEEK_STARTING_SUN_DATE 
	First Day of Week starting Sunday that DATE is in

START_OF_WEEK_STARTING_MON_DATE 
	First Day of Week starting Monday that DATE is in

START_OF_WEEK_STARTING_TUE_DATE 
	First Day of Week starting Tuesday that DATE is in

etc.

CODO ERGO SUM

gad
Starting Member

14 Posts

Posted - 2006-03-03 : 11:00:42

Thank you for all the suggestions - I think I see the light.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources