Post reply

TSQL Self Join to find differences?

  • September 5, 2024 at 8:04 pm

    #4454442

    All,

    I have a table I need to query and I need to count the instances that the AgentExpDate changes mid transaction.  Below is the data I am looking at:

    AgentTable

    I am trying to come up with a query to detect the change in Agent_Exp_Date at Agent_Dec_Number 11.

    This is a small example of what I am trying to find, ultimately I am trying to come up with a query that will be able to run over the entire table.

    I have tried a self join, which didnt work.  Attempting to find the max date and comparing that to the rest grouping by certain fields is not working out for me either.

    Any and all help here will be greatly appreciated!

    My apologies for what seems like a very sloppy post, I am having a hard time figuring out how  to past this sample table in.

    • This topic was modified 2 months, 1 week ago by  GBeezy.
    • This topic was modified 2 months, 1 week ago by  GBeezy.

  • September 5, 2024 at 10:20 pm

    #4454458

    I'll guess since you didn't post a way for anyone to post tested code.

    Use LEAD to access subsequent data in the current row. Then compare AgentExpDate to the LEAD value of AgentExpDate to identify when changes occurred.

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • September 6, 2024 at 1:48 pm

    #4454650

    Thank you, Chrissy321 for the advice here.  I didn't post any code, because at this point everything I have tried hasn't been close to correct, so I am starting from nothing.  In the future, if I have questions and some half way decent code, I will be sure to post it.

    Thanks again!

  • September 6, 2024 at 3:11 pm

    #4454674

    Chrissy321 wrote:

    I'll guess since you didn't post a way for anyone to post tested code.

    Use LEAD to access subsequent data in the current row. Then compare AgentExpDate to the LEAD value of AgentExpDate to identify when changes occurred.

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

    I think you mean LAG().  This will give you the first record (on or) AFTER the change occurs, rather than the last one BEFORE the change.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 3 (of 3 total)

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