Date range vis a vis missing record in transaction table

padmathiagarajan

Forums

Hi Brian,
I have two files. 
1. Stay database- which gives the date range for a particular record. For Eg:- Mr.A has stayed from 1st April 2017 to 4th April 2017.
2. Transaction database- This has the list of charges posted for each customer. So in this i would have day wise charges posted for Mr.A
Now i want to compare whether for every day stayed i.e. 1st April, 2nd April, 3rd April and 4th April the charges have got posted in transaction database or not.
How best can i do this using a script?
I am enclosing sample excel file for easy reference.
 
Regards
Padma 

Brian Element Tue, 08/29/2017 - 10:17

Hi Padma,

That was an interesting challenge.  How I ended up doing it was using the Stay Database to generate a transaction database with all the dates between the from and to dates.  I then used that new database to join with the transaction database to see if there is anything missing.  The rest is just doing a few summaries and joins.  I have attached the script.  I didn't do a dialog, if you go into the script I used constacts at the beginning to hold the field names and the names of the databases.

Let me know if you have any questions or if there is something that doesn't work (I didn't put any error checking into it).

Brian