Search code examples
sql-serverspotfire

SQL Query to segregate the data

My data set is as follows: which Indicates the movement of transport where T Indicates it is moving, F Indicates it is Stationary

I want the data to classify as follows for further analysis. Need to derive the Class based on the column Moving and should be counted from 0 and when the moving value changes from T to F then again recounted from F from 0. Next column ID is to group the Moving Set values from 0 to Increment level.

Output:

Img

Solution

  • You can use Common Table Expression to achieve your goal:https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql

    First, you have to order your data by the movement date, and you can use ROW_NUMBER: https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql

    Here a sample request:

    ;WITH orderedMovements AS
(
    SELECT [Date], [Moving], ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Date] ASC) AS [RowNum]
    FROM [dbo].[movements]
), completedMovements AS
(
    SELECT omo.[Date], omo.[Moving], 0 AS [Class], 0 AS [Id], omo.[RowNum] AS [RowNumBis]
    FROM orderedMovements omo
    WHERE [RowNum] = 1
    UNION ALL
    SELECT omo.[Date], omo.[Moving], IIF(cmo.[Moving] = omo.[Moving], cmo.[Class] + 1, 0) AS [Class], IIF(cmo.[Moving] = omo.[Moving], cmo.[Id], cmo.[Id] + 1) AS [Id], omo.[RowNum]
    FROM orderedMovements omo
            JOIN completedMovements cmo ON omo.[RowNum] = cmo.[RowNumBis] + 1
)
SELECT [Date], [Moving], [Class], [Id]
FROM completedMovements
OPTION (MAXRECURSION 100);