Days since last entry (1 Viewer)

[ejstefl]

All,

I have a table that contains balances for certain accounts by date. At certain intervals, a new record is added for each account. I am trying to measure the intervals for each record (basically, the number of days since the last balance).

The data looks like this:

BalanceID AccountID BalanceDate Balance
1 1 10/1/08 100
2 1 9/29/08 150
3 1 9/15/08 100
4 2 10/30/08 500
5 2 10/15/08 600
6 2 10/7/08 500

So what I would like to do is basically write a query that would give me a column that would indicate the number of days since the last time it was balanced, like so:

BalanceID AccountID BalanceDate Balance LastBalanceDate DaysSinceLast
1 1 10/1/08 100 9/29/08 2
2 1 9/29/08 150 9/15/08 14
3 1 9/15/08 100 N/A N/A
4 2 10/30/08 500 10/15/08 15
5 2 10/15/08 600 10/7/08 8
6 2 10/7/08 500 N/A N/A

Thanks!!

 

[MSAccessRookie]

Check out the DateDiff() Function. It might be what you want.

DateDiff("d",LastBalanceDate,Date())

 

[ejstefl]

Check out the DateDiff() Function. It might be what you want.

DateDiff("d",LastBalanceDate,Date())

Thanks for the reply! DateDiff doesn't help me - the problem is that the two dates I want to compare are in different records. Any other ideas?

 

[MSAccessRookie]

What do you mean Different REcords?

 

[ejstefl]

What do you mean Different REcords?

If you look at the example data I provided, the first set is what I have to work with. What I want to do is compare the date in BalanceID 1 to the date in BalanceID 2. The second set is what I want the resultant query to look like. Sorry, the tables didn't come out as clear as I would have wanted...

 

[Rabbie]

Tr this query. It is not quite there(no N/a values yet) but see if we are on the right lines

SELECT Table1.BalanceID, Table1.AccountID, Table1.BalanceDate, Table1.Balance, (SELECT TOP 1 Dupe.Balancedate            
   FROM Table1 AS Dupe                     
   WHERE Dupe.AccountID = Table1.AccountID
     AND Dupe.BalanceDate < Table1.BalanceDate) AS LastBalance
FROM Table1;

 

[MSAccessRookie]

Tr this query. It is not quite there(no N/a values yet) ut see if we are onthe right lines

SELECT Table1.BalanceID, Table1.AccountID, Table1.BalanceDate, Table1.Balance, (SELECT TOP 1 Dupe.Balancedate            
   FROM Table1 AS Dupe                     
   WHERE Dupe.AccountID = Table1.AccountID
     AND Dupe.BalanceDate < Table1.BalanceDate) AS LastBalance
FROM Table1;

Good One Rabbie. This should work for all cases except where there is only one record in the table. A small modification will be required to handle that case.

 

[WayPay]

rabbie beat me to it, but here goes:

SELECT
BalanceID,
AccountID,
BalanceDate,
Balance,
Nz(
(SELECT Max(prev_mutation.BalanceDate)
FROM mutation AS prev_mutation
WHERE prev_mutation.BalanceDate < mutation.BalanceDate
AND prev_mutation.AccountID = mutation.AccountID),
"N/A"
) AS PreviousBalanceDate,
Nz(
DateDiff( "d",
(SELECT Max(prev_mutation.BalanceDate)
FROM mutation as prev_mutation
WHERE prev_mutation.BalanceDate < mutation.BalanceDate
AND prev_mutation.AccountID = mutation.AccountID),
mutation.BalanceDate),
"N/A"
) AS DaysSinceLastMutation
FROM mutation

Personally, I'd do without the Nz(blah,"N/A") because it forces the last two columns into string datatype.

And BTW, your data is wrong the BalanceID sequence counts back as time progresses. I used this to get the previous mutation (I turned the BalanceIDs around), because comparing on date could go wrong if there are multiple mutations on one account on one day.

 

[ejstefl]

This works on my test dataset, but when i try it on my actual table, I get the very first date the account was balanced for all the records that have that account.

SELECT dbo_Balances.BalanceID, dbo_Balances.AccountID, dbo_Balances.Balance_Date, dbo_Balances.Difference, (SELECT Top 1 Dupe.Balance_Date FROM dbo_Balances AS Dupe WHERE Dupe.AccountID = dbo_Balances.AccountID AND Dupe.Balance_Date < dbo_Balances.Balance_Date) AS LastBalance
FROM dbo_Balances
WHERE (((dbo_Balances.Balance_Date)>#6/1/2008#));

Here is what I came up with, which looks like it works. Any idea for optimization?

SELECT dbo_Balances.BalanceID, dbo_Balances.AccountID, dbo_Balances.Balance_Date, dbo_Balances.Difference, dbo_Balances.SEI_Shares_0300, dbo_Balances.SEI_Shares_0400, Max(tblComp.Balance_Date) AS LastBalanceDate, dbo_Balances.Balance_Date-Max(tblComp.Balance_Date) AS DaysSinceLastBalance 
FROM dbo_Balances LEFT JOIN dbo_Balances AS tblComp ON (dbo_Balances.Balance_Date>tblComp.Balance_Date) AND (dbo_Balances.AccountID=tblComp.AccountID)
GROUP BY dbo_Balances.BalanceID, dbo_Balances.AccountID, dbo_Balances.Balance_Date, dbo_Balances.Difference, dbo_Balances.SEI_Shares_0300, dbo_Balances.SEI_Shares_0400
HAVING dbo_Balances.Balance_Date>=#6/1/2008#;

 

[ejstefl]

Thanks for the replies, guys! I think I've got it!!