detect if previous record is higher or lower ?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    In it's simplest form if I had a table that stored a persons name, date that the record was entered, and the amount of jobs done.. when I run the query in date order it produces 4 columns with the fourth indicating if the previous amount of jobs is more, less or static. Example below

    NAME     DATE             AMOUNT of JOBS    DIRECTION
    John       12/jan/2017    3                                 Static
    John       13/jan/2017    4                                  Up
    John       14/jan/2017    2                                  Down
    John       15/jan/2017    4                                  Up
    John       16/jan/2017    4                                  Static
    John       17/jan/2017    1                                  Down


    CREATE TABLE test ([Name] VARCHAR(50), [Date] smalldatetime,[AMOUNT of JOBS] Int )
    GO

    INSERT INTO leavecard(wtname,ein,startdate,endate)
    VALUES('John','12/jan/2017','3')
    VALUES('John','13/jan/2017','4')
    VALUES('John','14/jan/2017','2')
    VALUES('John','15/jan/2017','4')
    VALUES('John','16/jan/2017','4')
    VALUES('John','17/jan/2017','1')

  • Jeff Moden

    SSC Guru

    Points: 995468

    mick burden - Tuesday, May 22, 2018 7:51 PM

    In it's simplest form if I had a table that stored a persons name, date that the record was entered, and the amount of jobs done.. when I run the query in date order it produces 4 columns with the fourth indicating if the previous amount of jobs is more, less or static. Example below

    NAME     DATE             AMOUNT of JOBS    DIRECTION
    John       12/jan/2017    3                                 Static
    John       13/jan/2017    4                                  Up
    John       14/jan/2017    2                                  Down
    John       15/jan/2017    4                                  Up
    John       16/jan/2017    4                                  Static
    John       17/jan/2017    1                                  Down


    CREATE TABLE test ([Name] VARCHAR(50), [Date] smalldatetime,[AMOUNT of JOBS] Int )
    GO

    INSERT INTO leavecard(wtname,ein,startdate,endate)
    VALUES('John','12/jan/2017','3')
    VALUES('John','13/jan/2017','4')
    VALUES('John','14/jan/2017','2')
    VALUES('John','15/jan/2017','4')
    VALUES('John','16/jan/2017','4')
    VALUES('John','17/jan/2017','1')

    Are you allowed to write code that uses TempDB directly?  Since you're using 2008, I'm thinking that a "Quirky Update" would be the fastest and lightest weight.

    You might also want to change your readily-consumable data code above... it's just not going to work as written.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Jeff Moden - Tuesday, May 22, 2018 9:34 PM

    mick burden - Tuesday, May 22, 2018 7:51 PM

    In it's simplest form if I had a table that stored a persons name, date that the record was entered, and the amount of jobs done.. when I run the query in date order it produces 4 columns with the fourth indicating if the previous amount of jobs is more, less or static. Example below

    NAME     DATE             AMOUNT of JOBS    DIRECTION
    John       12/jan/2017    3                                 Static
    John       13/jan/2017    4                                  Up
    John       14/jan/2017    2                                  Down
    John       15/jan/2017    4                                  Up
    John       16/jan/2017    4                                  Static
    John       17/jan/2017    1                                  Down


    CREATE TABLE test ([Name] VARCHAR(50), [Date] smalldatetime,[AMOUNT of JOBS] Int )
    GO

    INSERT INTO leavecard(wtname,ein,startdate,endate)
    VALUES('John','12/jan/2017','3')
    VALUES('John','13/jan/2017','4')
    VALUES('John','14/jan/2017','2')
    VALUES('John','15/jan/2017','4')
    VALUES('John','16/jan/2017','4')
    VALUES('John','17/jan/2017','1')

    Are you allowed to write code that uses TempDB directly?  Since you're using 2008, I'm thinking that a "Quirky Update" would be the fastest and lightest weight.

    You might also want to change your readily-consumable data code above... it's just not going to work as written.

    Oops, I don't know what happened there Jeff, thanks for pointing it out. The revised code is below

    CREATE TABLE test ([Name] VARCHAR(50), [Date] smalldatetime,[AMOUNT of JOBS] Int )

    GO

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','12/jan/2017','3')

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','13/jan/2017','4')

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','14/jan/2017','2')

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','15/jan/2017','4')

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','16/jan/2017','4')

    INSERT INTO test(name,[date],[AMOUNT of JOBS])

    VALUES('John','17/jan/2017','1')

  • David Burrows

    SSC Guru

    Points: 64636

    Performance could be an issue here and Jeff's suggestion of Quirky Update would probably the best.
    However, a possibility

    WITH cte ([Name],[Date],[AMOUNT of JOBS],RowNum) AS (
     SELECT [Name],[Date],[AMOUNT of JOBS],
      ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY [Date] ASC)
     FROM test
    )
    SELECT cte.[Name],cte.[Date],cte.[AMOUNT of JOBS],
     CASE
      WHEN prev.[AMOUNT of JOBS] IS NULL THEN 'Static'
      WHEN prev.[AMOUNT of JOBS] = cte.[AMOUNT of JOBS] THEN 'Static'
      WHEN prev.[AMOUNT of JOBS] < cte.[AMOUNT of JOBS] THEN 'Up'
      ELSE 'Down'
      END
    FROM cte
    LEFT JOIN cte prev ON prev.[Name] = cte.[Name] AND prev.RowNum = cte.RowNum-1;

    Far away is close at hand in the images of elsewhere.
    Anon.

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Thanks David, that's a great help. I don't suppose you know what Jeff means about a quirky update ?

  • David Burrows

    SSC Guru

    Points: 64636

    mick burden - Wednesday, May 23, 2018 2:29 AM

    Thanks David, that's a great help. I don't suppose you know what Jeff means about a quirky update ?

    Please see his article http://www.sqlservercentral.com/articles/T-SQL/68467/

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 995468

    mick burden - Wednesday, May 23, 2018 2:29 AM

    Thanks David, that's a great help. I don't suppose you know what Jeff means about a quirky update ?

    David's method is the classic method for handling such things in pre-2012 instances and it works quite well, in most cases.  The thing to be aware of is that when you reference the same CTE in the FROM clause more than once, the CTE will be executed for each occurrence found in the FROM clause just like a VIEW would (rumor has it that 2018 or 2019 might provide the option to "materialize" the results of a CTE so that such duplicate execution would no longer be necessary but we'll see).

    As David suggests, on a huge amount of data, it can become a bit of a performance problem, especially because of the double execution of the CTE.

    As you can tell by the fervor in the discussion associated with the article that David provided a link for (and my apologizes for forgetting to post that link), a lot of people are aghast at the thought of using something that Microsoft won't support (and doesn't know how to use themselves).  But, if you have a lot of data and the classic method that David was kind enough to post becomes a performance problem, then you might want to resort to the "Quirky Update".

    If that's the case, post back and I'll write a demo for the consumable data you posted.  The only reason I didn't do it last night was because I was operating on shoestring of sleep and didn't want to take the time to fix the original data to use it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • David Burrows

    SSC Guru

    Points: 64636

    Jeff Moden - Wednesday, May 23, 2018 6:38 AM

    As David suggests, on a huge amount of data, it can become a bit of a performance problem, especially because of the double execution of the CTE.

    Yes it is very easy to forget this when using cte's.
    To overcome this (and other problems with cte's) I sometimes use an indexed temp table.
    But as you often state 'it depends' 😀

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 995468

    David Burrows - Wednesday, May 23, 2018 7:24 AM

    Jeff Moden - Wednesday, May 23, 2018 6:38 AM

    As David suggests, on a huge amount of data, it can become a bit of a performance problem, especially because of the double execution of the CTE.

    Yes it is very easy to forget this when using cte's.
    To overcome this (and other problems with cte's) I sometimes use an indexed temp table.
    But as you often state 'it depends' 😀

    Totally agreed.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442205

    Jeff Moden - Wednesday, May 23, 2018 6:38 AM

    mick burden - Wednesday, May 23, 2018 2:29 AM

    Thanks David, that's a great help. I don't suppose you know what Jeff means about a quirky update ?

    David's method is the classic method for handling such things in pre-2012 instances and it works quite well, in most cases.  The thing to be aware of is that when you reference the same CTE in the FROM clause more than once, the CTE will be executed for each occurrence found in the FROM clause just like a VIEW would (rumor has it that 2018 or 2019 might provide the option to "materialize" the results of a CTE so that such duplicate execution would no longer be necessary but we'll see).

    As David suggests, on a huge amount of data, it can become a bit of a performance problem, especially because of the double execution of the CTE.

    As you can tell by the fervor in the discussion associated with the article that David provided a link for (and my apologizes for forgetting to post that link), a lot of people are aghast at the thought of using something that Microsoft won't support (and doesn't know how to use themselves).  But, if you have a lot of data and the classic method that David was kind enough to post becomes a performance problem, then you might want to resort to the "Quirky Update".

    If that's the case, post back and I'll write a demo for the consumable data you posted.  The only reason I didn't do it last night was because I was operating on shoestring of sleep and didn't want to take the time to fix the original data to use it.

    If that rumor is true it will make the T-SQL CTE more like the Oracle sub-query refactoring clause.  That would actually be a really nice change.

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Lynn Pettis - Wednesday, May 23, 2018 9:50 AM

    Jeff Moden - Wednesday, May 23, 2018 6:38 AM

    mick burden - Wednesday, May 23, 2018 2:29 AM

    Thanks David, that's a great help. I don't suppose you know what Jeff means about a quirky update ?

    David's method is the classic method for handling such things in pre-2012 instances and it works quite well, in most cases.  The thing to be aware of is that when you reference the same CTE in the FROM clause more than once, the CTE will be executed for each occurrence found in the FROM clause just like a VIEW would (rumor has it that 2018 or 2019 might provide the option to "materialize" the results of a CTE so that such duplicate execution would no longer be necessary but we'll see).

    As David suggests, on a huge amount of data, it can become a bit of a performance problem, especially because of the double execution of the CTE.

    As you can tell by the fervor in the discussion associated with the article that David provided a link for (and my apologizes for forgetting to post that link), a lot of people are aghast at the thought of using something that Microsoft won't support (and doesn't know how to use themselves).  But, if you have a lot of data and the classic method that David was kind enough to post becomes a performance problem, then you might want to resort to the "Quirky Update".

    If that's the case, post back and I'll write a demo for the consumable data you posted.  The only reason I didn't do it last night was because I was operating on shoestring of sleep and didn't want to take the time to fix the original data to use it.

    If that rumor is true it will make the T-SQL CTE more like the Oracle sub-query refactoring clause.  That would actually be a really nice change.

    Many thanks Lynn, I would appreciate a demo if that's ok with you ?

    Kind regards

    Mick

  • sgmunson

    SSC Guru

    Points: 110443

    Another alternative is to get your version of SQL Server up to at least the 2012 version, and then you can use LAG and thus will no longer need the CTE.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • Jeff Moden

    SSC Guru

    Points: 995468

    @Mick,

    Are you all set or do you still have issues with this problem?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    Hi Jeff, no issues as such. It's just nice to find various alternatives to the same solution. I find them a great way of learning. As for Steve's suggestion of upgrading to 2012 or above that's not a possibility do to costs. I love this forum, even if I'm not posting asking questions it's great to trawl through looking at other posts. It's a great place to learn.

  • Jeff Moden

    SSC Guru

    Points: 995468

    mick burden - Thursday, May 31, 2018 12:22 AM

    Hi Jeff, no issues as such. It's just nice to find various alternatives to the same solution. I find them a great way of learning. As for Steve's suggestion of upgrading to 2012 or above that's not a possibility do to costs. I love this forum, even if I'm not posting asking questions it's great to trawl through looking at other posts. It's a great place to learn.

    Cool.  I just wanted to be absolutely sure that you were all set.  And thanks for the feedback.

    If performance does become an issue with the method you're using, post back and I'll show you how to safely use the "Quirky Update" method to blast through a million rows in just a couple of seconds.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 25 total)

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