Solved

SQL Query - Grouping by weekly

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to manage encyps queries mssql when sending conditioning values 10 38
Caste datetime 2 52
SQL Server 2008 R2 - Sums/Grouping 7 51
Help Extract Specific in SQL 8 27
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
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, f…

863 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

25 Experts available now in Live!

Get 1:1 Help Now