December 28, 2018 at 11:51 am
jcelko212 32090 - Thursday, December 27, 2018 12:39 PMStudent031 - Monday, February 19, 2018 11:09 AM
He's not screwed, he just needs to evaluate the separate date and time columns and see if the combination describes something in the past. While I agree setting a flag is dodgy, he could still set that dodgy flag and escape the hypothetical screwing 😉
This page talks about converting separate date and time columns into a datetime:
I don't know if you're into Yiddish humor, but one of the classic stories is "Levitan the Tailor" in which a customer comes into the tailor shop to look at the suit. The suit is a total nightmare. The legs are different lengths the shoulders are not padded correctly and nothing else matches. So Levitan offers him a deal; you put on the suit, hunch over little bit for the short leg, pull up the long leg a bit, with the shoulder a little higher, and put his hand in the pocket, etc. (if you see this done as a vaudeville skit. It's really pretty funny physical comedy). Now walk down to the end of the block and if you don't get one compliment on your suit., then it is yours for free. Having nothing to lose, the customer does this. Sure enough, as soon as he comes out of the shop a little old lady comes up to him and ask who is his tailor. "Why it is Levitan, right here is his shop!", says the customer, "why do you want to know?" The woman replies, "anyone who can make a suit fit a hopelessly horribly deformed crippled like you must be the best tailor in New York City!"
I hope you see my point. The fact you can kludge it doesn't mean that it's not a horrible mess and you really should start over with a decent schema, proper datatypes and not get used to the idea that a kludge is a substitute for proper performance and design. In short, like Levitan's customer, you're screwed with this.
I do see your point, heck I would be embarrassed to tears to show you what sort of things I get to work with LOL The only thing that keeps folks like me sane tho is the dogged hope that maybe we aren't totally screwed, or if we are, at least we get paid for the rogering we have to put up with! 😀
*adjusting my ill fitting suit*
December 28, 2018 at 12:30 pm
patrickmcginnis59 10839 - Friday, December 28, 2018 8:58 AMjcelko212 32090 - Thursday, December 27, 2018 12:39 PMStudent031 - Monday, February 19, 2018 11:09 AMHe's not screwed, he just needs to evaluate the separate date and time columns and see if the combination describes something in the past. While I agree setting a flag is dodgy, he could still set that dodgy flag and escape the hypothetical screwing 😉
This page talks about converting separate date and time columns into a datetime:
I don't know if you're into Yiddish humor, but one of the classic stories is "Levitan the Tailor" in which a customer comes into the tailor shop to look at the suit. The suit is a total nightmare. The legs are different lengths the shoulders are not padded correctly and nothing else matches. So Levitan offers him a deal; you put on the suit, hunch over little bit for the short leg, pull up the long leg a bit, with the shoulder a little higher, and put his hand in the pocket, etc. (if you see this done as a vaudeville skit. It's really pretty funny physical comedy). Now walk down to the end of the block and if you don't get one compliment on your suit., then it is yours for free. Having nothing to lose, the customer does this. Sure enough, as soon as he comes out of the shop a little old lady comes up to him and ask who is his tailor. "Why it is Levitan, right here is his shop!", says the customer, "why do you want to know?" The woman replies, "anyone who can make a suit fit a hopelessly horribly deformed crippled like you must be the best tailor in New York City!"
I hope you see my point. The fact you can kludge it doesn't mean that it's not a horrible mess and you really should start over with a decent schema, proper datatypes and not get used to the idea that a kludge is a substitute for proper performance and design. In short, like Levitan's customer, you're screwed with this.
I do see your point, heck I would be embarrassed to tears to show you what sort of things I get to work with LOL The only thing that keeps folks like me sane tho is the dogged hope that maybe we aren't totally screwed, or if we are, at least we get paid for the rogering we have to put up with! 😀
*adjusting my ill fitting suit*
At my prior company we had a database that used EAV for most things, and we had column names like Col1, Col2, Col3... dates and times were split, and there were flags galore... Joe would probably faint if he saw it 😉
But unfortunately that was the schema... it came to us prepackaged from the vendor, and changing it would have meant changing literally hundreds of other objects in SQL, would have meant changing application code for multiple systems, and would have probably taken hundreds of hours to complete and test and deploy...
And so we just had to deal with it. The company wasn't going to invest that kind of time and money into fixing something that worked - and worked pretty well - just because there was non-standard SQL in the system. We couldn't just tell management "Sorry, we could easily do what you want done - and it'd be perfectly fine - but first we're going to insist you spend the next six months fixing a problem that is only technically a problem from a standards perspective... no, it works fine, it's more about the principle you see... wait, why are you calling for security?"
Truth be told over the years I've worked with dozens of enterprise systems and every single one of them had schema that weren't standard. That is the reality of the business.
It'd be one thing to give the OP a possible solution and them tell them oh by the way you really shouldn't design a table like that... but telling someone they're screwed because their schema doesn't meet the standard and acting like there is no other recourse but to scrap and redesign... that's not being helpful, that's being pedantic and rude.
December 28, 2018 at 1:13 pm
I didn't see the real schema of the table, but here is my advice:
while you cannot change the schema, you can add new tables to keep track of what you checked already.
say the table is dbo.SomeTable, and it has a primary key.
create a table Audit.Sometable that keeps track of the items from dbo.SomeTable that you have emailed/processed.
then a simple query like SELECT * FROM dbo.SomeTable WHERE PK NOT IN(SELECT PK FROM Audit.SomeTable) tells you what "needs" to be processed.
Process those rows, sending emails, extra calculations whatever, then insert the PK and other relevant information into the Audit schema Audit.SomeTable.
Show the real representative schema for real, tested answers.
Lowell
December 28, 2018 at 4:45 pm
I will grant that everybody probably has some bad code somewhere in their systems. In my case, I'm the guy that gets called in when the bad code has gotten so bad the company is hurting. I'm not so much of a doctor as I am a pathologist. Doing training classes was much more rewarding. On the other hand, it can pay very well 🙂
At the start of the .com insanity, I worked for a company that had an EAV database of the client site to compute the commission structure for insurance salesmen. One of the problems was the database just kept growing, but nobody actually knew what it was doing because the customer also kept changing his business rules. Unfortunately, the solution the company came up with was to buy more disks and just let it grow.
I'd been hired as "the database guy" for them. There is really no way to save this thing once it had been implemented. We mercifully got fired by the client, and then they went to a company specializing in insurance industry software. They took one of their packages and put it on a desktop instead of a minicomputer.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply