MySQL Query to get endpoints within a date range?

Question

I have a database that contains transactions made in client investments. For each quarter (four months), there is one opening balance, one closing balance, and N transactions in between.

The table is structured as follows:

transaction_id (primary key)
investment_id (index, foreign key)
amount (decimal(11,2))
type (ENUM:'Opening','Closing','Transfer')
transaction_date (DATE)

I need to create a view that will, for each investment_id, retrieve the first transaction of type 'Opening', the last transaction of type 'Closing', and all N transactions of type 'Transfer' in between for a particular date range. However, no transactions of type 'Opening' or 'Closing' should be included other than the endpoints in that range. For example:

SELECT * FROM view_transactions
WHERE transaction_date > '2000-01-01' AND transaction_date < '2006-06-01'
AND investment_id = 6578734

Thanks!

possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Barmar, Feb 06 '14 at 18:15
Use the technique in that answer to get the opening and closing transactions in each group, and combine them with a `UNION`. — Barmar, Feb 06 '14 at 18:16
I could do this all in one query, but the problem is I want to store most of that query in a view so that in the application queries all the must be specified is the investment_id and a date range. I'm not sure if this is possible though since the query inside the view will be executed before the WHERE clauses of the outer query. — Paul, Feb 06 '14 at 18:21

Amir Keibi · Answer 1 · 2014-02-06T18:45:54.113

0

CREATE FUNCTION pFunc1() returns DATE DETERMINISTIC NO SQL return @param;
CREATE FUNCTION pFunc2() returns DATE DETERMINISTIC NO SQL return @param;

CREATE VIEW view_transactions AS
   SELECT * 
   FROM transactions
   WHERE
        type = 'Opening' AND
        transaction_date > pFunc1() AND
        transaction_date < pFunc2()
   ORDER BY transaction_date
   LIMIT 1
   UNION
   SELECT *
   FROM transactions
   WHERE
        type = 'Transfer' AND
        transaction_date > pFunc1() AND
        transaction_date < pFunc2() AND
   UNION
   SELECT *
   FROM transactions
   WHERE
        type = 'Closing' AND
        transaction_date > pFunc1() AND
        transaction_date < pFunc2()
   ORDER BY transaction_date DESC
   LIMIT 1;

and then:

  SELECT v.*
  FROM (select @param:='2000-01-01' pFunc1) param1, (select @param:='2000-06-01' pFunc2) param2 view_transactions v;

edited Feb 06 '14 at 18:45

answered Feb 06 '14 at 18:22

Amir Keibi

1,991
28
45

Yeah that's the main problem that I'm trying to get around. I wasn't sure if there would be some way of doing this so that additional opening and closing transactions would not be included – Paul Feb 06 '14 at 18:26
Would there be any way of passing the WHERE clauses on the transaction_date to the subqueries? – Paul Feb 06 '14 at 18:29
I doubt that. You can create a procedure in that case. – Amir Keibi Feb 06 '14 at 18:33
Also, this query would only select the earliest opening transaction and last closing transaction out of all transactions, and so it would not include for each group of investment_id – Paul Feb 06 '14 at 18:33
The query in the view is generic, of course. You'll have to filter it when you query the view. – Amir Keibi Feb 06 '14 at 18:34
But in fact the entire view will only return ONE opening transaction and ONE closing transaction instead of one for each group of investment_id. Thus, when I do query the view, I likely won't get any opening or closing transactions for a particular investment – Paul Feb 06 '14 at 18:36
You are right. I revised the answer to depict a workaround for passing the parameters to the view. – Amir Keibi Feb 06 '14 at 18:47

MySQL Query to get endpoints within a date range?

1 Answers1