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

SQL Server 2005 Forums

Transact-SQL (2005)

Group by Week

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-12 : 19:43:44

I have daily data that I would like to sum a running total and group by week.

1/1/2001, 5
1/2/2001, 5
1/3/2001, 5
1/4/2001, 5
1/5/2001, 5
1/6/2001, 5
1/7/2001, 5
1/8/2001, 5
1/9/2001, 5
1/10/2001, 5

So my results would look like
1, 35
2, 50

I've tried this but it throws an error...

select datename(mm,c.TimeByDay)
, (select sum(b.data) from myTable b where b.timebyDay <= c.timebyDay) as running_total_by_week
from myTable c
group by datename(mm,c.TimeByDay)

ftsoft
Starting Member

22 Posts

Posted - 2009-11-12 : 20:27:30

I use this code to sum bicycle ride totals by week.

SELECT SUM(distance) As weeklydistancetotal, SUM(HOURS) + SUM(MINUTES)/60 AS weeklyhourstotal
FROM rideevent
/*WHERE DATENAME(yyyy,RIDEDATE) = DATENAME(yyyy,@date) AND DATENAME(week,RIDEDATE) = DATENAME(week,@date)*/
where DATEPART(WEEK,RIDEDATE) = datepart(week, @date) AND DATEPART(YEAR, RIDEDATE) = DATEPART(YEAR, @date)

Hope this helps.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-12 : 20:44:06

Note : datename(mm,c.TimeByDay) will give you January, February etc not week no

What is your definition of week ?


declare @sample table
(
	[TimeByDay]	datetime,
	[data]		int
)
insert into @sample
select	'1/1/2001', 5	union all
select	'1/2/2001', 5	union all
select	'1/3/2001', 5	union all
select	'1/4/2001', 5	union all
select	'1/5/2001', 5	union all
select	'1/6/2001', 5	union all
select	'1/7/2001', 5	union all
select	'1/8/2001', 5	union all
select	'1/9/2001', 5	union all
select	'1/10/2001', 5

-- Using datepart(week, ) definition of week no
select	WeekNo		= datepart(week, [TimeByDay]), 
	RuningTotal	= (select sum(data) from @sample x where datepart(week, x.[TimeByDay]) <= datepart(week, s.[TimeByDay]))
from	@sample s
group by datepart(week, [TimeByDay])

/*
WeekNo      RuningTotal 
----------- ----------- 
1           30
2           50
*/

select	s.WeekNo, r.RunningTotal
from	(
		select	distinct WeekNo = datepart(week, [TimeByDay])
		from	@sample
	) s
	cross apply
	(
		select	RunningTotal = sum(data)
		from	@sample x
		where	datepart(week, x.[TimeByDay])	<= s.WeekNo
	) r
/*
WeekNo      RuningTotal 
----------- ----------- 
1           30
2           50
*/

-- Using 1st of the year as begining of the week
select	WeekNo		= (datepart(dayofyear, TimeByDay) - 1) / 7 + 1,
	RuningTotal	= (select sum(data) from @sample x where (datepart(dayofyear, x.TimeByDay) - 1) / 7 + 1 <= (datepart(dayofyear, s.TimeByDay) - 1) / 7 + 1)
from	@sample s
group by (datepart(dayofyear, TimeByDay) - 1) / 7 + 1

/*
WeekNo      RuningTotal 
----------- ----------- 
1           35
2           50
*/

KH
[spoiler]Time is always against us[/spoiler]

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-12 : 21:24:16

thanks...this looks great!! yep my mistake on the mm rather than ww.

matrixmind
Starting Member

9 Posts

Posted - 2009-11-13 : 01:34:15

quote:
Originally posted by JohnBGood

I have daily data that I would like to sum a running total and group by week.

1/1/2001, 5
1/2/2001, 5
1/3/2001, 5
1/4/2001, 5
1/5/2001, 5
1/6/2001, 5
1/7/2001, 5
1/8/2001, 5
1/9/2001, 5
1/10/2001, 5

So my results would look like
1, 35
2, 50

I've tried this but it throws an error...

select datename(mm,c.TimeByDay)
, (select sum(b.data) from myTable b where b.timebyDay <= c.timebyDay) as running_total_by_week
from myTable c
group by datename(mm,c.TimeByDay)

with abc
as
(
select Datepart(week,IDate) as [W],Sum(value) as

,Row_Number() over(Order by Datepart(week,IDate)) as [R] From Runn
Group by Datepart(week,IDate)
)
select W,(select Sum(V) From Abc A Where A.R<=Abc.R) as

From Abc

Dinesh Sharma
Matrix Solution
Sr.Software Engg.

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-13 : 08:00:32

These are great solutions!! what can i do if my data spans years?? Otherwise I get

1, 5
2, 10
3, 15
50, 20
51, 25
52, 30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-13 : 08:26:48

yes. Group by year


select	s.Year, s.WeekNo, r.RunningTotal
from	(
		select	distinct 
                        Year   = datepart(year, [TimeByDay]),
                        WeekNo = datepart(week, [TimeByDay])
		from	@sample
	) s
	cross apply
	(
		select	RunningTotal = sum(data)
		from	@sample x
		where	datepart(year, x.[TimeByDay])	<= s.Year
                and     datepart(week, x.[TimeByDay])	<= s.WeekNo
	) r


select	Year            = datepart(week, [TimeByDay]),
        WeekNo		= (datepart(dayofyear, TimeByDay) - 1) / 7 + 1,
	RuningTotal	= (select sum(data) from @sample x where (datepart(dayofyear, x.TimeByDay) - 1) / 7 + 1 <= (datepart(dayofyear, s.TimeByDay) - 1) / 7 + 1)
from	@sample s
group by datepart(week, [TimeByDay]),
         (datepart(dayofyear, TimeByDay) - 1) / 7 + 1

KH
[spoiler]Time is always against us[/spoiler]

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-13 : 09:30:38

great ... i will give this a try! Thanks so much.

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-13 : 12:52:23

--I really appreciate the help.. I've tried each of these and they don't quite do the trick

declare @sample table
(
[TimeByDay] datetime,
[data] int
)
insert into @sample
select '12/26/2001', 5 union all
select '12/27/2001', 5 union all
select '12/28/2001', 5 union all
select '12/29/2001', 5 union all
select '12/30/2001', 5 union all
select '12/31/2001', 5 union all
select '1/1/2002', 5 union all
select '1/2/2002', 5 union all
select '1/3/2002', 5 union all
select '1/4/2002', 5 union all
select '1/5/2002', 5 union all
select '1/6/2002', 5 union all
select '1/7/2002', 5 union all
select '1/8/2002', 5 union all
select '1/9/2002', 5 union all
select '1/10/2002', 5 union all
select '1/11/2002', 5

select s.Year, s.WeekNo, r.RunningTotal
from (
select distinct
Year = datepart(year, [TimeByDay]),
WeekNo = datepart(week, [TimeByDay])
from @sample
) s
cross apply
(
select RunningTotal = sum(data)
from @sample x
where datepart(year, x.[TimeByDay]) <= s.Year
and datepart(week, x.[TimeByDay]) <= s.WeekNo
) r

-- the result I need is -- the running total spans over the year change

2001, 52, 20
2001, 53, 30
2002, 1, 55
2002, 2, 85

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-13 : 18:49:22

[code]

select s.Year, s.WeekNo, r.RunningTotal
from
(
select distinct
Year = datepart(year, [TimeByDay]),
WeekNo = datepart(week, [TimeByDay]),
YearWeek = datepart(year, [TimeByDay]) * 1000 + datepart(week, [TimeByDay])
from @sample
) s
cross apply
(
select RunningTotal = sum(data)
from @sample x
where datepart(year, x.[TimeByDay]) * 1000 + datepart(week, x.[TimeByDay]) <= s.YearWeek
) r
[/code]

KH
[spoiler]Time is always against us[/spoiler]

JohnBGood
Starting Member

48 Posts

Posted - 2009-11-14 : 07:45:45

That works well khtan!! thanks so very much for your excellent solution! I would have still been banging my head if it were not for your help. Gonna have to look up the cross apply operation - as i've never come across it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources