1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Development SQL Help Anyone?

Discussion in 'Tech Support' started by Midlight, 28 Apr 2021.

  1. Midlight

    Midlight Minimodder

    Joined:
    6 Jun 2011
    Posts:
    133
    Likes Received:
    74
    I have been having a little difficulty with some SQL I am trying to write. My skills have reached their limit and I'm hoping we have a guru on here somewhere.
    The issue is;
    When we complete an order we send a message to the client, they send an acknowledgment followed by either an acceptance or rejection.
    We do have multiple tables related to the completion message, however they only hold data for the most recent. Previously this was not an issue as we could only send one completion message. Now that we have the rejection replies available there can be multiple completion messages sent.
    The only table we have that has all the relevant data available is the Order_Status table, this records each message as a separate line. Outgoing message is a Status_ID 101 and the return messages are Status_ID 107,108 or 109.
    I'm wanting a single line output with Created_Date, Created_User and reply Status_ID for each 101 we send for each Order_Number.

    What I have currently is;

    Code:
    SELECT DISTINCT a.ID
    , a.Order_Number
    , a.Created_Date
    , a.Created_By
    , FIRST_VALUE(b.Created_Date) OVER (PARTITION BY b.Order_Number ORDER BY b.Created_Date ASC) Reply_Date
    , FIRST_VALUE(b.Status_ID) OVER (PARTITION BY b.Order_Number ORDER BY b.Created_Date ASC) Reply_Type
    FROM dbo.Order_Status a
    LEFT JOIN dbo.Order_Status b ON a.Order_Number = b.Order_Number AND a.Created_Date < b.Created_Date
    WHERE a.Status_ID = 101
    AND b.Status_ID IN(107,108,109)
    But this is just returning the same values for the reply date on orders with multiple rejections.

    Any help that anyone can give would be greatly appreciated.
    Many thanks
     
    Last edited: 28 Apr 2021
  2. Squallers

    Squallers Meat Puppet

    Joined:
    7 Oct 2008
    Posts:
    280
    Likes Received:
    25
    If I've understood correctly (and there's a good chance that I haven't) your problem may be that unless you've got some data that explicitly links replies to the confirmations then you're reliant on dates so it's only ever going to be a best guess.

    Is the created date field just a date, or is it a datetime?
     
  3. Midlight

    Midlight Minimodder

    Joined:
    6 Jun 2011
    Posts:
    133
    Likes Received:
    74
    You are correct there is no explicit link. The table in question would look some thing like;
    ID | Order_No | Status_ID | Created_Date | Created_By
    7001 | 5002597 | 101 | 2021-04-30 07:21:56.000 | 932
    7004 | 5002597 | 112 | 2021-04-30 07:22:04.000 | 674
    7014 | 5002597 | 108 | 2021-05-01 21:03:15.000 | 674
    7028 | 5002597 | 108 | 2021-05-02 11:17:15.000 | 674

    The ID's are the Primary Key for the table. The created date is a datetime. It is just the first 108 (or 107/109) after the 101 that I am trying to get.
    The code works fine if it returns 107 as no further 101s are sent for that Order_No. Its where there are multiple 101s for an Order_No that I am getting the same 108 being returned for all the 101s on that Order_No.

    I hope this makes sense because I am loosing the plot with it.
    Thank you for looking.
     
  4. Squallers

    Squallers Meat Puppet

    Joined:
    7 Oct 2008
    Posts:
    280
    Likes Received:
    25
    Ok, so provided that another 101 isn't sent out before any replies then we should be ok.

    I don't think i'm good enough to say exactly why your query isn't working but I'd probably use an outer apply to run a specific sub query for each line:

    Code:
    SELECT comp.ID,
    comp.Order_Number,
    comp.Created_Date,
    comp.Created_By,
    rep.Reply_Date,
    rep.Reply_Type
    FROM dbo.Order_Status comp
    OUTER APPLY (SELECT TOP(1)
                a.Created_Date AS Reply_Date,
                a.Status_ID AS Reply_Type
                FROM dbo.Order_Status a
                WHERE (a.Order_Number = comp.Order_Number)
                AND (a.Created_Date > comp.Created_Date)
                AND (a.Status_ID IN (107,108,109))
                ORDER BY a.Created_Date
    ) rep
    WHERE (comp.Status_ID = 101)
    
    I can't speak to the performance but I think that should give you what you need.
     
    Arboreal and Midlight like this.
  5. Midlight

    Midlight Minimodder

    Joined:
    6 Jun 2011
    Posts:
    133
    Likes Received:
    74
    You are an absolute legend. That has worked perfectly and performance is pretty good.

    Thank you so much.
     
    Arboreal likes this.

Share This Page