SQL Server - Select last value given a date To make it easier, I have data that looks like this as an example:



What I am trying to return is the Amount of the Balance Update as of a specified Date for each Investor_ID.  For example, if my date is 12/31/15, It would return two rows:


My query is:

SELECT Investor_ID,
(SELECT Max(CASE WHEN Type = 'Balance Update'
                 THEN Amount else 0 END)) as Val
FROM Table1
WHERE Date &lt;= '12/31/2015'


However this returns the maximum value of "Amount" for the date range (before 12/31/15) as opposed to the Amount at the Maximum date, 12/31/15.  

How can I restructure the query to where the Date can be changed, and return the LATEST "Amount" of "Balance Update" for each Investor_ID?

Thank you!
You can do this for all databases (even MySQL - which doesn't 
have Window functions or CTEs - i.e. WITH clauses).

To solve this, I did the following:

Created a table

CREATE TABLE table1 (Investor_ID int, my_date DATE, type VARCHAR(20), amount int);


Note, never have a column named "date" (or any other SQL keyword).

Populated the table:

INSERT INTO table1 VALUES (1, '2015-03-31', 'CONTRIBUTION'   ,  3);
INSERT INTO table1 VALUES (1, '2015-06-30', 'Balance Update' , 20);
INSERT INTO table1 VALUES (1, '2015-09-30', 'CONTRIBUTION' ,    5);
INSERT INTO table1 VALUES (1, '2015-12-31', 'Balance Update' , 10);
INSERT INTO table1 VALUES (2, '2015-03-31', 'CONTRIBUTION'   ,  8);
INSERT INTO table1 VALUES (2, '2015-06-30', 'Balance Update' , 38);
INSERT INTO table1 VALUES (2, '2015-09-30', 'CONTRIBUTION' ,    1);
INSERT INTO table1 VALUES (2, '2015-12-31', 'Balance Update' , 50);


And then ran the following query:

SELECT t1.my_date, t1.Investor_ID, t1.type, t1.amount
FROM table1 t1 
WHERE my_date = (SELECT MAX(my_date) FROM table1 t2 WHERE  t1.Investor_ID = t2.Investor_ID)


And the result was as desired:

my_date   |investor_id|               type|amount
--------------------------------
2015-12-31|1               |Balance Update    |10
2015-12-31|2               |Balance Update    |50


If you want to use Window functions, use this query:

SELECT main.* FROM
(
  SELECT Investor_Id
  ,my_date
  ,type
  ,amount
 ,Row_Number() OVER (PARTITION BY Investor_ID ORDER BY my_date DESC) AS RowNum
 FROM table1
) main
WHERE main.type = 'Balance Update'
AND rownum = 1;