|
A longtime customer of mine recently switched receiving/inventory software and was having trouble reconciling between a specific report from the new system against values that my software imports. For some reason we have imported almost $300K more that their report shows for last month. Of course the assumption is that my import is wrong. The customer is unhappy, my colleague who has been trying to figure this out for a couple of days now is unhappy and they decide that I should get involved which makes me unhappy. 
Narrowing the focus, it became clear that the records their report is missing all fall on the ending day of the month. Looking closer, those missing records actually all have a datetime of 2018-09-30 23:59:59:000. Further, the time part of the datetime field for all records is either all 0s or maxed. 
Getting back to the source system and the report in question, I set the ending date to 2018-10-01 and viola, there are the missing records, however they are showing a date of 9/30 in the report. Interesting...now run the report for just 10/1 and they are gone. OK, got it...somebody forgot to make the ending date inclusive! 
I explain this all to the customer...my numbers are right, yours are wrong and here's why, along with 'you need to show this to the other software vendor'. Instead, they asked if I would send an email with the explanation that they could share with management and the other vendor. (so wtf did I just spend 15 minutes explaining it!  ) Sensing that I was about to say something sarcastic, my colleague agreed to do it...of course I had to explain it all over again and basically dictate the message anyway. That's almost 2 hours spent chasing problems in someone else's software. Can I please just work on my own stuff now? 
"Go forth into the source" - Neal Morse
|
|
|
|
|
"somebody forgot to make the ending date inclusive!"
This is correct behavior. Personally, I can't stand SQL's BETWEEN behavior.
|
|
|
|
|
PIEBALDconsult wrote: This is correct behavior.
Really, explain that to the customer that expects to see transactions that occurred at any time on the ending date they selected.
You are correct in terms of a SQL query, but not from a UI perspective. My habit when filtering on a single datetime field is to use BETWEEN and apply the max time to the ending date. This is why my import was correct. The problem is with the other vendors report and there's nothing I can do about it. Even advising them to simply extend the ending date by a day will not work because most records simply have all 0s as the time part. The ball is in their court.
"Go forth into the source" - Neal Morse
|
|
|
|
|
"use BETWEEN and apply the max time to the ending date."
No, that's very bad, you can't be sure that the end time you choose is "max enough".
The correct implemention is to set the end time to 00:00 the following day and remain exclusive.
|
|
|
|
|
PIEBALDconsult wrote: The correct implemention is to set the end time to 00:00 the following day and remain exclusive.
Right, but then I can't use Between which means more typing...all for the tradeoff of not worrying about maybe 1 transaction in a million years might actually hit in that precise millisecond!  Anyway, good point!
"Go forth into the source" - Neal Morse
|
|
|
|
|
If your looking to report on days, use days! I.e. "between convert(date,start_dt) and convert(date,end_dt)"
|
|
|
|
|
I don't know if this is a factor in this case, but don't forget that everybody also has to agree on timezone details. Bad assumptions about timezone offsets are enough to get the first (or last) couple of records for a given day to be dropped.
|
|
|
|
|
dandy72 wrote: timezone details.
Not a factor in this case, all reporting 68 reporting sites are in the same TZ and as mentioned, this vendor uses a datetime field which is not actual time recorded, but either with the time truncated (all 0s) or maxed, depending on which process created the record.
Also not a factor, DST!
"Go forth into the source" - Neal Morse
|
|
|
|
|
Good man. That can add an extra layer of complexity.
|
|
|
|
|
Are you saying they had 300K worth of orders in the second between 2018-09-30 23:59:59:000 and 2018-10-01 00:00:00:000?
modified 20-Oct-19 21:02pm.
|
|
|
|
|
No, I'm saying the time part of the datetime is made up. In their case, as mentioned, it's either all 0s or 23:59:59.000.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Yup. Vendors change their protocols and now I/we have to wallow in their filth.
As an aside - if you can get away with it, CAST(your_datetime, DATE) for your range and tests, as appropriate (going forward) and, for a little while, you've outsmarted them.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
W∴ Balboos wrote: if you can get away with it,
Unfortunately, it's not my system so I can't change their report/query, and would rightly refuse to do so if asked.
It's a minor error, but they are a large company...it could take months to get it fixed!
"Go forth into the source" - Neal Morse
|
|
|
|
|
kmoorevs wrote: A longtime customer of mine recently switched receiving/inventory software and was having trouble reconciling between a specific report from the new system against values that my software imports
...
they asked if I would send an email with the explanation that they could share with management and the other vendor
Ya done the right thing (mostly), but honestly, it wasn't your responsibility to debug the other vendor's software. Your responsibility ended at the point of telling the customer that a manual check of the data showed that your report was correct and the new vendor's one was in error.
Part of what that customer needs to know, is what the experience is like with the new vendor when their stuff is in error. They also need to know they can trust that the new vendor got things done right the first time. You robbed them of that. If that new vendor can't get basic stuff like this right, and especially if they need someone outside their company to tell them what they did wrong, then that customer should probably be reconsidering using that new vendor. Which they won't do if things go smoothly when they were not smooth.
BTW, it sounds like you actually went to the point of telling the new vendor how they processed the data wrong. If so, than that was slightly irresponsible of you -- without access to their code and test suite, you can't be sure that what you identified is their actual problem.
|
|
|
|
|
patbob wrote: without access to their code and test suite
Well, it was on a remote, initiated by the customer, and using their compiled product. Our product uses an account with read-only access to their database. We have had a relationship/agreement with this vendor for years and actually recommended them. Our product (like all of our direct connect imports) contains a query viewer for exporting and for troubleshooting just these type of issues. I modified our query to expose the time parts and just put 2 and 2 together.
Also, I only found their problem from investigating what everyone assumed was my problem. I was playing defense not offense. I didn't actually tell them how to fix their problem, only the steps required to duplicate the issue and my observation that the missing transactions have a time part and the others don't...a free clue.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Good job! The way to described it at first made it sounds like you were telling them how they'd pulled the data wrong, not just what about it seems to be missing/
|
|
|
|
|
Dates and (especially) times are a PITA to work with.
So are third parties.
The only thing that could make your story worse is if those reports were actually Crystal Reports
The last three bugs I "fixed" were in our CRM system (which I can't even fix because they took away my admin rights) and in a third party application (which I can't fix either).
Alright, so I didn't fix anything, but at least I figured out the problems and told someone else to fix it.
The problem is that OUR software "breaks" because THEY mess up.
And because I'm spending time finding those bugs I can't do my own work.
You have to be careful not to be known as the programmer who delivers bugs and does so at a slow pace!
|
|
|
|
|
As frustrating as it is, making a detailed writeup for a non-technical person to forward to another dev/etc is the lesser evil. The non technical person trying to regurgitate what they were told and getting critical details wrong is even worse.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, weighing all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
Quote: they asked if I would send an email with the explanation that they could share with management and the other vendor. (so wtf did I just spend 15 minutes explaining it! )
I never just tell the customer something like this - I always write it down first. If they don't understand or misunderstand and pass the wrong info along it's going to come back to you - "That's what the developer said." Now you waste more time (and rep) untangling the mess. Best is to write a quick explanation of the problem and a add couple possible approaches to resolution.
|
|
|
|
|
I have found that this process of explaining to non-technical people was is actually occurring is what occupies a vast portion of my time, and I don't have a problem with that. I have embraced it, and am rather good at it at this point. One point to consider is that coders are considered "dime a dozen," but someone who understands, and can explain, why something behaves the way it does is a valued asset to management.
In terms of your import being "wrong," I might contend you could improve your import by excluding the time. If it is always 0 or max value, why is it even stored? Why is the field not a "date" instead of a "datetime"? If you don't have control over the database, how about always clearing the time on import since it obviously serves no purpose. It would then accurately represent a date only albeit wasting five extra bytes, and potentially causing problems such as this, each time to do it.
As a developer, your job is not solely to satisfy the immediate requirement, importing the data in this case. It is to anticipate problems that MAY occur such as this reporting problem, and solve for those, as well. So, technically, I would consider those "2 hours spent chasing problems" AS working on your own stuff.
|
|
|
|
|
Thanks Greg, I think you have misunderstood the issue. My import pulls monthly data from another vendor's system. The customer uses a report from the source system to check what was imported and finds that the import has quite a bit more than the report. The assumption at that point is that my import is wrong.
Using the query viewer from my import, I can modify the query by removing the convert functions for the datetime to date, and reveal that the records not being pulled in their report (for the same date range) have a time element and all occur on the ending date. Using the other vendor's software, I can pull the same report extending the ending date by one day, and now those rogue records show up along with records for the 1st which I don't want. Again, this is the other vendor's report...nothing I can do about it...either a query problem or a UI problem. This is clearly the other vendor's issue.
I really wanted to stay out of it, which is why I demonstrated multiple time to the customer how this is a problem that must be addressed with the other vendor...just show them this report run under the two date ranges and they will surely see the problem.
Yes, that was two hours spent finding a problem with another software vendor's report.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Things like this are why I advocate for BA's with technical skills. Explaining why a system did what it did shouldn't require a developer.
In the absence of someone else who could explain, I feel like you did the right thing. The absolute worst thing any vendor (or department if internal) could tell a user is that it's someone else's fault/issue without an adequate explanation. Pointing fingers without backing it up is one of the quickest ways to loose a client's trust.
|
|
|
|
|
When word processing a presentation about guns, should you know how to use bullet points?
Sent from my Amstrad PC 1640
Never throw anything away, Griff
Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!
|
|
|
|
|
I Colt not agree more!
"If we don't change direction, we'll end up where we're going"
|
|
|
|
|
As long as your presentation uses blanks I don't care.
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
