Hello all,
I'm wondering if anyone has some SQL or some other method that helps them identify batches where the create, closing, and posting dates are different from one another. To be clear, I want to get a count of any batches where at least one of the three dates is not the same in a given time frame (i.e., 6/1/23-6/30/23). We're using Tessitura Merchant Services, and we've been noticing a lot of the reports from Adyen and Tessitura are at odds with one another. My suspicion is that this is because of batches opened on one date, and closed and/or posted on another date. Or if there is some easy, out-of-the-box way to get this, let me know what that is.
Thanks in advance,
Michael Dorsey
CRM Systems Manager
Spoleto Festival USA
Hi Michael,
Since dates record down to (nearly) the millisecond, you'll want to define how much wiggle room you want when matching dates.
Thanks Gawain! The date should be considered the same if it falls anywhere between 6/1/2023 00:00:00.000 and 6/1/2023 23:59:59.999. We just want the date to match--of course the time of closing would be different than that of opening, and posting would be slightly different still. If a batch is opened on 6/1 at 9:00 AM, closed on 6/1 at 4:00 PM, and posted on 6/1 at 5:00 pm, that batch would pass. If, however, the batch was opened on 5/31 at 9:00 AM and the other two dates remained the same, that batch would be flagged as having one of the dates out of line with the others. So I'll need to convert the date/time value to MM/DD/YYYY to make matching easier, that will definitely help.
I think you could just use the convert function to convert all the datetimes to dates. This would allow you to look for when any of the 3 dates don't match. Something like Where create_dt <> closing_dt or create_dt <> posting_dt or closing_dt <> posting_dt (using the convert function to convert each datetime to date). CONVERT(date,closing_dt)
Not at my desk to confirm syntax, but don't forget the NOT operator.