1

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 enter image description here

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.

1
  • 1
    "Partition column is [work_order_key]" - Cool, and the ORDER BY field(s) for the window function are? "and any column from the list as the way it is suitable." - Huh?
    – J.D.
    Feb 9 at 13:48

1 Answer 1

1

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

2
  • RE: "you can update the CTE directly" - Are you sure in "Azure Synapse Analytics"? The "Syntax for Azure Synapse Analytics and Microsoft Fabric" doesn't indicate this learn.microsoft.com/en-us/sql/t-sql/queries/…. This isn't a product that I use directly but from what I have seen in SO questions the supported TSQL surface area is fairly different from SQL Server. Feb 11 at 12:03
  • Maybe not, I wasn't concentraing on that. So then it would need a self-join by primary key. Feb 11 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.