Daily Value continuous incline and decline

Question

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

Answer 1

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:

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.

Answer 2

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.

Answer 3

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.