We have written a custom report in our system for promoter settlement reports. However, because our local governments decided they were going to change the tax rates in the middle of the fiscal year, we had need of a report that could have a user defined tax rate and look at revenue based on order date. Everything seemed to be working just fine, when we ran the report from opening sales to the current date, it seemed to work fine and all revenue and ticket totals matched up. However, when we start splitting up the revenue based on a specific date, we will run from opening sales to an arbitrary date, and then the next day to the day of the show. When we add together the reported revenue from the 2 reports, it has never matched the report for the overall sales. And its never consistent; we set the split date as one day and the 2 reports are short from the overall revenue. We set it a few days prior and the 2 reports are over the overall revenue. We made sure the order dates were within a second of another so we weren't missing any sales.
We attempted to change the qualifying date to that of the MAX(trn_dt) from T_TRANSACTION, but that still didn't report consistent totals. Does anyone have a report like this or have run into similar issues when using order or transaction dates? I just feel like the procedures are either missing transactions or counting them twice based on the date in which we split the report, just can't figure out why.