Help with a SELECT query used in SSIS package

  • 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

  • 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

  • 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

  • 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

  • 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

  • Oh, I see. Thanks to Wikipedia I found out who he was.

    He died when I was 7, that's why I don't know him...

    -- Gianluca Sartori

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply