June 24, 2009 at 4:37 am
Hi, I have a query that returns Purchase Orders Line Items that have a 'Due Date' +6 days from current date. The SSIS package then sends an email to the supplier requesting confirmation of delivery.
The query can return multiple rows for the same purchase order as multiple lines may have the same Due Date.
In my test data Order '0040701' has 6 rows all with a due date + 6 days from now, with 3 different Qty Outstanding Values (5 / 10 / 20). In this instance 3 emails are sent to the supplier which is a bit of a pain.
I only want to send 1 email when the Due Date is + 6 from GETDATE() and QtyOutstanding > 0?
I have attached script to create Tables with Sample Data sepcific to the issue. The SELECT query is also included within the script.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 24, 2009 at 6:40 am
First of all, the jokes in your signature almost made me run away from this thread... They really are horrible!:hehe:
Your code is really long and complex, although I must admit that you posted everything is needed to work on it for a solution. There aren't many members around that would have spent even half the time you did to build up the test script. Kudos.
What you need here is an extra flag to mark rows to send email and rows not to send email. You can populate the column in a subquery or cte, or even create a temp table to mark the rows and then select them later.
I would do it this way:
WITH CTE (
SupplierId,
EmailAddress,
ProductId,
PurchaseOrderId,
DueDate,
QtyOutstanding,
row_num
) AS (
SELECT s.SupplierId,
s.EmailAddress,
p.ProductId,
po.PurchaseOrderId,
poi.DueDate,
SUM(poi.QuantityOutstanding) AS QtyOutstanding,
row_num = ROW_NUMBER() OVER (
PARTITION BY po.PurchaseOrderId, CASE WHEN ABS(DATEDIFF(day, GETDATE(), poi.DueDate)) <= 6 THEN 1 ELSE 0 END
ORDER BY po.PurchaseOrderId, poi.DueDate
)
FROM PurchaseOrderItems AS poi
INNER JOIN PurchaseOrders AS po
ON poi.PurchaseOrder = po.PurchaseOrder
INNER JOIN Suppliers AS S
ON po.Supplier = s.Supplier
INNER JOIN Products AS p
ON poi.Product = p.Product
WHERE po.SystemType = 'F'
AND s.EmailAddress IS NOT NULL
AND s.EmailAddress ' '
AND poi.DueDate = dateadd(dd, datediff(dd, 0, @TriggerDate), 0)
AND poi.QuantityOutstanding > 0
GROUP BY p.ProductId,
s.SupplierId,
po.PurchaseOrderId,
poi.DueDate,
s.EmailAddress
)
SELECT *
FROM CTE
WHERE row_num = 1
ORDER BY PurchaseOrderId
Is this the intended behaviour?
Let me know
Gianluca
-- Gianluca Sartori
June 24, 2009 at 8:41 am
Gianluca many thnaks for taking time to review my thread, I have struggled to get onto the site from the office.
I also reviewed my code and came up with the following;
DECLARE @TriggerDate smalldatetime
SET @TriggerDate = Getdate()+7;
WITH Test AS
(
SELECT
s.EmailAddress,
po.PurchaseOrderId,
DENSE_RANK() OVER (PARTITION BY poi.DueDate ORDER BY po.PurchaseOrderId) AS DR,
poi.DueDate,
poi.QuantityOutstanding
FROM PurchaseOrderItems AS poi
INNER JOIN PurchaseOrders AS po ON poi.PurchaseOrder = po.PurchaseOrder
INNER JOIN Suppliers AS S ON po.Supplier = s.Supplier
WHERE po.SystemType = 'F' AND s.EmailAddress IS NOT NULL AND s.EmailAddress ' ' AND poi.DueDate = dateadd(dd, datediff(dd,0,@TriggerDate),0)AND poi.QuantityOutstanding > 0
)
SELECT DR,PurchaseOrderId,DueDate,EmailAddress
FROM Test
GROUP BY
DR,
PurchaseOrderId,
DueDate,
EmailAddress
This appears to give what I am after, but please feel free to pass comment.
Many Thanks,
Phil.
PS: The jokes are Tommy Cooper classics!!
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 24, 2009 at 8:49 am
Glad you found a solution.
I'm sorry, but I don't know Tommy Cooper. He doesn't seem to be very popular here in Italy...:-)
Ciao
Gianluca
-- Gianluca Sartori
June 24, 2009 at 8:51 am
Poor old Tommy is dead.......
Thanks for all your efforts, your code looks neater than my attempt.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply