Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    11
    Provided Answers: 1

    Question Answered: Need Help With SQL

    Hello, SQL gurus. I'm a bit lost. In the following setup, I have 2 tables, PRODUCT and ALERT. The product is given a certain expiration date. In the alert table, we will store records to declare how long before the expiration date an alert is sent out (in days) to a given email address. I'm trying to put together a query that will give me a list of the alerts to send out. Here is an example of how it is structured with some sample data and desired output. Please help me construct SQL to put it all together.
    Thank you!!!!
    Tony

    PRODUCT
    ----------
    PRODID - NUMBER(3) PK [ProductID]
    EXPIREDATE - DATE [Expiration Date]
    PRODNAME - VC(15) [Product Name]

    ALERT
    ---------
    ALERTID - NUMBER(4) PK [Alert ID]
    PRODID - NUMBER(3) FK [Links Alert to Product ID]
    DAYRANGE -NUMBER (3) [Days before expiration to notify]
    EMAILADDRESS -VC(50) [Email address to send alert to]
    SENTFLAG -NUMBER(1) [Flag indicating if alert has been sent]


    PRODUCT
    ----------
    PRODID|EXPIREDATE|PRODNAME
    ===========================
    1 |08/10/2017|PRODUCTA
    2 |12/10/2017|PRODUCTB
    3 |09/05/2017|PRODUCTC
    4 |10/01/2017|PRODUCTD
    5 |10/05/2017|PRODUCTE

    ALERT
    ---------
    ALERTID|PRODID|DAYRANGE|EMAILADDRESS|SENTFLAG
    =============================================
    1 |1|30|USERA@DOMAIN.COM|
    2 |1|60|USERB@DOMAIN.COM|1
    3 |2|90|USERA@DOMAIN.COM|
    4 |3|30|USERA@DOMAIN.COM|
    5 |3|90|USERB@DOMAIN.COM|
    6 |4|30|USERA@DOMAIN.COM|
    7 |5|60|USERB@DOMAIN.COM|


    I want to
    Select PRODNAME, EMAILADDRESS where the EXPIREDATE-DAYRANGE is before or equal to TODAY(SYSDATE) and SENTFLAG != 1

    Desired Result:

    PRODNAME|EMAILADDRESS
    =======================
    PRODUCTC|USERB@DOMAIN.COM
    Last edited by boybles; 06-24-17 at 19:37.

  2. Best Answer
    Posted by boybles

    "Guess I was overthinking it. Here is a simple solution:
    select p.PRODNAME,a.EMAILADDRESS
    from product p, alert a
    where a.prodid=p.prodid
    and (p.EXPIREDATE-a.DAYRANGE) - trunc(sysdate) <= 0
    and a.SENTFLAG IS NULL

    Tony"


  3. #2
    Join Date
    Mar 2004
    Posts
    11
    Provided Answers: 1

    Overthinking

    Guess I was overthinking it. Here is a simple solution:
    select p.PRODNAME,a.EMAILADDRESS
    from product p, alert a
    where a.prodid=p.prodid
    and (p.EXPIREDATE-a.DAYRANGE) - trunc(sysdate) <= 0
    and a.SENTFLAG IS NULL

    Tony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •