At 2/25/2004 09:25 PM, james_027 wrote:

>Good day everyone.
>
>Iam having trouble to write a sql statement that gives a average sales
>amount for a month for a customer on a invoice table which has this
>following fields(not a complete list of fields)
>
>invoice table
>1. invid
>2. customerid
>3. invdate
>4. totalamount
>
>Anybody has tried doing this?

There may be ways to do this with a single query using Extractxxx
functions. However, I typically use a calendar table, which has dates, and
amplifying info such as day of week, day of month, month number, quarter
number, etc... It then becomes a simple matter of joining to this calendar
table with your INVDATE, and grouping your results by the month number from
the calendar table. It's an incredibly useful table that has come in quite
handy.

create domain D_DATE as date;
create domain D_ID as integer not null;
create domain D_SMALL_INT as smallint;
create domain D_YNFLAG as char(1) check (((value is NULL) or (value in
('Y','N'))));
create table calendar(
CALENDAR_ID D_ID,
CAL_DATE D_DATE not null,
DAY_OF_WEEK D_SMALL_INT,
DAY_OF_MONTH D_SMALL_INT,
DAY_OF_YEAR D_SMALL_INT,
WEEK_NBR D_SMALL_INT,
MONTH_NBR D_SMALL_INT,
QTR_NBR D_SMALL_INT,
YEAR_NBR D_SMALL_INT,
FISCAL_WEEK D_SMALL_INT,
FISCAL_MONTH D_SMALL_INT,
FISCAL_QUARTER D_SMALL_INT,
FISCAL_SEASON D_SMALL_INT,
FISCAL_YEAR D_SMALL_INT,
HOLIDAY_FLAG D_YNFLAG,
WEEKDAY_FLAG D_YNFLAG,
LAST_DAY_IN_MONTH_FLAG D_YNFLAG);

alter table calendar add constraint PK_CALENDAR primary key (CALENDAR_ID);
create unique index CALENDAR_DATE_IDX on calendar(CAL_DATE);
create index CALENDAR_SEASON_IDX on calendar(FISCAL_SEASON, CAL_DATE);
create index CALENDAR_PERIOD_IDX on calendar(FISCAL_MONTH, CAL_DATE);
create index CALENDAR_FISCAL_YEAR_IDX on calendar(FISCAL_YEAR, CAL_DATE);


Bob M..