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
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?
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.
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.
You are an absolute legend. That has worked perfectly and performance is pretty good. Thank you so much.
There is some handy tutorials and things at https://www.w3schools.com/sql/ Its not the most in depth but it is quite good for getting the basics down.