Re: Can this query be built?

Just my opionion, but I prefer to calculate the dates as and to_char(date, 'yymm') between 9901 and 9908

However a question arises when I do that if I have an index on the date field, does the to_char function drop the index?

Ken Rachynski wrote:

> Thank you. I had forgotten about nesting the year to date portion.
> This will do what I need when I change the following lines:
>
> where to_char(month, 'YYYYMM') >
> to_char(add_months(sysdate, -12), 'YYYYMM')
>
> to
>
> where quota_month between :start_of_fy and :current_month
>
> The users can run the report for any arbitrary month to get a year to
> date report. Not very nice, but I'll do it for them.
>
> Ken Rachynski
>
> On Thu, 5 Aug 1999 18:39:04 +0200, "Michel Cadot" <micadot_at_altern.org>
> wrote:
>
> >I'm not sure i understand clearly what you mean, but try the
> >following query I think it gives the result you want:
> >
> >select sales.manager_id, sales.quota "CURRENT", sview.ytp
> >from sales, (select manager_id, sum(quota) ytp from sales
> > where to_char(month, 'YYYYMM') >
> > to_char(add_months(sysdate, -12), 'YYYYMM')
> > group by manager_id) sview
> >where sview.manager_id = sales.manager_id
> > and trunc(sales.month, 'MONTH') = trunc(sysdate, 'MONTH')
> >;
> >
> >
> >
> >
> >Ken Rachynski a écrit dans le message <37a9abb3.4725347_at_news.cadvisi=
on.com>...
> >>Good day,
> >>
> >>I have a table of sales quotas showing the quotas for each sales
> >>manager for each month. ie
> >>
> >>MANAGER_ID QUOTA MONTH
> >>KJR 8000 1-JUN-1999
> >>KJR 6000 1-JUL-1999
> >>KJR 7000 1-AUG-1999
> >>SFH 5000 1-JUN-1999
> >>SFH 6000 1-JUL-1999
> >>SFH 8000 1-AUG-1999
> >>
> >>The month column is a DATE type and I expect to use a BETWEEN clause
> >>to compare dates (BETWEEN '1-SEP-1998' AND '1-SEP-1999').
> >>I need to query out the current month quotas plus the year to date
> >>quotas for each sales manager. ie (Current month is AUG-1999, Year to=

> >>date is SEP-1998 to AUG-1999):
> >>
> >>MANAGER_ID CURRENT YTD
> >>KJR 7000 21000
> >>SFH 8000 19000
> >>
> >>I tried modifying a Paradox 7 QBE statement that *does* give me this
> >>answer into an equivalent SQL statement, but for the CURRENT column,
> >>it returns the first quota on the list. For the actual data, I have
> >>noticed that the Sales Managers are assigned a yearly quota and it is=

> >>split evenly between all 12 months to generate the monthly quotas so
> >>it's not readily noticable unless I fake data like this.
> >>
> >>Can this work?
> >>
> >>Ken Rachynski
> >
> >

Received on Thu Aug 05 1999 - 15:52:47 CDT