Solved

SQL Query - Grouping by weekly

Posted on 2010-09-15
3
371 Views
Last Modified: 2012-05-10
Hello experts,

I need to group some data base of dates.  It needs to be grouped base on a weekly date.  See example.

Raw data:
ID      HOURS      DATE
8922      10.00      8/30/2010
8923      09.00      8/31/2010
8924      08.00      9/2/2010
8930      09.00      9/2/2010
8941      04.00      9/6/2010
8942      09.00      9/8/2010
8945      03.00      9/10/2010

Need to group it like the following:

ID      HOURS      DATE
8/30 - 9/3
8922      10.00      8/30/2010
8923      09.00      8/31/2010
8924      08.00      9/2/2010
8930      09.00      9/2/2010
9/6 - 9/10
8941      04.00      9/6/2010
8942      09.00      9/8/2010
8945      03.00      9/10/2010
0
Comment
Question by:holemania
3 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 33681901
You could try something like this: (not exactly what you were asking for)

select ID, Hours, Date, DATEPART(wk, Date) WeekNumber
from ....

or if you were summarizing by week, you could put

select sum(Hours) TotalHours, DATEPART(wk, Date) WeekNumber
from ....
group by DATEPART(wk, Date)
0
 
LVL 15

Accepted Solution

by:
AmmarR earned 500 total points
ID: 33723763
Dear holemania

to be able to get exactly the output you are looking for is doable but you will need some tweaks

because you want to output 3 columns (ID,  HOURS   ,DATE) and you want to output the Week as
8/30 - 9/3
how will it be displayed.

so i will demonstrate a different way below but i will get you same results

Step1.
you need to create a function that will identify the first day of the week
check code below and for more details check the link

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

step 2,

use the code below to generate the results like the image attached
i added a new field in the output 'Week', that will display the week.

step3
you can store this result in a temporary table and do what ever you like with it
--Step 1 Create function 

create function dbo.F_START_OF_WEEK
(
	@DATE			datetime,
	-- Sun = 1, Mon = 2, Tue = 3, Wed = 4
	-- Thu = 5, Fri = 6, Sat = 7
	-- Default to Sunday
	@WEEK_START_DAY		int	= 1	
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns		datetime
as
begin
declare	 @START_OF_WEEK_DATE	datetime
declare	 @FIRST_BOW		datetime

-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
	begin
	-- Find first day on or after 1753/1/1 (-53690)
	-- matching day of week of @WEEK_START_DAY
	-- 1753/1/1 is earliest possible SQL Server date.
	select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
	-- Verify beginning of week not before 1753/1/1
	if @DATE >= @FIRST_BOW
		begin
		select @START_OF_WEEK_DATE = 
		dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
		end
	end

return @START_OF_WEEK_DATE

end
-----------------------------------------------------

--Step 2 code script to generate output requested

select 
ID ,
[HOURS],
convert(varchar(10),[DATE] ,101) as 'Date',
convert(varchar(5),dbo.F_START_OF_WEEK([DATE],2) ,101) + ' - ' +
convert(varchar(5),DATEADD("d",4,dbo.F_START_OF_WEEK([DATE],2)) ,101)  as 'Week' from table

Open in new window

weeks.png
0
 

Author Closing Comment

by:holemania
ID: 33753687
Awesome.  This is exactly what I was trying to get at and use the week as a grouping.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question