SQL Server - Select last value given a date

Question

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 <= '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!

Answer 1

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;

Answer 2

Sounds like you don't care about the records unless they are of type "Balance Update." Also looks like you're just trying to return the highest of the "balance Update" records, and not a Sum of them.

In that case, I think this will do the trick.

This query shows what the second one is doing, which is using the ROW_NUMBER functionality to order the records by the criteria we're specifying:

SELECT [Investor_Id]
,[Date]
,[Type]
,[Amount]
,Row_Number() OVER (PARTITION BY [Investor_ID] ORDER BY [Date] DESC) AS RowNum
FROM #TempLastValueTable
WHERE [Type] = 'Balance Update'
AND [Date] <= '12/31/2015';

This query should get you the two rows you are looking for.

SELECT main.*

 FROM (SELECT [Investor_Id]
 ,[Date]
 ,[Type]
 ,[Amount]
 ,Row_Number() OVER (PARTITION BY [Investor_ID] ORDER BY [Date] DESC) AS RowNum
 FROM #TempLastValueTable
 WHERE [Type] = 'Balance Update'
 AND [Date] <= '12/31/2015')main

 WHERE main.RowNum = 1;

Edit: had to change things around a bit once I could actually replicate your issue.