I am perplexed by this complicated (at least for me) challenge. I will break this question into several steps to make it easier to understand.
Step 1
I am working with a database that records donations and I've created a view that generates a running total for each donor's ID (giftid). The results of the SQL are as follows:
giftkey giftid gifteffdat giftamt Running_Total
00001 01 1/1/2012 $1000 $1000
00002 01 2/1/2012 $500 $1500
00003 01 3/1/2012 $200 $1700
00004 02 1/5/2012 $100 $100
00005 02 4/1/2012 $200 $300
Etc...
The SQL that is used to generate these totals:
SELECT TOP (100) PERCENT giftkey, giftid, gifteffdat, giftamount,
(SELECT SUM(giftamount) AS Expr1
FROM dbo.gifts_full AS G2
WHERE (giftkey <= G1.giftkey)
AND (giftid = G1.giftid)
AND (gifttype IN ('g', 'y', 'b', 'c'))) AS Running_Total
FROM dbo.gifts_full AS G1
WHERE (gifttype IN ('g', 'y', 'b', 'c'))
ORDER BY giftid, gifteffdat
All seems good. What I want to do now...
Step 2
A good number of these individuals in the database also have a spouse in the database with his/her own totals (the relationship is recorded in a table as described further below). Instead of the running totals calculating for each individual, I would like the generate a running total for the 'couple' if they are married, so that the results are like the below example, where persons with the IDs of 05 and 55 are a married couple.
Key ID Date Amt Running_Total
00111 05 1/2/2012 $500 $500
00112 55 1/3/2012 $300 $800
00159 05 2/5/2012 $1000 $1800
00207 55 3/6/2012 $2000 $3800
Each person with a spouse also in the database has that relationship recorded in the relationship table as shown below:
ID RelType RelID
05 Spouse 55
55 Spouse 05
Step 3
Preferable, I would like to create a household ID for all entries, so that the above couple has a household id of something like 'H05' or 'H55' and a person with no spouse would have a household id of 'I47' where 47 is their ID number in the system and 'I' was appended in the view.
The final results would be a a running total with both the individual ID, household id, and the running total either for the individual or the couple, as below:
Key ID HldID Date Amt Running_Total
00111 05 H05 1/2/2012 $500 $500
00112 55 H05 1/3/2012 $300 $800
00159 05 H05 2/5/2012 $1000 $1800
00207 55 H05 3/6/2012 $2000 $3800
00358 47 I47 3/1/2012 $50 $50
00398 47 I47 6/1/2012 $100 $150
For a couple's household ID, I still need to figure out which criteria will determine which of the individual IDs would be used as the household ID with the 'H' appended in the front, but for now it does not matter.
Now, is this a difficult task to accomplish with SQL?
Generating Running Total and Combining with Spouse
Posted - 12/28/2012 : 11:57:00
