The way I have come up with to do these types of calculations is a bruite
force method. I create a table with the a date field and then some other
columns adding the calculations on the date that I need, like day_num
(iday), mon_num (imon), week_num, qtr_num. I then write a program that
loops through and populates the table for whatever date range I need with a
record for every day. I usually run it back 5 years and forward 5 years so
I make sure I have every day. Index all of the fields. Then you can buld a
view between your original table and the dates table. Now you can do
reports, selects, and group bys on the view.
There are other methods using views with calculations that you can
accomplish the same thing, but if you are dealing with a large dataset, this
is the fastest method.
Troy Sosamon
===== Original Message from [EMAIL PROTECTED] at 10/23/02 11:27 pm
>Hi all,
>
>Some time back I think I saw a simple method for generating report totals by week in
>a R:Base
report but I can not seem to remember how it was done or locate it -
possibly a simptom of working too late into the evening ...
>
>Does anyone have a favorite solution to this problem. Basically I have dated records
>that
include numeric columns. There are not records for every date. I need to
print a report that generates a break footer with subtotals at weekly
intervals
>ending with each Sunday, but there are no records for Sundays. All of the solutions
>I have
come up with (using modular arithmetic MOD 7 or 7 day increments using
ADDDAY) seem a bit convoluted - or at least my approach seems to be.
>
>I almost hate to ask because I am sure there is a really slick way to do this, and
>I'm going
to kick myself when I see it :-{
>
>Thanks,
>Michael Moser
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
