Group by week - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by week

Author

Topic

mageshks
Yak Posting Veteran

59 Posts

Posted - 2006-07-03 : 06:38:59

Consider this is my table

Date Balance
------------------------------------
2006-06-17 00:00:00.000 62180.7500
2006-04-18 00:00:00.000 1431.0000
2006-04-28 00:00:00.000 2000.0000
2006-05-31 00:00:00.000 457.0000
2006-04-01 00:00:00.000 11.0000
2006-06-16 00:00:00.000 281.2500
2006-04-22 00:00:00.000 156.0000
2006-04-07 00:00:00.000 4.0000
2006-05-18 00:00:00.000 2457.0000
2006-04-18 00:00:00.000 57.0000
2006-04-14 00:00:00.000 15016.0000
2006-05-06 00:00:00.000 25312.5000
2006-05-04 00:00:00.000 506.2500
2006-06-05 00:00:00.000 2849.6000
2006-05-19 00:00:00.000 2600.0000
2006-05-03 00:00:00.000 29444.0000
2006-05-11 00:00:00.000 3037.5000
2006-05-11 00:00:00.000 6708.0000
2006-05-07 00:00:00.000 3434.0000
2006-05-05 00:00:00.000 3768.7500

i have to take the sum of the balace for every week .Lets take th first record in this table

2006-06-17 00:00:00.000 62180.7500 .Here the start date of the week 17. i have to take the sum of this week(for dates 17,18,19,20,21,22,23)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 07:17:20

Something like this?

--data
declare @t table (Date datetime, Balance money)
insert @t
          select '20060617', 62180.7500
union all select '20060418', 1431.0000
union all select '20060428', 2000.0000
union all select '20060531', 457.0000
union all select '20060401', 11.0000
union all select '20060616', 281.2500
union all select '20060422', 156.0000
union all select '20060407', 4.0000
union all select '20060518', 2457.0000
union all select '20060418', 57.0000
union all select '20060414', 15016.0000
union all select '20060506', 25312.5000
union all select '20060504', 506.2500
union all select '20060605', 2849.6000
union all select '20060519', 2600.0000
union all select '20060503', 29444.0000
union all select '20060511', 3037.5000
union all select '20060511', 6708.0000
union all select '20060507', 3434.0000
union all select '20060505', 3768.7500

--calculation
select START_OF_WEEK_STARTING_SAT_DATE, sum(Balance) as Balance
from dbo.F_TABLE_DATE('20060401','20060701') a left outer join @t b on a.date = b.date
group by START_OF_WEEK_STARTING_SAT_DATE
order by START_OF_WEEK_STARTING_SAT_DATE

/*results
START_OF_WEEK_STARTING_SAT_DATE                        Balance               
------------------------------------------------------ --------------------- 
2006-04-01 00:00:00.000                                15.0000
2006-04-08 00:00:00.000                                15016.0000
2006-04-15 00:00:00.000                                1488.0000
2006-04-22 00:00:00.000                                2156.0000
2006-04-29 00:00:00.000                                33719.0000
2006-05-06 00:00:00.000                                38492.0000
2006-05-13 00:00:00.000                                5057.0000
2006-05-20 00:00:00.000                                NULL
2006-05-27 00:00:00.000                                457.0000
2006-06-03 00:00:00.000                                2849.6000
2006-06-10 00:00:00.000                                281.2500
2006-06-17 00:00:00.000                                62180.7500
2006-06-24 00:00:00.000                                NULL
2006-07-01 00:00:00.000                                NULL
*/

Using this function...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-03 : 08:36:25

In the shadow of Ryan's excellent solution, here is another approach without external function. However, this does not produce the sum of Zero for missing weeks, as the one above.

declare @startdate datetime

select @startdate = '2006-06-17'

SELECT		DATEADD(wk, DATEDIFF(wk, @StartDate, Date), @StartDate),
		SUM(Balance)
FROM		@t
GROUP BY	DATEADD(wk, DATEDIFF(wk, @StartDate, Date), @StartDate)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources