?
Solved

SQL Query - Grouping by weekly

Posted on 2010-09-15
3
Medium Priority
?
386 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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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