How to implement taking action based in column value?

  • Student031

    SSC Veteran

    Points: 296

    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.

  • Sue_H

    SSC Guru

    Points: 90260

    Student031 - Monday, February 19, 2018 11:09 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.

    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

  • Jeff Moden

    SSC Guru

    Points: 994661

    Student031 - Monday, February 19, 2018 11:09 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.

    What are the datatypes for the "date", "time", and "third" columns?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Student031

    SSC Veteran

    Points: 296

    Jeff Moden - Monday, February 19, 2018 2:23 PM

    What 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.

  • This was removed by the editor as SPAM

  • Jeff Moden

    SSC Guru

    Points: 994661

    inshah2297 - Wednesday, December 26, 2018 4:17 AM

    RBAR 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 994661

    Student031 - Monday, February 19, 2018 3:13 PM

    Jeff Moden - Monday, February 19, 2018 2:23 PM

    What 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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442141

    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.

  • cphite

    SSCarpal Tunnel

    Points: 4112

    Student031 - Monday, February 19, 2018 3:13 PM

    Jeff Moden - Monday, February 19, 2018 2:23 PM

    What 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.

  • jcelko212 32090

    SSCrazy Eights

    Points: 8846

    Student031 - Monday, February 19, 2018 11:09 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 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. 

  • Lynn Pettis

    SSC Guru

    Points: 442141

    jcelko212 32090 - Thursday, December 27, 2018 12:39 PM

    Student031 - Monday, February 19, 2018 11:09 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 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.

  • cphite

    SSCarpal Tunnel

    Points: 4112

    jcelko212 32090 - Thursday, December 27, 2018 12:39 PM

    Student031 - Monday, February 19, 2018 11:09 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 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.

  • x

    SSC-Insane

    Points: 23438

    jcelko212 32090 - Thursday, December 27, 2018 12:39 PM

    Student031 - Monday, February 19, 2018 11:09 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 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:

    https://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server

  • x

    SSC-Insane

    Points: 23438

    Jeff Moden - Wednesday, December 26, 2018 9:37 AM

    Student031 - Monday, February 19, 2018 3:13 PM

    Jeff Moden - Monday, February 19, 2018 2:23 PM

    What 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.

  • jcelko212 32090

    SSCrazy Eights

    Points: 8846

    patrickmcginnis59 10839 - Friday, December 28, 2018 8:58 AM

    jcelko212 32090 - Thursday, December 27, 2018 12:39 PM

    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:

    https://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server

    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 19 total)

You must be logged in to reply to this topic. Login to reply