Ms sql table adds rows whenever the level changes by more than 1, so each row has a difference of 1 in start_level and end_level

Question

(This is my first question. Therefore, please let me know the suggestions for a better question if you cannot understand.) I have a table of about 500 people (users) who climb the stairs from the floor x (0 = x, max ( y) = 50). A person can go up to zero / one or several levels in one pass, which corresponds to one row of the table along with the time spent on it in seconds. I want to find the average time spent moving from floor a to +1, where a is the number of the number. To do this, I intend to split each row of the specified table into rows that have start_level + 1 = end_level. The duration will be divided evenly, as shown in the EXPECTED OUTPUT TABLE for user b.

GIVEN TABLE INPUT start_level end_level duration user 1 1 10 a 1 2 5 a 2 5 27 b 5 6 3 c EXPECTED OUTPUT start_level end_level duration user 1 1 10 a 1 2 5 a 2 3 27/3 b 3 4 27/3 b 4 5 27/3 b 5 6 3 c 

Note: level transitions are only in integers. After getting the expected result, I can simply create the column amount (duration) / account (individual users) at the start_level level to get the average time spent getting one floor from the top from each floor.

Any help is appreciated.

Answer 1

You can use the Numbers table to “create” incremental steps. Here is my setup:

 CREATE TABLE #floors ( [start_level] INT, [end_level] INT, [duration] DECIMAL(10, 4), [user] VARCHAR(50) ) INSERT INTO #floors ([start_level], [end_level], [duration], [user]) VALUES (1,1,10,'a'), (1,2,5,'a'), (2,5,27,'b'), (5,6,3,'c') 

Then, using the Numbers table and some LEFT JOIN / COALESCE logic:

 -- Create a Numbers table ;WITH Numbers_CTE AS (SELECT TOP 50 [Number] = ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) FROM sys.columns) SELECT [start_level] = COALESCE(n.[Number], f.[start_level]), [end_level] = COALESCE(n.[Number] + 1, f.[end_level]), [duration] = CASE WHEN f.[end_level] = f.[start_level] THEN f.[duration] ELSE f.[duration] / ( f.[end_level] - f.[start_level] ) END, f.[user] FROM #floors f LEFT JOIN Numbers_CTE n ON n.[Number] BETWEEN f.[start_level] AND f.[end_level] AND f.[end_level] - f.[start_level] > 1 

Here are the logical steps:

• LEFT JOIN In the Numbers table for cases where end_level> = start_level + 2 (this gives us several rows - one for each ascending step)
• new start_level = If the LEFT JOIN "completes": take Number from the Numbers table, otherwise: take the original start_level
• new end_level = If LEFT JOIN "completes": take Number + 1, else: take the original end_level
• new duration = If end_level = start_level: take the initial duration (to avoid dividing by 0), otherwise: take the average duration over end_level - start_level