SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Generating Running Total and Combining with Spouse
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bazinga
Starting Member

19 Posts

Posted - 12/28/2012 :  11:57:00  Show Profile  Reply with Quote
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?

Edited by - Bazinga on 12/28/2012 11:58:22

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  15:21:25  Show Profile  Reply with Quote
How is Step 1 and Step 2 related?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000