Increment a field in runtime for Synapse SQL dedicated SQL Pool

Question

I have a task to increment the expected flag column by looking in to existing values. I solved this using while loop and I can get the expected value updated using while loop in Azure synapse-Sql Data warehouse. But I would like to know a direct way to do this using any ranking functions?

My sample Data

CREATE TABLE [dbo].[work_table](
[work_order_key] [int] NULL,
[modification_date] [datetime2](7) NULL,
[from_status] [varchar](1000) NULL,
[to_status] [varchar](100) NULL,
[count] [int] NULL,
[sequence] [int] NULL,
[count_VCR] [int] NULL,
[flag] [int] NULL,
[flag_expected] [int] NULL
  )
INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-14T11:53:05.1630000' AS DateTime2), N'Not Reviewed', N'Planned', 1, 1, 1, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-19T03:49:25.7370000' AS DateTime2), N'Planned', N'In Progress', 1, 2, 1, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-19T04:22:33.0630000' AS DateTime2), N'In Progress', N'Awaiting Parts', 1, 3, 1, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:58:54.7570000' AS DateTime2), N'Awaiting Parts', N'Parts Ordered', 1, 4, 0, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:59:00.8530000' AS DateTime2), N'Parts Ordered', N'Parts Received', 1, 5, 0, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-27T02:59:09.9000000' AS DateTime2), N'Parts Received', N'Planned', 1, 6, 0, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-28T07:59:58.1130000' AS DateTime2), N'Planned', N'In Progress', 1, 7, 1, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-04-29T07:53:30.9030000' AS DateTime2), N'In Progress', N'Work Completed', 1, 8, 1, 1, NULL)
  INSERT [dbo].[work_table] ([work_order_key], [modification_date], [from_status], [to_status], [count], [sequence], [count_VCR], [flag], [flag_expected]) VALUES (1002586, CAST(N'2022-05-05T06:15:34.4300000' AS DateTime2), N'Work Completed', N'Completed', 1, 9, 0, 1, NULL)

My expected result is

Partition column is [work_order_key] and any column from the list as the way it is suitable. While loop can get this answer. I used work_order_key and count_VCR column to get this in a while loop. But I want to check with the group for direct query using the any SQL window or ranking functions.

I cannot add my solution as it is production one. This is sample data set only. Thanks for the support.

score 1 · Accepted Answer · answered Feb 10 '24 at 19:15

This is a classic gaps-and-islands problem. There are many solutions, but a typical one is to use LAG and then a windowed COUNT.

WITH PrevValues AS (
    SELECT *,
      PrevCount_VCR = LAG(wt.count_VCR) OVER (PARTITION BY wt.work_order_key ORDER BY wt.sequence)
    FROM dbo.work_table wt
)
SELECT *,
  New_flag_expected = COUNT(CASE WHEN wt.count_VCR = wt.PrevCount_VCR THEN NULL ELSE 1 END)
    OVER (PARTITION BY wt.work_order_key ORDER BY wt.sequence ROWS UNBOUNDED PRECEDING)
FROM PrevValues wt;

And as an UPDATE statement (note there are no joins needed in SQL Server as you can update the CTE directly).

WITH PrevValues AS (
    SELECT *,
      PrevCount_VCR = LAG(wt.count_VCR) OVER (PARTITION BY wt.work_order_key ORDER BY wt.sequence)
    FROM dbo.work_table wt
),
Counted AS (
    SELECT *,
      New_flag_expected = COUNT(CASE WHEN wt.count_VCR = wt.PrevCount_VCR THEN NULL ELSE 1 END)
        OVER (PARTITION BY wt.work_order_key ORDER BY wt.sequence ROWS UNBOUNDED PRECEDING)
    FROM PrevValues wt
)
UPDATE Counted
SET flag_expected = New_flag_expected;

db<>fiddle

Increment a field in runtime for Synapse SQL dedicated SQL Pool

1 Answers1