Re: Select data and sum by weeks in a month

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
'====================================================


Yossy wrote:
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.
.

Relevant Pages