Hello,
I need help on how to deal with identifying missing dates, creating new records and assigning values to them.
The data looks like that:
Account_Number----------Currency-----------PostingDate--------OpeningBalance-----NetDaily---------Closing Balance
1234-------------------------USD--------------July 1, 2016----------$80-----------------+$20-----------------$100
1234-------------------------USD--------------July 3, 2016----------$100----------------+$40-----------------$140
1234-------------------------USD--------------July 4, 2016----------$120----------------+$10-----------------$130
1234-------------------------SGD--------------July 1, 2016----------$160-----------------$5-------------------$155
1234-------------------------SGD--------------July 7, 2016----------$180----------------+$0------------------$180
1235-------------------------USD--------------July 1, 2016----------$100---------------+$10------------------$110
1235-------------------------USD--------------July 3, 2016-----------$10------------------$5-------------------$5
So, the objective is - if a date for the Acc Number/Currency combination is missing - to create a new record (i.e. for 1234-USD for July 2, 2016) and assign the closing balance from July 1 to the opening balance of the newly created record ($100), put zero for the NetDaily and put the sum of the Opening plus NetDaily into closing ($100+$0, or simply $100 since there was no activity that day).
The time gap could be up to two weeks. The data is sent by individual banks in csv format and is uploaded nightly into the database. The banks are located in different countries and operate on their own schedules. If the bank is closed, there is no report.
A query based on the table feeds an excel spreadsheet via pivot table and displays a cash position per day. Since some dates are missing for some combos of (Acc Num, Curr) - the opening balance, Net daily and the closing balance display NULL. To make the report uniform I need the data to have all elements for all days.
I did google different resources and ask some other forums but no results so far. So, if anyone can help - that would be really great.
Thanks
Valeri