Select data and sum by weeks in a month

  • Thread starter Thread starter Yossy
  • Start date Start date
Y

Yossy

I have a table with multiple columns. But I want to select data from column 1
and Column 4 using date(column 5) as criteria.

My dates are listed daily, how do I sum up the activities of each day into
weeks and have just 4 weekly summed up data. E.g If I want to retrieve for
the month of November...

E.g Column 1 Column 2 Column 3 Column 4 Column 5 (Date)
1. 20 100 90 10 11/1/2008
2. 9 200 108 89 11/2/2008
3. 30 80 69 107 11/3/2008
e.t.c

From sample above I only want weekly data, so for 11/7/2008 I want Column 1
and column 4 summed up for the week. This way my monthly result will be
column 1 column 4 column 5(Date)
290 650 11/7/2008 - This should sum up date
11/1 to 11/7
450 370 11/15/2008 - This should sum up date
11/8 to 11/15
275 610 11/23/2008- This should sum up date
11/16 to 11/23
80 170 11/30/2008- This should sum up date
11/24 to 11/30

Thanks, all help totally appreciated. My query need to be run in MSSql.
 
It's a little hard to tell, based on the output you gave us, because there is
no consistency in the Column 5 data (11/7 is a Friday, 11/15 is a Sat, 11/23
is a Sun, 11/30 is a Mon).

Assuming that you want to sum column1 and Column 4 and that you want them to
be grouped by the week ending on Saturday (so 11/2 - 11/8 would all show up
on the 11/8 week), the sql would look something like:

SELECT Sum([Column 1]) as SumOfCol1,
Sum([Column 4]) as SumOfCol4,
DateAdd("d", 7 - Weekday([Column 5], 1), [Column 5]) as WeekEnding
FROM yourTable
GROUP BY DateAdd("d", 7 - Weekday([Column 5], 1), [Column 5])

The key to this is figuring out what date you want to group by. The WeekDay(
) function returns the numeric day of the week, based on the optional 2nd
parameter. If the second parameter is left out, it is assumed to be 1
(Sunday). So what my use of the DateAdd function does is determine the next
Saturday for each date in [Column 5]. By grouping on that field, You get
what you want.


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
One problem is that every month except February in non-leap years
contains 4 weeks and some odd day(s).

So the first thing to resolve is how do you handle the extra days. One
method would be to put all the data into 4 weeks based on the day number
and then put the "extra" days in the last week. You would have weeks for
1 to 7
8 to 14
15 to 21 and
21 to last day in month

Or have five weeks with the "extra" days put in the fifth week.

Or have four weeks of eight days each and the fourth week would always
be short.

Or have weeks of 7, 8, 8, and the remaining days in the month.

After you decide that then you can try to develop a solution.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Back
Top