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:
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.
If you are using Reporting Services, you can use data driven subscription and send customized & personalized reports via emails to your delivery list automatically
Member
23 Points
149 Posts
Send Email based on Query
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.
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.
Contributor
6071 Points
1439 Posts
Re: Send Email based on Query
Jan 16, 2019 05:52 AM|eralper|LINK
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
SQL Server 2017