?
Solved

SQL Query - Grouping by weekly

Posted on 2010-09-15
3
Medium Priority
?
376 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

765 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