How to increment a variable in case function (Query import to BI)

[joemon]

Is it possible to increment an local variable in SQL Server inside a CASE WHEN statement.

I am attempting to identify a Group ID in my table when a error. I am currently using CASE WHEN & Declare as in the following statement.

My code:

Declare @GroupID INT = 1;
SELECT a.CPUT AS Tim
      ,a.USNA AS wayq
      ,a.WE AS Pl
      ,a.MA AS Mau
      ,b.MAK AS Material_Desc
      ,a.BW AS Movement_type
      ,c.BTE AS Movement_Type_Text
      ,lag(a.BW) over (order by a.MB ,a.ZE) as Prev_Movement_type
      ,@GroupID  = @GroupID + CAST(
       CASE
        WHEN (a.BW = '13' AND (lag(a.BW) over (order by a.MB ,a.ZE) = '14'))
        THEN (@GroupID + 1)
        WHEN (a.BW = '13' AND (lag(a.BW) over (order by a.MB ,a.ZE) = '15'))
        THEN (@GroupID + 1)
        WHEN (a.BW = '16' AND (lag(a.BW) over (order by a.MB ,a.ZE) = '14'))
        THEN (@GroupID + 1)
        WHEN (a.BW = '16' AND (lag(a.BW) over (order by a.MB ,a.ZE) = '15'))
        THEN (@GroupID + 1)
        ELSE @GroupID
       END AS BIT) as GroupID

  FROM Database_Table1 a 
  LEFT JOIN Database_Table2 b ON (a.MA = b.MA)
  LEFT JOIN Database_Table3 c ON (a.BW = c.BW)

  WHERE a.BW IN ('13', '14', '15', '16')
  ORDER BY a.MB ,a.ZE

I want the following result:

Best regard.

[rubayatyasmin · Accepted solution]

Hi, @joemon 

Sql is not something that can handle procedural operations. Try using window functions to create a cumulative sum or some other running total. 

For example:

WITH RankedData AS (
    SELECT 
        a.CPUT AS Tim,
        a.USNA AS wayq,
        a.WE AS Pl,
        a.MA AS Mau,
        b.MAK AS Material_Desc,
        a.BW AS Movement_type,
        c.BTE AS Movement_Type_Text,
        lag(a.BW) OVER (ORDER BY a.MB, a.ZE) as Prev_Movement_type,
        ROW_NUMBER() OVER (ORDER BY a.MB, a.ZE) as rn
    FROM 
        Database_Table1 a 
    LEFT JOIN 
        Database_Table2 b ON a.MA = b.MA
    LEFT JOIN 
        Database_Table3 c ON a.BW = c.BW
    WHERE 
        a.BW IN ('13', '14', '15', '16')
),
Groupings AS (
    SELECT 
        *,
        SUM(CASE
            WHEN (Movement_type = '13' AND Prev_Movement_type = '14') THEN 1
            WHEN (Movement_type = '13' AND Prev_Movement_type = '15') THEN 1
            WHEN (Movement_type = '16' AND Prev_Movement_type = '14') THEN 1
            WHEN (Movement_type = '16' AND Prev_Movement_type = '15') THEN 1
            ELSE 0
        END) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as GroupID
    FROM 
        RankedData
)
SELECT 
    Tim,
    wayq,
    Pl,
    Mau,
    Material_Desc,
    Movement_type,
    Movement_Type_Text,
    Prev_Movement_type,
    GroupID
FROM 
    Groupings
ORDER BY 
    rn;

[joemon]

Very thankful 🙂