Topic: Struggling for an elegant solution
I can't see my way round this one. Maybe I'm just tired but maybe my brain isn't big enough.
The application calculates tax refunds. A 'donor' makes many 'donations' and each 'donation' has an 'account'. Each 'account' is either eligible for tax or not (a simple boolean flag).
When I run the report I need to sum up all the eligible and ineligible amounts according to that flag. So I'd get:
Bloggs - eligible: 400
Bloggs - ineligible: 200
Jones - eligible: 300
Smith - eligible: 100
Smith - ineligible: 250
That's easy. I can do that. I sum the donations grouping by donor and the eligible flag.
But the business rule says that accounts aren't mandatory and a donation can be set up without an account. If it has no account, it's eligible.
Now I'm struggling with the Find that totals up:
1) all the donations where the account exists and eligible is false
2) all the donations where the opposite occurs
I've got the LEFT JOIN behaving itself but whenever I approach the modification to address the above, by brain leaks out of my left ear.
Can anyone help me? Or am I beyond help?