CODEWITHSUNDEEP
sqlt-sql
totalfreakingnoob
totalfreakingnoob

Reputation: 423

Retrieve value from the closest available date

I have a system which stores the history of a balance in a history table. The table has the account number, sort code, balance, balance start date, balance end date. When a balance is updated an entry is created in the history table which shows what the balance, what the date was when that balance first started and the date which shows when the balances changed. So for example the table will show a balance of $100.00 and this balance ran from 07/10/2013 to 07/15/2013.

What I'm trying to do is get the sum of the balances for all sort codes on a specific day however the balance may not have changed on this date so I would need to return the closest prior date but I'm failing.

This is what I tried so far.

declare @sdate datetime
set @sdate = '06/08/2012' --mm/dd/yyyy

SELECT CONVERT(varchar(20),MAX(StartDate),103) as "Closest Start Date",  Sort, SUM(Balance) AS "Sum of balances"
    FROM BalanceHistory
        WHERE StartDate <= convert(smalldatetime ,@sdate) AND SortCode <> 'ABC123456'
            GROUP BY SortCode

SELECT FROM BalanceHistory would produce something like

AccountNumber, SortCode, Balance, StartDate, EndDate, RECID
00000001, srt010203, 100.00, 06/01/2013, 06/02/2013,  RECID
00000001, srt010203, 110.00, 06/02/2013, 06/03/2013,  RECID
00000001, srt010203, 120.00, 06/03/2013, 06/04/2013,  RECID
00000002, srt010204, 200.00, 06/01/2013, 06/02/2013,  RECID
00000003, srt010204, 300.00, 06/01/2013, 06/02/2013,  RECID
00000004, srt010205, 400.00, 06/01/2013, 06/02/2013,  RECID
00000005, srt010205, 500.00, 06/01/2013, 06/02/2013,  RECID

Upvotes: 0

Views: 100

Answers (2)

Hart CO
Hart CO

Reputation: 34774

You can do this without a JOIN by using the ROW_NUMBER() function (assuming SQL Server 2005 or newer):

DECLARE @sdate DATE
SET @sdate = '2012-06-08'
SELECT SortCode, SUM(Balance)'Sum of Balances'
FROM (SELECT AccountNumber,SortCode, Balance,ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY StartDate DESC)'RowRank'
        FROM BalanceHistory
        WHERE StartDate <= @sdate AND SortCode <> 'ABC123456'
     )sub
WHERE RowRank = 1
GROUP BY SortCode

Demo: SQL Fiddle

The ROW_NUMBER() function in the subquery assigns a 'RowRank' to the balance for each accountnumber, we order by StartDate DESC to get rank of '1' for the most recent balance for each accountnumber, the WHERE criteria limits it to most recent balance from the date you set in your variable. Then you use that rank in the outer query to limit only to that one balance.

Upvotes: 1

SerenityNow
SerenityNow

Reputation: 1065

This should work

Declare @table as table
(AccountNo int,
 Balance money,
 DateEntered datetime)

 Declare @dt datetime

 set @dt = '2013-07-01'
 Insert into @table values(1, 100, '2013-04-01')
Insert into @table values(2, 200, '2013-04-01')
Insert into @table values(2, 300, '2013-05-01')
Insert into @table values(2, 400, '2013-06-01')
  --select AccountNo, Max(Balance), Max(DateEntered) MaxDateEntered From @table where DateEntered <= @dt  group by AccountNo 


Select Sum(t.Balance) From @table t
inner join (select AccountNo, Max(Balance) Balance, Max(DateEntered) MaxDateEntered From @table where DateEntered <= @dt  group by AccountNo) tm
on t.AccountNo = tm.AccountNo and t.DateEntered = tm.MaxDateEntered

    enter code here

Upvotes: 0

Related Questions