I need to generate a report based on the following tables:
CALLS_FOR_PROPOSALS ID|Name --+------ 1|Call 1 2|Call 2 3|Call 3 PROPOSALS ID|Call ID|Title --+-------+---------- 1| 1|Proposal 1 2| 2|Proposal 2 3| 2|Proposal 3 PROPOSAL_STATUSES ID|Proposal ID|Status ID --+-----------+--------- 1| 1| 1 2| 2| 1 3| 3| 1 4| 3| 2 STATUSES ID|NAME --+------------ 1|Not Reviewed 2|Processing 3|Accepted 4|Rejected With this sample data, there are 3 Calls for Proposals. There are three Proposals; one for Call 1, and two for Call 2. (Call 3 does not have any proposals.) Each proposal has at least one status assigned to it. When a row is inserted into the PROPOSALS table, a corresponding row is inserted into PROPOSAL_STATUSES, giving the Proposal an initial default status of 1 (Not Reviewed). Each time the status is changed, a new row is inserted into the PROPOSAL_STATUSES table, so that the history of status changes is preserved. I need to generate a report that shows for each Call, the number of Proposals submitted, and the number of Proposals that have had more than one status (i.e. the status has been changed from the default at least once.) For the sample data above, the results would look like this:
Call Name|Proposals Submitted|Proposals Reviewed| ---------+-------------------+------------------+ Call 1 | 1| 0| Call 2 | 2| 1| Call 3 | 0| 0| How would I write the SQL query to generate this report based on the above table structure? Thanks for your help.
