table modification

Dear people

At my company we develop a hub between multinationals and all the banks and their accounts. We have the following issue:

There are days when we don't receive any transactions or closing balance value for a specific account (closing days for banks, weekends, holidays etc.). So there is just no record in the DB for that account on that specific day. I'm currently investigating how we can fill up these gaps in data.

I've already mentioned the possibility to copy the last known closing balance for an account into the gap (inserting fake records) in the company where I work. But sometimes it's possible that we suddenly receive an account statement a few days later and then the gap needs to be updated. The tricky part at this problem is when a certain gap needs to be updated, what about the possible gaps that come right after the updated day.

The table I work with is the fact balance table and it contains the following fields:
ID
Account_ID
DATE_ID
OB_AMOUNT
CB_AMOUNT

I was thinking to add an extra field named GAP_CODE and specify that the record is a gap or not.

Do you guys have any advise for me or experience in working with fake records in the banking industry?

Thanks in advance for the advise and expertise

Kind regard

Cedric from Belgium

Hi ,

one way you cold do this is to modify the query that is reteriving the data to pick up the previous days' closing balance if you have not recived for that day. That way you dont need to worry when you receive the closing day later.


thanks

If there are genuinely no transactions for a day then why do you need to create a record for that date? What's the business reason for not having gaps?
If there are transactions but you receive the information 'late' then why not fill in the gap(s) when you do receive the information - rather than creating dummy records that you subsequently have to go back and correct?