February 19, 2018 at 11:10 am
The title might be a bit non-specific but I couldn't think of any better way to describe the issue.
I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set a third columns value to true and send an email to a stored email address.
It is not possible to change the database structure.
I have been searching quite some time now and still have no idea on where to start solving this.
February 19, 2018 at 11:32 am
Student031 - Monday, February 19, 2018 11:09 AMThe title might be a bit non-specific but I couldn't think of any better way to describe the issue.I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set a third columns value to true and send an email to a stored email address.
It is not possible to change the database structure.
I have been searching quite some time now and still have no idea on where to start solving this.
One option is to set up a job to poll the table at regular intervals and check the date and time columns and do the process if both have passed.
Sue
February 19, 2018 at 2:23 pm
Student031 - Monday, February 19, 2018 11:09 AMThe title might be a bit non-specific but I couldn't think of any better way to describe the issue.I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set a third columns value to true and send an email to a stored email address.
It is not possible to change the database structure.
I have been searching quite some time now and still have no idea on where to start solving this.
What are the datatypes for the "date", "time", and "third" columns?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 19, 2018 at 3:13 pm
Jeff Moden - Monday, February 19, 2018 2:23 PMWhat are the datatypes for the "date", "time", and "third" columns?
Date, Time and Bit.
I already thought about writing a procedure and trigger that procedure every hour (or after an other time period).
However I would like to do this in real-time if possible without having one server completely dedicated to running only one script that looks for rows that meet the requirements.
December 26, 2018 at 4:17 am
This was removed by the editor as SPAM
December 26, 2018 at 9:10 am
inshah2297 - Wednesday, December 26, 2018 4:17 AMRBAR is articulated ree-bar and is a Modenism for Line By-Anguishing Column. Initial move towards the change in outlook of composing Set Based code:
Quit pondering what you need to do to a column... think, rather, of what you need to do to a segment. On the off chance that you think its costly to procure an expert to carry out the responsibility, hold up until the point when you enlist a novice. - Red Adair When you put the correct level of turn on it, the number 318 is likewise a glyph that depicts the idea of a DBAs work. Wink.
Actually, it's a Modenism for "Row By Angonzing Row". 😉 And there's no such thing as a "segment". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2018 at 9:37 am
Student031 - Monday, February 19, 2018 3:13 PMJeff Moden - Monday, February 19, 2018 2:23 PMWhat are the datatypes for the "date", "time", and "third" columns?
Date, Time and Bit.
I already thought about writing a procedure and trigger that procedure every hour (or after an other time period).
However I would like to do this in real-time if possible without having one server completely dedicated to running only one script that looks for rows that meet the requirements.
Ok. The other question I have is what do you mean by "If both have passed " in your original post? What are the qualifications of something "passing" and, yes, I'm assuming that "passed" means passing some sort of test.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 26, 2018 at 9:48 am
Assuming that you mean that the stored date and time have been passed by the current date and time, your only choice to poll the data on a regular schedule, whether that is hourly or every minute. Unfortuately there are no temporal triggers that will fire when such an event occurs.
December 26, 2018 at 2:45 pm
Student031 - Monday, February 19, 2018 3:13 PMJeff Moden - Monday, February 19, 2018 2:23 PMWhat are the datatypes for the "date", "time", and "third" columns?
Date, Time and Bit.
I already thought about writing a procedure and trigger that procedure every hour (or after an other time period).
However I would like to do this in real-time if possible without having one server completely dedicated to running only one script that looks for rows that meet the requirements.
Not sure you're gonna find a "real time" solution, but if all the job is doing is checking one table, and if a row is found set a flag and send an email, there isn't any reason you couldn't have it run every minute or heck, every 10 seconds if you really wanted to. If you need it to only process one row at a time, just have it look for the TOP 1 row that meets your condition, ordered by the date and time.
December 27, 2018 at 12:39 pm
I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set the value of a third column to true and send an email to a stored email address.
It is not possible to change the database structure.
Date and time should not be split apart like this. This design flaw is referred to as attribute splitting. For example, would you keep dollars in one column and since in another? No, of course not! I also have no idea what the meaning of your assembly language style Boolean flag would be. We don't use flags like this in RDBMS.
I'm sorry, but you can not correct this design error, then you are screwed.
Please post DDL and follow ANSI/ISO standards when asking for help.
December 27, 2018 at 1:08 pm
Student031 - Monday, February 19, 2018 11:09 AMThe title might be a bit non-specific but I couldn't think of any better way to describe the issue.I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set the value of a third column to true and send an email to a stored email address.
It is not possible to change the database structure.
Date and time should not be split apart like this. This design flaw is referred to as attribute splitting. For example, would you keep dollars in one column and since in another? No, of course not! I also have no idea what the meaning of your assembly language style Boolean flag would be. We don't use flags like this in RDBMS.
I'm sorry, but you can not correct this design error, then you are screwed.
Unfortunately, Mr. Celko, sometimes you are stuck with what you have unless you can convince the powers that be that a redesign ot the database and the application is financially feasible.
December 27, 2018 at 1:37 pm
Student031 - Monday, February 19, 2018 11:09 AMThe title might be a bit non-specific but I couldn't think of any better way to describe the issue.I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set the value of a third column to true and send an email to a stored email address.
It is not possible to change the database structure.
Date and time should not be split apart like this. This design flaw is referred to as attribute splitting. For example, would you keep dollars in one column and since in another? No, of course not! I also have no idea what the meaning of your assembly language style Boolean flag would be. We don't use flags like this in RDBMS.
I'm sorry, but you can not correct this design error, then you are screwed.
Joe... with all due respect, that isn't helpful. That isn't even an attempt at being helpful.
Attribute splitting and Boolean flags are design flaws, yes... but they don't make the data unworkable. The date and time columns can be combined for the purpose of filtering; and it's quite simple to derive the meaning of his flag based on his description of the process. If you want to point out design flaws while offering help, that's great... that way they'll know better next time... But just bashing the guy for a design he likely had no say in creating isn't even remotely helpful.
Anyone who works in SQL occasionally finds themselves confronted with designs that aren't optimal, and sometimes downright awful. And in a lot of cases, changing them just isn't an option.
There is absolutely no reason that the OP can't do what they're describing given the schema they have, even if it isn't strictly correct.
December 28, 2018 at 8:58 am
Student031 - Monday, February 19, 2018 11:09 AMThe title might be a bit non-specific but I couldn't think of any better way to describe the issue.I don't know if the following is even possible in SQL so if not I would love to hear it as well including what you would use as a solution.
I have a table that had a column that stores a date and a column that stores a time. If both have passed I need to set the value of a third column to true and send an email to a stored email address.
It is not possible to change the database structure.
Date and time should not be split apart like this. This design flaw is referred to as attribute splitting. For example, would you keep dollars in one column and since in another? No, of course not! I also have no idea what the meaning of your assembly language style Boolean flag would be. We don't use flags like this in RDBMS.
I'm sorry, but you can not correct this design error, then you are screwed.
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:
December 28, 2018 at 9:00 am
Jeff Moden - Wednesday, December 26, 2018 9:37 AMStudent031 - Monday, February 19, 2018 3:13 PMJeff Moden - Monday, February 19, 2018 2:23 PMWhat are the datatypes for the "date", "time", and "third" columns?
Date, Time and Bit.
I already thought about writing a procedure and trigger that procedure every hour (or after an other time period).
However I would like to do this in real-time if possible without having one server completely dedicated to running only one script that looks for rows that meet the requirements.
Ok. The other question I have is what do you mean by "If both have passed " in your original post? What are the qualifications of something "passing" and, yes, I'm assuming that "passed" means passing some sort of test.
maybe he meant the time has "passed" as in not future? that's was my guess.
December 28, 2018 at 11:02 am
Student031 - 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.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply