Daily Value continuous incline and decline

Wesley Butler 26 Reputation points
2021-08-10T07:24:44.917+00:00

I have a data set which has a single value for each day. The dates are consecutive too.

How to I script the SQL such that it identifies an inclining trend (am going to need to do the same for decline but I'll simply use the reverse of what's used for inclining). There also need to be at least 7 values in the upwards trend for it to need to be labelled, and therefore 7 or more consecutive daily values that continue to incline is ultimately the requirement.

Any suggestions would be a huge help - I just ended up confusing the heck out of myself trying to do this with ifs and subqueries.

For example:

DATE VALUE TREND STATUS TREND LABEL

2021-01-01 199 1st value in an upwards trend part of upwards trend

2021-01-02 252 2 value in an upwards trend part of upwards trend

2021-01-03 303 3 value in an upwards trend part of upwards trend

2021-01-04 377 4 value in an upwards trend part of upwards trend

2021-01-05 480 5 value in an upwards trend part of upwards trend

2021-01-06 512 6 value in an upwards trend part of upwards trend

2021-01-07 522 7 value in an upwards trend upward trend ends here

2021-01-08 449

2021-01-09 329

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
{count} votes

3 answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-08-10T08:26:45.267+00:00

    Hi @Wesley Butler ,

    Sorry, your description is not clear enough for me. What is your expected output? Here is what I am currently trying:

    CREATE TABLE #test(DATEVALUE DATE,TRENDSTATUS INT)  
    INSERT INTO #test VALUES('2021-01-01',199)  
    ,('2021-01-02',252)  
    ,('2021-01-03',303)  
    ,('2021-01-04',377)  
    ,('2021-01-05',480)  
    ,('2021-01-06',512)  
    ,('2021-01-07',522)  
    ,('2021-01-08',449)  
    ,('2021-01-09',329)  
      
      
    ;WITH cte  
    as(SELECT *,DENSE_RANK() OVER(ORDER BY TRENDSTATUS) rr  
       FROM  #test)  
      
    SELECT *  
    FROM #test t  
    JOIN cte c   
    ON t.DATEVALUE=c.DATEVALUE  
    ORDER BY t.DATEVALUE  
    

    Output:
    121884-image.png

    rr is the ranking of the daily value.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

  2. Wesley Butler 26 Reputation points
    2021-08-10T15:38:26.787+00:00

    Every day there is a value, I need to identify instances where the daily amount increased each and every consecutive day for 7 or more days in a row. So ideally in the label column I'd like to count the days where the value is increasing. So lets say days 1,2, ..., 7 have a value that increases such that value on each day creates an effect like = 1<2<3<4<5<6<7 so day 1 in label column would be 1st value where following days value is greater, then day 2 has a following day that is greater so its 2nd day in an inclining trend and so on.

    0 comments No comments

  3. Viorel 112.9K Reputation points
    2021-08-10T17:47:18.963+00:00

    Check the next query, which calculates the trend labels (as numbers):

    ;
    with Q1 as
    (
        select *,
            iif([TREND STATUS] > isnull(lag([TREND STATUS]) over (order by [DATE VALUE]), -1), 1, 0) f
        from MyTable
    ),
    Q2 as
    (
        select *,
            row_number() over (order by [DATE VALUE]) - dense_rank() over (partition by f order by [DATE VALUE]) g
        from Q1
    )
    select 
        [DATE VALUE], 
        [TREND STATUS],
        iif(f = 0, 0, row_number() over (partition by g order by [DATE VALUE])) as [TREND LABEL]
    from Q2
    order by [DATE VALUE]
    

    You can convert it to INSERT. Give more details if you want to put a specific text to [TREND LABEL] instead of numbers.

    0 comments No comments