Need Help with Query 1

[iccb]

Here is the query I'm using:

select m.transaction_number, m.transmittal_number, m.received_from, d.fy_applied,
d.ae_state_basic as ae_state_basic,
d.ae_state_performance as ae_state_performance,
d.ae_state_public_assistance as ae_state_public_assistance,
d.ae_federal_basic_admin as ae_federal_basic_admin,
d.ae_federal_basic_grants as ae_federal_basic_grants,
d.ae_federal_basic_leadership as ae_federal_basic_leadership,
d.ae_elcivics_federal_admin as ae_elcivics_federal_admin,
d.ae_elcivics_federal_grants as ae_elcivics_federal_grants,
d.ae_elcivics_federal_leadership as ae_elcivics_federal_leadership
from receipts_master m, receipts_detail d
where m.transaction_number = d.transaction_number and
m.transaction_type = 'Refund' and
m.fy_received = '2006' and
m.completion_date >= '8/1/2005' and
m.completion_date <= '8/31/2005' and
(d.ae_state_basic +
d.ae_state_performance +
d.ae_state_public_assistance +
d.ae_federal_basic_admin +
d.ae_federal_basic_grants +
d.ae_federal_basic_leadership +
d.ae_elcivics_federal_admin +
d.ae_elcivics_federal_grants +
d.ae_elcivics_federal_leadership > 0) and
d.fund in ('001', '692')

I trimmed the results just to the fields that have something in them.

The Results:
TRANSACTION_NUMBER TRANSMITTAL_NUMBER RECEIVED_FROM FY_APPLIED AE_STATE_PERFORMANCE AE_FEDERAL_BASIC_GRANTS
================ =============== ================== ======== ================= ===================
2006055 EAT06007 Decatur School District #61 2005 50
2006055 EAT06007 Decatur School District #61 2005 0 845
2006063 EAT06009 Il Eastern Comm College 2005 0 724

The first two records have an entry in state and federal fields and I want this to show on one line if the fy_applied is the same.

I want it to show on separate lines if the fy_applied are different.

State fields are fund 001 and Federal fields are fund 692 and this appears to be where I'm having a problem.

Bottom line is if the fy_applied is the same and the fund is 001 or 692 then I want it to show as one record.

If the fy_applied is different and the fund is 001 or 692 then this should show as two different records.

Any help would be greatly appreciated.

 

[PHV]

Perhaps this ?
select m.transaction_number, m.transmittal_number, m.received_from, d.fy_applied,
SUM(d.ae_state_basic) as ae_state_basic,
SUM(d.ae_state_performance) as ae_state_performance,
SUM(d.ae_state_public_assistance) as ae_state_public_assistance,
SUM(d.ae_federal_basic_admin) as ae_federal_basic_admin,
SUM(d.ae_federal_basic_grants) as ae_federal_basic_grants,
SUM(d.ae_federal_basic_leadership) as ae_federal_basic_leadership,
SUM(d.ae_elcivics_federal_admin) as ae_elcivics_federal_admin,
SUM(d.ae_elcivics_federal_grants) as ae_elcivics_federal_grants,
SUM(d.ae_elcivics_federal_leadership) as ae_elcivics_federal_leadership
from receipts_master m, receipts_detail d
where m.transaction_number = d.transaction_number and
m.transaction_type = 'Refund' and
m.fy_received = '2006' and
m.completion_date >= '8/1/2005' and
m.completion_date <= '8/31/2005' and
(d.ae_state_basic +
d.ae_state_performance +
d.ae_state_public_assistance +
d.ae_federal_basic_admin +
d.ae_federal_basic_grants +
d.ae_federal_basic_leadership +
d.ae_elcivics_federal_admin +
d.ae_elcivics_federal_grants +
d.ae_elcivics_federal_leadership > 0) and
d.fund in ('001', '692')
GROUP BY m.transaction_number, m.transmittal_number, m.received_from, d.fy_applied

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[iccb]

That did the trick, thanks so much.