Styjun

I'm trying to build a query in SQL Server Management Studio using a while loop to obtain the final Result column.

Let say I already have a query (My_query) where I am extracting the following information.

Output sample

My_query is giving me already all the information below without the Result column. My plan was to include a while loop that allows me to identify the correct status for each Period and ID. The logic I was trying to use was the following:

Declare i int = 0
Declare j int = 0
Declare buffer int = 0

With My_query as (My query code is here)

Select *,
 Result = 

 while (i <= 12) --This will be the number of rows coming from My_query.
 Begin
 set buffer = 0 -- Resetting the status variable for the next ID
 while (j <= 6) -- This will be the number of periods for every ID. Max(Block)
 Begin
 If (Block = 1 and Status_History IS NULL) -- Block = 1 always will be the higher period consulted in My_query.
 Set Result = Current_Status
 Set buffer = Result
 If (Block = 1 and Status_History IS NOT NULL)
 Set Result = Status_History
 Set buffer = Result
 If (Block <> 1 and Status_History IS NULL)
 Set Result = buffer
 If (Block <> 1 and Status_History IS NOT NULL)
 Set Result = Status_History
 Set buffer = Result
 Set j = j+1
 END
 Set i = i+1
 END
From My_query

Solution:
The best way I found to solve this problem was creating an index (besides the "Block" one) that allows me to identify specific changes by group. The index assign 0 to NULL values and every time a value different than NULL is found it SUM 1 to the index. When the ID change, the index reset. This solution is way lighter on resources consumption.

Index

SUM(CASE 
WHEN(Status_History) IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY ID ORDER BY Period DESC) AS IND

Solution

WITH DAT AS (My_query)

SELECT *,
CASE WHEN IND = 0 THEN DAT.Current_Status ELSE MAX(DAT.Status_History) OVER ( PARTITION BY DAT.ID, DAT.IND ORDER BY DAT.Period DESC) END AS Result
FROM DAT
ORDER BY ID, Period DESC 

 ID Period Current_Status Status_History IND Block Result
1012 201903 32 NULL 0 1 32
1012 201902 32 NULL 0 2 32
1012 201901 32 16 1 3 16
1012 201812 32 NULL 1 4 16
1012 201811 32 NULL 1 5 16
1012 201810 32 10 2 6 10
1012 201809 32 NULL 2 7 10
5124 201903 25 NULL 0 1 25
5124 201902 25 23 2 2 23
5124 201901 25 29 3 3 29
5124 201812 25 NULL 4 4 29
5124 201811 25 NULL 5 5 29
5124 201810 25 NULL 6 6 29
5124 201809 25 NULL 7 7 29

Thank you all!

You do not actually need a loop to iterate through the result set as functions in the SELECT statement are executed on each result record.

You can use Control Flow Functions as described in the MySQL documentation page to handle the relevant data in your result. There are some examples of this in this stackoverflow question 'IF' in 'SELECT' statement - choose output value based on column values or you can also use a combination of the different Data combination statements in MySQL

I did have success getting the expected output based on the image you provided by using a combination of UNION, JOIN and subqueries.

Code

 SELECT *, mqa.Current_Status as Result 
 FROM MyQuery mqa
 WHERE 
 mqa.Block = 1 
 AND mqa.Status_History IS NULL
 UNION
 SELECT mqb.*, mqb.Status_History as Result 
 FROM MyQuery mqb
 WHERE 
 Status_History IS NOT NULL
 UNION
 SELECT mqc.ID,
 mqc.Period,
 mqc.Current_Status,
 mqc.Status_History,
 mqc.Block,
 (SELECT TOP 1 mqd.Status_History FROM MyQuery mqd
 WHERE mqd.ID = mqc.ID
 AND mqd.Block < mqc.Block
 AND mqd.Status_History IS NOT NULL
 ORDER BY Block DESC)
 FROM MyQuery mqc
 WHERE 
 mqc.Status_History IS NULL
 AND mqc.Block <> 1

Results

| ID | Period | Current_Status | Status_History | Block | Result |
|--------|--------|----------------|----------------|-------|--------|
| 012014 | 201710 | 5 | (null) | 6 | 7 |
| 012014 | 201711 | 5 | 7 | 5 | 7 |
| 012014 | 201712 | 5 | (null) | 4 | 3 |
| 012014 | 201810 | 5 | (null) | 3 | 3 |
| 012014 | 201811 | 5 | 3 | 2 | 3 |
| 012014 | 201812 | 5 | (null) | 1 | 5 |
| 012015 | 201710 | 2 | (null) | 6 | 10 |
| 012015 | 201711 | 2 | (null) | 5 | 10 |
| 012015 | 201712 | 2 | (null) | 4 | 10 |
| 012015 | 201810 | 2 | (null) | 3 | 10 |
| 012015 | 201811 | 2 | (null) | 2 | 10 |
| 012015 | 201812 | 2 | 10 | 1 | 10 |

You can play with the code above in this SQLFiddle

It seems like you are falling back on the first non-null value of the previous record when a condition is not met. In theory this could be a coalesce result of the previous values and the current values looking for the first non-null in the desired order.

With My_query as (My query code is here)

SELECT i,Block,
 Result = 
 CASE WHEN (Block=1) THEN 
 COALESCE(Status_History,Current_History) 
 ELSE 
 COALESCE(Status_History, LAG(Status_History) OVER (PARTITION BY Block ORDER BY i),LAG(Current_Status) OVER (PARTITION BY Block ORDER BY i)
 END
FROM
 My_Query