Home » SQL & PL/SQL » SQL & PL/SQL » SQL help on View to combine Spouse's FY giving
| SQL help on View to combine Spouse's FY giving [message #10056] |
Mon, 29 December 2003 07:25 |
Susan Gipson
Messages: 4
Registered: December 2003
|
Junior Member |
|
|
Here is what I need to do: People make donations to us for different designated campaigns. Some of these donors are married couples, but each spouse has their own individual giving record. Sometimes a spouse may make a donation soley for their own credit and at other times they may make a donation and ask us to split their gift with their spouse (the person gets half of the credit on his giving record and the spouse gets the other half of the credit on her giving record for the donation). So, any person may make gifts for only their own credit and/or they may make gifts split with their spouse.
I am dealing with only 2 tables. The gifthist table (accessed once for his gift records and once for her gift records) has the total of an individuals gifts by campaign designation for each fiscal fiscal year. An entry will exist in the gifthist table only for those fiscal years in which a donation was made. The other table, xref, is a cross reference table with the id of the individual, the xref_id of the spouse, and a code 'SPS' if the couple splits their gifts. I need to create a view that will pull all giving for a couple together, if their xref code is 'SPS', into just 2 totals for each fiscal year: a sum of giving to the 'SF' campaign fund and a sum of giving to all other designations/funds. The following code works as long as BOTH individuals in the couple made a donation in the fiscal year. However, if only one made a donation in a fiscal year, this code doesn't work - it will select the gift and include it in the FY total on the ID on the one who made the gift, but the ID of the spouse show no gift entry for the FY at all.
For example, Tim and Susie are married and are coded as 'SPS'; Susie made a $100. gift in 2002 solely for her own credit. Tim made no gift at all in 2002 and as a couple, they did not split any gift in 2002. However, because they are coded 'SPS' in the xref table, I need to show combined giving totals for each of them for each fiscal year. For 2002, Susie's ID shows $100. combined total, but Tim has no entry for 2002. He needs to have a 2002 entry that also shows the $100. combined giving total.
I'm still new at SQL, but it seems that I maybe need an outer join - so that I would pick up all gifts for both individuals of the couple, but I don't really know how to do that - nothing that I try works.
Here is an example of the code I'm trying to use:
select a.gifthist.id,
a.gifthist_fisc_code,
a.gifthist_campaign,
sum(decode(substr(a.gifthist_campaign,1,2), 'SF',
NVL(a.gifthist_amt_pledged_paid, 0) +
NVL(a.gifthist_amt_gift, 0) +
NVL(a.gifthist_amt_memo, 0) +
NVL(b.gifthist_amt_pledged_paid, 0) +
NVL(b.gifthist_amt_gift, 0) +
NVL(b.gifthist_amt_memo,0),
0)) SF_credit,
sum(decode(substr(a.gifthist_campaign,1,2), 'SF', 0,
NVL(a.gifthist_amt_pledged_paid, 0) +
NVL(a.gifthist_amt_gift, 0) +
NVL(a.gifthist_amt_memo, 0) +
NVL(b.gifthist_amt_pledged_paid, 0) +
NVL(b.gifthist_amt_gift, 0) +
NVL(b.gifthist_amt_memo, 0))) other_camp_credit
from gifthist a,
gifthist b,
xref
where EXISTS (select 'x'
from xref
where a.gifthist_id = xref_xref_id (+)
and xref_xref_code = 'SPS')
and a.gifthis_id = xref_xref_id
and xref_id = b.gifthis_id
and a.gifthis_campaign = b.gifthis_campaign
group by a.gifthis_id,
a.gifthis_fisc_code,
a.gifthis_campaign
order by a.gifthis_id,
a.gifthis_fisc_code,
a.gifthis_campaign;
Hope you can understand the situation here. Once I get this part to work, I will need to union it to a similar piece of code that shows the same total FY giving for those who ONLY give individually and are not coded 'SPS'. The SQL code that I have for that works fine as stand-alone code, but I have not tried to union it to this 'SPS' part yet.
Thanks for any help you can offer.
Susan
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:26:27 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
