CODEWITHSUNDEEP
sqlsql-serverjoin
Don J
Don J

Reputation: 18

Build query that gives calculated results from several tables

I need to build a query that contains a Fair market Value for each fund in a portfolio. These are not public entities, so the Fair market value statements don’t come every month. The calculation is simple when I step through a cursor based operation, but I would like to build a query that returns all of the results at once. Here’s a simplified version of how it works:

tblFMV contains FMVDate, FundID, FairMarketValue

tblTransaction contains TransactionDate, FundID, Transaction amount (could be positive or negative)

To calculate the FMV for a given date, I get the most current FMV on or before the given date from tblFMV, and then I add any transactions from tblTransaction on or after the FMV date up to and including the given date.

Example:
tblFMV

Date, FundID, FMV
1/1/2015, 1, $1000
4/1/2015, 1, $1500

tblTransaction

TransactionDate, FundID, TransactionAmount
1/3/2015,        1,      $100
2/5/2015,        1,      -$75

To get the FMV for 2/1/2015 I start with the $1000 entry on 1/1/2005 and add the $100 from 1/3/2015 for a FMV of $1100
The FMV for 3/1/2015 is $1025
The FMV for 4/1/2015 is $1500 (at this point I can calculate a gain, but that’s a different story)

I have another simple table that has the month beginning dates for every month in a year: 

1/1/2015
2/1/2015 
3/1/2015 
etc

I would like to join all three tables to come up with a result set that looks like this:

FMVDate, FundID, FMV
1/1/2015, 1, $1000
2/1/2015, 1, $1100
3/1/2015, 1, $1025
4/1/2015, 1, $1500
...

Like I said, I am currently doing it in several steps in code. I get the latest FMV, sum up and transactions and add them to the total. Do you have any thoughts on how to create a query (or several queries) to return the result set? This is in SQL server.

Thanks

Upvotes: 0

Views: 70

Answers (2)

Tim3880
Tim3880

Reputation: 2583

You can try this:

SELECT MON, FUNDID, 
   LAST_FMV + CASE WHEN CHANGES IS NOT NULL THEN CHANGES ELSE 0 END FMV 
FROM (
    SELECT MON, FUNDID, LAST_DATE, LAST_FMV , 
            (SELECT SUM(TRANSACTIONAMOUNT) FROM TBLTRANSACTION T 
             WHERE T.FUNDID = N.FUNDID 
                  AND T.TRANSACTIONDATE > N.LAST_DATE 
                  AND T.TRANSACTIONDATE < N.MON
            ) AS CHANGES
    FROM (
        SELECT MON,M.FUNDID, LAST_DATE, M.FMV AS LAST_FMV 
        FROM 
           (SELECT MON , MAX(L.DATE) LAST_DATE, L.FUNDID
                FROM MONTHS M
                LEFT JOIN TBLFMV L
                ON M.MON > = L.DATE
                GROUP BY MON, FUNDID) FMV
        JOIN TBLFMV M
        ON FMV.LAST_DATE = M.DATE AND FMV.FUNDID = M.FUNDID
    ) N
) C

Upvotes: 1

David W
David W

Reputation: 10184

This is quick, crude, and very much untested, but see if this comes close. The idea, at least, is to sum the per-fund monthly transactions as "deltas" to the FMV, then add the deltas to the starting FMV, and derive the result. The "current month's" FMV would be equal to the last-month's starting FMV plus the previous month's deltas:

select dateadd(month,1,a.month_begin_date) month_begin_date,b.fund_id,b.fmv+c.fmv_delta FMV
  from months a
  cross apply tblfmv
  join (select month(transaction_date) tmonth,year(transaction_date) tyear ,fundid,sum(transaction) fmv_delta
          from tbltransaction
         group by month(transaction_date),year(transaction_date),fundid) c
  on c.tmonth=month(a.month_begin_Date)
  and c.tyear=year(a.month_begin_Date)
  and tblfmv.fundid=c.fundid

Upvotes: 0

Related Questions