Generating Running Total and Combining with Spouse

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Generating Running Total and Combining with Spouse

Author

Topic

Bazinga
Starting Member

19 Posts

Posted - 2012-12-28 : 11:57:00

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?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 15:21:25

How is Step 1 and Step 2 related?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources