Last post Jan 16, 2019 05:52 AM by eralper
Jan 15, 2019 06:55 PM|RuthlessRoth|LINK
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.
CURRENT_TIMESTAMP as now
,DATEADD(hh,0, GETDATE()) as thetime
,DATEADD(hh, -1, GETDATE()) as hourless1
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())
the datetime the receipt was done
PO Agent email address
Vendor the PO was written to
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.
Jan 16, 2019 05:52 AM|eralper|LINK
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