Determine if column is increasing or decreasing

Question

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

Answer 1

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

Answer 2

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