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.
On second thought, that alone will not get you what you want. You would get every batch since each day you close batches. You may need to get creative with your query. You could use CTE's. One for each of the 3 dates you are comparing along with batch_no. This would allow you to join them on batch_no and look for discrepancies.
I'm not quite sure what you mean: it seems to me that the query will find what Michael Dorseyis looking for.
Certainly for our business, postings usually happen on a different day to batch close, but the other orgs in our consortium do nightly postings after COB.
select b.batch_no, b.create_dt, b.close_dt, b.posted_dt, b.* from T_BATCH as b where not ( CAST(b.create_dt as date) = CAST(b.close_dt as date) and CAST(b.close_dt as date) = CAST(b.posted_dt as date) );
Note that using the NOT here means that you will see rows with null dates, and therefore see batches that aren't posted. If that's a problem you'd just add "and b.post_no is not null".