Solved

SQL Query - Grouping by weekly

Posted on 2010-09-15
3
365 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now