Metal Guitarist Forums banner

1 - 20 of 26 Posts

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #1 ·
Ok, first, what I'm trying to do, in case there's something I'm missing - I'm trying to build a query for a friend here at work that's a bit beyond her abilities. Basically, I'm working with three tables:

Table A - A table that, for a given consolidation, can provide the member accounts within that consolidation for a specified date.
Table B - A table that, for a given account, can provide a monthly return for a given date.
Table C - a table that, for a given account, can return the associated benchmark for that account (this is a non-date-specific field)

So, what I've done so far is joined Table B to Table A where A.Date = B.Date and A.Member account = B.Account, and then Table C to table A on A.Member Account = C.Account. These aren't the actual names, but they're close enough.

Here's the catch - I also want to, if possible, return the monthly return for the benchmark associated with a portfolio.

I think what I want to do (if this is possible) is nest a query within the query where it joins Table B and C where it joins C.Benchmark at B.Account, and then creates a result set of B.Account and the B.Return associated with C.Benchmark, and then query this as Table D and join that back to Table A on A.Member Account so for a given composite, when it returns A.Member Account I can also return something like "D.Benchmark Return" where D.Benchmark Return is B.Monthly Return for C.Benchmark. But, I have no clue how to do this, and I'm not really sure what to ask Google.

Any thoughts?
 

·
Premium Member
Joined
·
11,451 Posts
Sure - inline views are AWESOME tools. There's a couple of different approaches depending on what your flavor of SQL supports. The easy one would be this:

select a.member_account, d.benchmark_return from (table name) a, (select benchmark_return, a.account account from (select b.return return, c.benchmark benchmark from b, c where b.account = c.account)) d where a.account = d.account and a.date = (whatever the range is)

If I'm reading you right, this should give your return joined to account by the date range, which is what you want. The important bit is this:

(select benchmark_return, a.account account from (select b.return return, c.benchmark benchmark from b, c where b.account = c.account)) d

Where your query becomes an inline view, i.e. a table that can be selected against.
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #3 ·
I THINK I just got it to work - I realized that I didn't actually need to put any joins in the subtable, since it could all come from Table B (returns) as long as it was then joined correctly.

So, I did a Left Outer Join to Table (Select: Account, Return, Date, Segment From: Table B) D, and then Joined that on C.Benchmark = D.Account, B.Date = D.Date, and B.Segment=D.Segment

(Segment is just sort of a code identifying the type of return).

Am I on the right track? I'm about to send it over to see if it's getting her what she's expecting. If so, I have to add a SECOND subtable, so I can pul market values from a different return table as well. :lol:
 

·
Read Only
Joined
·
10 Posts
Wirelessly posted (Metal Shop \m/: Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_0_2 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8A400 Safari/6531.22.7)

WTF is this? 6 years later and I find out that Drew can do SQL?!? Next time the damn DB crashes, it's all you motherfucker!
 

·
NICE BLACKMACHINE YO
Joined
·
7,276 Posts
Wirelessly posted (Metal Shop \m/: Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_0_2 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8A400 Safari/6531.22.7)

WTF is this? 6 years later and I find out that Drew can do SQL?!? Next time the damn DB crashes, it's all you motherfucker!
Quick, Drew, act dumb so he thinks you'll put out he buys you another drink.

Guys like Chris fear smart ones; too much hassle. :lol:
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #7 ·
Wirelessly posted (Metal Shop \m/: Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_0_2 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8A400 Safari/6531.22.7)

WTF is this? 6 years later and I find out that Drew can do SQL?!? Next time the damn DB crashes, it's all you motherfucker!
:lol: I'm not exactly good at it, but yeah, all of my data at work is stored on database tables - knowing how to access them sort of pays off. :lol:
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #9 ·
:lol: Sorry man - we have to store our rate of return data SOMEWHERE, right? :lol:
 

·
Read Only
Joined
·
10 Posts
Honestly, if someone told the joke about naming their kid DROP TABLE STUDENTS JONES, I never in a million years would have thought you'd get it. :lol:
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #13 ·
Honestly, if someone told the joke about naming their kid DROP TABLE STUDENTS JONES, I never in a million years would have thought you'd get it. :lol:
:lol: Please, there's a XKCD about that. :lol:

Actually, to be fair, my SQL is limited solely to querying data, not modifying tables. Considering most of the staff here just uses canned queries and there's no formal SQL training, you REALLY don't want a whole bunch of people going about writing code to modify tables, flying blind. :lol: I don't even know if my ID is authorized to do more than extract existing data.

Techno- debatable... It really depends what you're looking to do, I guess. I mean, you can do some pretty simple functions in SQL relatively efficiently, but I suspect if you wanted to calculate the standard deviation of a return series, it's faster to use a query to pull the returns, dump into excel, and write a Sdev function than it is to try to code a standard deviation calculation into SQL. Ditto with return calculation - I think pulling 30 days' of daily cashflows, dumping into excel, and using weighting formulas to day-weight and then do a modified Dietz calculation is way faster than trying to write a SQL query to do that for you. I could be dead wrong here, but I think as the complexity of a calculation increases, the more it becomes attractive to rely on Excel's computational firepower and just use SQL as a data source.
 

·
Read Only
Joined
·
3 Posts
You could write package procedures to do those calculations and use them in-line in your SQL queries... no Excel needed.
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #16 ·
You could write package procedures to do those calculations and use them in-line in your SQL queries... no Excel needed.
Hmm. I'm not really sure what you mean by that, Matt.

Say we have a database, like so (whipping this up in Excel). Call it "Monthly Returns":
Code:
ACT Number	Date 	Return
Account 1	6/30/2004	-4.15
Account 1	7/31/2004	0.42
Account 1	8/31/2004	-4.24
Account 1	9/30/2004	1.77
Account 1	10/31/2004	-3.16
Account 1	11/30/2004	-2.57
Account 1	12/31/2004	-2.58
Account 1	1/31/2005	-1
Account 1	2/28/2005	-3.12
Account 1	3/31/2005	-3.72
Account 1	4/30/2005	3.62
Account 1	5/31/2005	-4.8
Account 1	6/30/2005	4.63
Account 1	7/31/2005	4.73
Account 1	8/31/2005	-2.35
Account 1	9/30/2005	1.62
Account 1	10/31/2005	-1.64
Account 1	11/30/2005	-1.35
Account 1	12/31/2005	-2.22
Account 1	1/31/2006	-0.41
Account 1	2/28/2006	-1.53
Account 1	3/31/2006	2.91
Account 1	4/30/2006	0.44
Account 1	5/31/2006	3.08
Account 1	6/30/2006	1.46
Account 1	7/31/2006	4.41
Account 1	8/31/2006	0.33
Account 1	9/30/2006	-4.56
Account 1	10/31/2006	-3.71
Account 1	11/30/2006	2.88
Account 1	12/31/2006	-1.44
Account 1	1/31/2007	3.95
Account 1	2/28/2007	1.02
Account 1	3/31/2007	4.67
Account 1	4/30/2007	0.56
Account 1	5/31/2007	4.11
Account 2	6/30/2004	-3.4
Account 2	7/31/2004	1.25
Account 2	8/31/2004	-4.51
Account 2	9/30/2004	-2.92
Account 2	10/31/2004	-1.41
And, let's say I was looking to calculate the standard deviation of a three year period, ending 3/31/07, for Account1. Querying the raw data would be a cinch - Select ACT Number, Return From Monthly Returns Where Date Between '4/30/04' and '3/31/07'. I've never tried to calculate a mean in SQL, but I believe there's a SQL function where I could nest a query to calculate the mean of the specified date range.

How'd you do Standard Deviation, though? I assume you're familiar with the formula, but essentially, it's the square root of the sum of the differences from the mean, squared, and then divided by the number of observations. I.E, if your mean is 3 and an observation is 5, then (5-3)2, added to the sum of the square of every other difference from the mean, that resulting number divided by the total number of observations, and then take the square root of THAT number.

For the sake of simplicity, let's say I'm cool with a result format of ACCT|DATE|RETURN|MEAN|SDEV where the date and return are for that period, and the mean and sdev are for the entire sample. I'd imagine it's much easier to do that than to return just the standard deviation, and sometimes it's good to see the source data.
 

·
Is Actually Recording
Joined
·
32,765 Posts
Discussion Starter · #18 ·
ever heard of notepad? :squint:
I mean, somewhere where our reporting engine can retrieve it easily, and I don't have to scroll down for 1,254,823 minutes to find the periodic return for June, 2002 for one particular account. :fawk:
 

·
Premium Member
Joined
·
23,428 Posts
I use SQL almost daily, been on it all morning, infact. And, once I get off lunch, I'll be running my queries in a series of scripts in a MiniTAB/SAS program.

[action=Leon]approves of this thread.[/action]
 

·
Slow Money
Joined
·
14,612 Posts
I mean, somewhere where our reporting engine can retrieve it easily, and I don't have to scroll down for 1,254,823 minutes to find the periodic return for June, 2002 for one particular account. :fawk:
Jesus and he needs a search function

Fine, use emacs :lol:
 
1 - 20 of 26 Posts
Top