locked
Send Email based on Query RRS feed

  • Question

  • User1215529056 posted

    What I need to do:

    Have an email go out to the Purchasing Agent that their product has arrived.

    Create Scheduled Job to run every hour to see if any P.O.s have been received in the past hour and send out emails to each PO Agent

    with their specific POs information.

    I have this query that gets what I need based on what happened for the past hour. The first 3 fields are for testing only.

    SELECT
    CURRENT_TIMESTAMP as now
    ,DATEADD(hh,0, GETDATE()) as thetime
    ,DATEADD(hh, -1, GETDATE()) as hourless1
    ,PoReceipt.CreateDatim
    ,ReceiveDate
    ,POHeader.POAgent
    ,OrderHeader.csr
    ,Employee.Email
    ,POLineItem.Description
    ,VendorName
    ,POLineItem.Jobnumber
    FROM POReceipt
    LEFT JOIN POLineItem ON POReceipt.PONumber = POLineItem.PONumber
    AND POReceipt.ItemNumber = POLineItem.ItemNumber
    LEFT JOIN POHeader ON POLineItem.PONumber = POHeader.PONumber
    LEFT JOIN Orderheader ON POLineItem.jobnumber = orderheader.JobNumber
    LEFT JOIN Employee ON Orderheader.CSR = Employee.EmployeeName
    LEFT JOIN Vendor ON POHeader.VendorID = Vendor.VendorID
    WHERE POLineItem.LineItemType = 0
    AND email <> ''
    AND Convert(DATE, POReceipt.Receivedate) = Convert(DATE, Getdate())
    AND POReceipt.CreateDatim between DATEADD(hh,-1, GETDATE()) and DATEADD(hh,0, GETDATE())

    It returns:query

    the datetime the receipt was done

    POAgent name

    PO Agent email address

    Vendor the PO was written to

    PO Description

    PO Number.

    I can return more than 1 row for more than 1 PO agent.

    The problem I am having is how to send an email to each agent for each PO that was received that hour

    and use the PO Agent Email that is returned in the query to send the email to.

    Tuesday, January 15, 2019 6:55 PM

All replies

  • User-595703101 posted

    Hello RuthlessRoth,

    If you are using Reporting Services, you can use data driven subscription and send customized & personalized reports via emails to your delivery list automatically

    If you have SSRS installed but not used data driven subscription method for distributing reports to consumers, please have a look at the tutorial Create Data-Driven Subscription on SQL Server Reporting Services

    Wednesday, January 16, 2019 5:52 AM