I need help with a query.
I have the following dataset:
AgCode SumOfPremAmt AppStatus NumOfApps ProducerType
------------------------------------------------------------
11 $35,423.96 HYPOED 12 Broker
11 $28,791.36 HYPOED 13 FR/FTA
11 $17,203.39 NOT TAKEN 6 Broker
11 $25,847.22 NOT TAKEN 7 FR/FTA
11 $127,760.99 RECEIVED 61 Broker
11 $82,692.06 RECEIVED 39 FR/FTA
11 $1,433.53 RECEIVED 1 Pending
11 $1,731.70 REOPENED 2 Broker
11 $12,411.83 REOPENED 8 FR/FTA
Now, in a seperate query, I want the following:
Agency Wastage NumOfAppSubmitted Producer Type
-------------------------------------------------------
11 16 61 Broker
The wastage is calculated by:
(NumOfHypoed - NumOfReopended) + NumOfNotTaken
I know how to do this with multiple queries using joins, but was
wondering if there was a way to get it in one query.
Thanks!
Brian