Re: Can this query be built?

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.cadvision.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 - 12:52:39 CDT