• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

SELECT with IF Else and multiple date ranges

Hello,

I have a database with the following info:
Date
Ticker
Price
SharesOutstanding
dSO (change in shares outstanding)

Definitions:
AUM (Assests under management): (Price * SharesOutstanding) AS AUM
Flows (Change in AUM): (Price * dSO) AS Flows

I am trying to pull the following for a list of tickers in a single query.

Assets as of 08/31/2011 (this would be pulling the AUM of 8/31/2011)
Flows from 01/01/2010 – 08/31/2010 (SUM(Flows) over date range)
Flows from 01/01/2011 – 08/31/2011
Flows from 08/01/2011 – 08/31/2011

I believe that I would do this by pulling the data for my earliest date to my last date and then creating columns with calculation in them. However I am not sure how to do this.

Any ideas?
0
KOvitt
Asked:
KOvitt
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
I believe that I would do this by pulling the data for my earliest date to my last date and then creating columns with calculation in them...

Exactly. That is called conditional aggregates. You may wish to make this based dynamic (formulaic) dates later, but for now using static values you listed, this may look something like this:

SELECT /*...,*/ SUM(CASE WHEN [Date] >= '20100101' AND [Date] < '20100901' THEN (Price * dSO) ELSE 0 END) AS FlowsPriorYTD
FROM your_table
WHERE [Date] >= '20100101' AND [Date] < '20110901'
;

Hope that helps!
0
 
KOvittAuthor Commented:
Worked like a charm, Thank you soo much!! You are quite the SQL guru :D
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now