Sql-server – Aggregation by positive and negative values

sql serversql-server-2012window functions

I have a table called "PROD_COST" with 5 columns:

+----+----------+------+-----------+-------------+
| ID | Duration | Cost | COST_NEXT | COST_CHANGE |
+----+----------+------+-----------+-------------+
  1. Duration = number of days the price is valid (1 day=1row).
  2. Cost = product price in this day.
  3. -Cost_next = lead(cost,1,0).
  4. Cost_change = Cost_next – Cost.

I need an extra column called "Groups" for aggregation:

+----+----------+------+-----------+-------------+--------+
| ID | Duration | Cost | Next_Cost | Cost_change | Groups |
+----+----------+------+-----------+-------------+--------+
|  1 |        1 | 10   | 8,5       | -1,5        |      1 |
|  2 |        1 | 8,5  | 12,2      | 3,7         |      2 |
|  3 |        1 | 12,2 | 5,3       | -6,9        |      3 |
|  4 |        1 | 5,3  | 4,2       | 1,2         |      4 |
|  5 |        1 | 4,2  | 6,2       | 2           |      4 |
|  6 |        1 | 6,2  | 9,2       | 3           |      4 |
|  7 |        1 | 9,2  | 7,5       | -2,7        |      5 |
|  8 |        1 | 7,5  | 6,2       | -1,3        |      5 |
|  9 |        1 | 6,2  | 6,3       | 0,1         |      6 |
| 10 |        1 | 6,3  | 7,2       | 0,9         |      6 |
| 11 |        1 | 7,2  | 7,5       | 0,3         |      6 |
| 12 |        1 | 7,5  | 0         | 7,5         |      6 |
+----+----------+------+-----------+-------------+--------+

Now I need to group the "Groups" column by Cost_change. It can be positive,negative or 0 values. I was advised to use this code:

select 
  id, COST_CHANGE, sum(GRP) over (order by id asc) +1
from
(
  select
    *,
    case when sign(COST_CHANGE) != sign(isnull(lag(COST_CHANGE) 
      over (order by id asc),COST_CHANGE)) and Cost_change!=0 then 1 else 0 end as GRP
  from
    PROD_COST
) X

But there is a problem: If there are 0 values between two positive or negative values than it groups it separately, for example:

+-------------+--------+
| Cost_change | Groups |
+-------------+--------+
| 9.262       |   5777 |
| -9.262      |   5778 |
| 9.262       |   5779 |
| 0.000       |   5779 |
| 9.608       |   5780 |
| -11.231     |   5781 |
| 10.000      |   5782 |
+-------------+--------+

I need to have:

+-------------+--------+
| Cost_change | Groups |
+-------------+--------+
| 9.262       |   5777 |
| -9.262      |   5778 |
| 9.262       |   5779 |
| 0.000       |   5779 |
| 9.608       |   5779 | -- Here
| -11.231     |   5780 |
| 10.000      |   5781 |
+-------------+--------+

I would be very grateful for any help.

Best Answer

I use your code and added some checks, regarding the COST_CHANGE = 0

For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2)

--my source table
DECLARE @tPROD_COST TABLE
(ID INT /*IDENTITY(1,1)*/,
 Duration INT,
 Cost FLOAT, 
 COST_NEXT FLOAT, 
 COST_CHANGE FLOAT) 

 INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE) 
 VALUES  
(  1 , 1 , 10   , 8.5  , -1.5  ),
(  2 , 1 , 8.5  , 12.2 , 3.7   ),
(  3 , 1 , 12.2 , 5.3  , -6.9  ),
(  4 , 1 , 5.3  , 4.2  , 1.2   ),
(  5 , 1 , 4.2  , 6.2  , 2     ),
(  6 , 1 , 6.2  , 9.2  , 3     ),
(  7 , 1 , 9.2  , 7.5  , -2.7  ),
(  8 , 1 , 7.5  , 6.2  , -1.3  ),
(  9 , 1 , 6.2  , 6.3  , 0.1   ),
(  10 , 1 , 6.3  , 7.2  , 0.9  ),
(  11 , 1 , 7.2  , 7.5  , 0.3  ),
(  12 , 1 , 7.5  , 0    , 7.5  )
,(13,1,10,19.262,9.262)
,(14,1,9.262,9.262,0)
,(15,1,9.262,18.870 , 9.608)
,(16,1,18.870,18.870 , 0)
,(17,1,18.870,18.870 , 0)
,(18,1,18.870, 18.000, -0.870)
,(19,1,18.000, 18.000, 0)
,(20,1,18.000, 17.000, -1.00)

--for SQL 2012

SELECT ID,
      Duration,
      Cost,
      COST_NEXT,
      COST_CHANGE,
      SUM(GRP) OVER (ORDER BY ID ASC) +1 AS Groups
FROM ( 
    SELECT *, 
        CASE            
            WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE)) 
                 AND ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE) <> 0
                 AND Cost_change != 0
            THEN 1 
            WHEN SIGN(COST_CHANGE)  <> 0
                AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE)) 
            THEN 1
            ELSE 0 END AS GRP 
    FROM @tPROD_COST AS PC
    OUTER APPLY  -- get the previous COST CHANGE <> 0
    (
        SELECT TOP(1)
            PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
         FROM 
            @tPROD_COST AS PC3
         WHERE
            PC.ID>PC3.ID
            AND PC3.COST_CHANGE <> 0
        ORDER BY PC3.ID DESC
    )OA2(Prev_Cost_Change_NonZero)
    ) X

--for SQL 2008,2008R2:

;WITH cte
AS( SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          GRP
    FROM
       ( SELECT ID, Duration, Cost, COST_NEXT, COST_CHANGE,
            CASE            
                WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(OA.Prev_Cost_Change,COST_CHANGE)) 
                     AND ISNULL(OA.Prev_Cost_Change,COST_CHANGE) <> 0
                     AND Cost_change != 0
                THEN 1 
                WHEN SIGN(COST_CHANGE)  <> 0
                    AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE)) 
                THEN 1
                ELSE 0 END AS GRP ,
                OA.Prev_Cost_Change,
                OA2.Prev_Cost_Change_NonZero
        FROM 
            @tPROD_COST AS PC
        OUTER APPLY  -- get the previous COST CHANGE
        (
            SELECT TOP(1)
                PC2.COST_CHANGE AS Prev_Cost_Change
             FROM 
                @tPROD_COST AS PC2
             WHERE
                PC.ID>PC2.ID
            ORDER BY PC2.ID DESC
        )OA(Prev_Cost_Change)
        OUTER APPLY  -- get the previous COST CHANGE <> 0
        (
            SELECT TOP(1)
                PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
             FROM 
                @tPROD_COST AS PC3
             WHERE
                PC.ID>PC3.ID
                AND PC3.COST_CHANGE <> 0
            ORDER BY PC3.ID DESC
        )OA2(Prev_Cost_Change_NonZero)
    )A
)

    SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          --GRP,
          ISNULL(OA.Prev_Sum_Grp,0) +1 AS Groups
    FROM
        cte AS C
    OUTER APPLY  -- get the previous SUM
    (
        SELECT 
            SUM(C2.GRP) AS Sum_GRP
         FROM 
            cte AS C2
         WHERE
            C.ID>=C2.ID
    )OA(Prev_Sum_GRP)

The output: 

ID          Duration    Cost                   COST_NEXT              COST_CHANGE            Groups
----------- ----------- ---------------------- ---------------------- ---------------------- -----------
1           1           10                     8.5                    -1.5                   1
2           1           8.5                    12.2                   3.7                    2
3           1           12.2                   5.3                    -6.9                   3
4           1           5.3                    4.2                    1.2                    4
5           1           4.2                    6.2                    2                      4
6           1           6.2                    9.2                    3                      4
7           1           9.2                    7.5                    -2.7                   5
8           1           7.5                    6.2                    -1.3                   5
9           1           6.2                    6.3                    0.1                    6
10          1           6.3                    7.2                    0.9                    6
11          1           7.2                    7.5                    0.3                    6
12          1           7.5                    0                      7.5                    6
13          1           10                     19.262                 9.262                  6
14          1           9.262                  9.262                  0                      6
15          1           9.262                  18.87                  9.608                  6
16          1           18.87                  18.87                  0                      6
17          1           18.87                  18.87                  0                      6
18          1           18.87                  18                     -0.87                  7
19          1           18                     18                     0                      7
20          1           18                     17                     -1                     7
Related Question