Re: Can this query be built?

From: Michel Cadot <micadot_at_altern.org>
Date: Thu, 5 Aug 1999 18:39:04 +0200
Message-ID: <7oceo6$7gi$1@oceanite.cybercable.fr>

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 - 11:39:04 CDT