Determine if column is increasing or decreasing

user_15333266 0
Viewed 677

I have data table that has column ORDER that is supposed to indicate if the values are increasing or decreasing and another column ORDER_BASIS. However, data in ORDER is often incorrect at the first place so I am trying to determine the correct order using ORDER_BASIS.

Here's what the table looks like:

ORDER ORDER_BASIS
INCREASING 8
INCREASING 16
INCREASING 12
INCREASING 5
INCREASING 1
INCREASING 1
INCREASING 10
INCREASING 16
INCREASING 16

I am trying to achieve this:

ORDER ORDER_BASIS CORRECT_ORDER
INCREASING 8 INCREASING
INCREASING 16 INCREASING
INCREASING 12 DECREASING
INCREASING 5 DECREASING
INCREASING 1 DECREASING
INCREASING 1 DECREASING
INCREASING 10 INCREASING
INCREASING 16 INCREASING
INCREASING 16 INCREASING

First column may use ORDER then the following rows should determine if it's increasing or decreasing. If value did not increase or decrease then remain with it's current status until there's a change in value.

My current logic uses LAG and LEAD:

SELECT
    LEAD (ORDER_BASIS, 1, 0) AS NEXT_BASIS,
    LAG (ORDER_BASIS, 1, 0) AS PREV_BASIS
FROM 
    DATA_TABLE

Then created a condition but cannot get it to work correctly

CASE 
    WHEN CAST(PREV_BASIS AS int) = 0  
         OR (CAST(PREV_BASIS AS int) >= CAST(ORDER_BASIS AS int) 
             AND CAST(NEXT_BASIS AS int) <= CAST(ORDER_BASIS AS int))   
        THEN ORDER_BASIS 
        ELSE 'OPPOSITE_DIRECTION' 
END AS CORRECT_ORDER

Using SQL Server 2014

sql-server-2014 tsql sql-server sql
2 Answers

if your query has not any order by statement, the order of rows is totally random anytime,and can be different. so to solve this problem you need some column that you can guarantee the initial order of rows , then we can fix the issue :

select * , 
 case when ORDER_BASIS > LAG(ORDER_BASIS,1,-1) over (order by <the column>) 
    then 'INCREASING'
 case when ORDER_BASIS = LAG(ORDER_BASIS,1,-1) over (order by <the column>) 
   then 'No change'
 else 'DECREASING' end CORRECT_ORDER
from DATA_TABLE
user_13674540

With sequential processing functions, like LAG and LEAD the sequence is the most important factor to maintain and is the one item that was left out of the original post. In SQL Server, window functions will operate on their own partition (grouping) and sort criteria, so when visually correlating the data it is important to use the same criteria in the external query as you do for the window functions.

The following solution can be explored in this fiddle: http://sqlfiddle.com/#!18/5e1ee/31

To validate your input conditions, run the query to output the LAG and LEAD results:

SELECT
  [Id],[Order]
  , LAG (ORDER_BASIS, 1, NULL) OVER (ORDER BY [Id]) AS PREV_BASIS
  , [Order_Basis]
  , LEAD (ORDER_BASIS, 1, NULL) OVER (ORDER BY [Id]) AS NEXT_BASIS
FROM DATA_TABLE;
Id Order PREV_BASIS Order_Basis NEXT_BASIS
1 INCREASING (null) 8 16
2 INCREASING 8 16 12
3 INCREASING 16 12 5
4 INCREASING 12 5 1
5 INCREASING 5 1 1
6 INCREASING 1 1 10
7 INCREASING 1 10 16
8 INCREASING 10 16 16
9 INCREASING 16 16 (null)

The next issue is that your attempted logic is using the LAG AND the LEAD values, which is not invalid, but is usually used to compute a value that either smooths out the curve or is trying to detect spikes or Highs and Lows.

It is not necessary to do this via a CTE however, it simplifies the readability of the syntax for this discussion, within the CTE we can perform the Integer Casting as well, however in a production environment it might be optimal to store the ORDER_BASIS column as an Integer in the first place.

WITH Records as
(
  SELECT
    [Id],[Order]
    , CAST(LAG (ORDER_BASIS, 1, NULL) OVER (ORDER BY [Id]) AS INT) AS PREV_BASIS
    , CAST([Order_Basis] AS INT) AS [Order_Basis]
    , CAST(LEAD (ORDER_BASIS, 1, NULL) OVER (ORDER BY [Id]) AS INT) AS NEXT_BASIS
  FROM DATA_TABLE
)
SELECT
    [Id],[Order],PREV_BASIS,[Order_Basis],NEXT_BASIS
    ,CASE 
        WHEN NEXT_BASIS > ORDER_BASIS AND PREV_BASIS > ORDER_BASIS THEN 'LOW'
        WHEN NEXT_BASIS < ORDER_BASIS AND PREV_BASIS < ORDER_BASIS THEN 'HIGH'
        WHEN ISNULL(PREV_BASIS, ORDER_BASIS) = ORDER_BASIS THEN 'NO CHANGE'
        WHEN ISNULL(PREV_BASIS, ORDER_BASIS) >= ORDER_BASIS 
               AND ISNULL(NEXT_BASIS, ORDER_BASIS) <= ORDER_BASIS
          THEN 'DECREASING' 
        WHEN ISNULL(PREV_BASIS, ORDER_BASIS) <= ORDER_BASIS 
               AND ISNULL(NEXT_BASIS, ORDER_BASIS) >= ORDER_BASIS 
        THEN 'INCREASING' 
        ELSE 'INDETERMINATE'
    END AS CORRECT_ORDER
FROM Records
ORDER BY [Id];
Id Order PREV_BASIS Order_Basis NEXT_BASIS CORRECT_ORDER
1 INCREASING (null) 8 16 NO CHANGE
2 INCREASING 8 16 12 HIGH
3 INCREASING 16 12 5 DECREASING
4 INCREASING 12 5 1 DECREASING
5 INCREASING 5 1 1 DECREASING
6 INCREASING 1 1 10 NO CHANGE
7 INCREASING 1 10 16 INCREASING
8 INCREASING 10 16 16 INCREASING
9 INCREASING 16 16 (null) NO CHANGE

You could extend this by using a LAG comparison again to determine if the NO CHANGE in the middle of the above record set is in fact a low point over a longer period.

If the CORRECT ORDER should only be a function of the previous record, then there is no need to use a LEAD evaluation at all:

WITH Records as
(
  SELECT
    [ID],[ORDER]
    , CAST(LAG (ORDER_BASIS, 1, NULL) OVER (ORDER BY [Id]) AS INT) AS PREV_BASIS
    , CAST([ORDER_BASIS] AS INT) AS [ORDER_BASIS]
  FROM DATA_TABLE
)
SELECT 
  [ID],[ORDER],[PREV_BASIS],[ORDER_BASIS]
  , CASE WHEN ORDER_BASIS < PREV_BASIS
           THEN 'DECREASING'
         WHEN ORDER_BASIS > PREV_BASIS
          THEN 'INCREASING'
         ELSE 'NO CHANGE' 
    END CORRECT_ORDER
FROM Records;
ID ORDER PREV_BASIS ORDER_BASIS CORRECT_ORDER
1 INCREASING (null) 8 NO CHANGE
2 INCREASING 8 16 INCREASING
3 INCREASING 16 12 DECREASING
4 INCREASING 12 5 DECREASING
5 INCREASING 5 1 DECREASING
6 INCREASING 1 1 NO CHANGE
7 INCREASING 1 10 INCREASING
8 INCREASING 10 16 INCREASING
9 INCREASING 16 16 NO CHANGE
user_16902170
Related