Get multiple records between particular start and end point in the table-CodePudding

I have table data like below

In the given table, some records are outdated data that is generated by a bug in a system.

I want to remove all the outdated records

Correct data condition: if itemStatus rows start and end from status 4 and 6 without 13 or 14 status then it is correct. I do not need to delete that row. Example: Id 18 to 20 that are correct data. Note: Id 21 and 22 is correct because status 13,14 start after status 6 (id 20)

Incorrect data conditions: If itemStatus row starts and ends from status 4 and 6, with 13 and 14 status. Then it is incorrect data and I have to delete that from a DB.

Example: 24 and 25 then 29 and 30

My query

IF OBJECT_ID('tempdb..#TempResult') IS NOT NULL DROP TABLE #TempResult

CREATE TABLE #TempResult (
    Id int 
)

select ItemId
into #TempItemGroup
from Item
group by itemid

declare @SelectedItemId int

while exists (select ItemId from #TempItemGroup)
begin
    select @SelectedItemId = (select top 1  ItemId
                        from #TempItemGroup
                        order by ItemId asc
                        )
    IF OBJECT_ID('tempdb..#TempSingleItemGroup') IS NOT NULL DROP TABLE #TempSingleItemGroup

    SELECT i1.*
        into #TempSingleItemGroup
        FROM Item i1
        WHERE i1.[Id] >= ( SELECT TOP 1 [Id] FROM Item
                             WHERE [ItemStatus] = 4 and ItemId = @SelectedItemId )
         AND i1.[Id] <= ( SELECT TOP 1 [Id] FROM Item
                             WHERE [ItemStatus] = 6 and ItemId = @SelectedItemId  )

        INSERT INTO #TempResult (Id) (SELECT Id FROM #TempSingleItemGroup where ItemStatus = 13 or ItemStatus = 14)

    IF OBJECT_ID('tempdb..#TempSingleItemGroup') IS NOT NULL DROP TABLE #TempSingleItemGroup

    delete #TempItemGroup
    where ItemId = @SelectedItemId
end

-- Delete or do other operation if required
IF(EXISTS(SELECT count(Id) FROM #TempResult))
BEGIN
    -- write a query to delete the data
      select * from #TempResult
END


IF OBJECT_ID('tempdb..#TempResult') IS NOT NULL DROP TABLE #TempResult
IF OBJECT_ID('tempdb..#TempItemGroup') IS NOT NULL DROP TABLE #TempItemGroup

Expected result There are some differences between screenshot and actual table data that you can find at the end of this question

Screenshot: 24,25,29,30 Table data: 13,14,15 ,26,27, 31,32

I have two queries

My above query is only getting first status which have 4 and 6 status, example Id 24 and 25, and I am unable to get next wrong data for 29 and 30
I am using while loop that may not a good practices. Please suggest me a better way to write a query.

Table schema and data

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Item](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemId] [int] NOT NULL,
    [ItemStatus] [int] NOT NULL,
 CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Item] ON 
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (1, 23, 2)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (2, 23, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (3, 23, 7)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (4, 23, 6)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (5, 23, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (6, 23, 14)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (7, 23, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (8, 23, 6)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (9, 23, 3)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (10, 45, 2)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (11, 45, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (12, 45, 7)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (13, 45, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (14, 45, 14)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (15, 45, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (16, 45, 6)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (17, 45, 3)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (18, 25, 2)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (19, 25, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (20, 25, 7)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (21, 25, 6)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (23, 25, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (24, 25, 14)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (25, 25, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (26, 25, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (27, 25, 14)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (28, 25, 6)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (29, 25, 3)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (30, 25, 4)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (31, 25, 13)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (32, 25, 14)
GO
INSERT [dbo].[Item] ([Id], [ItemId], [ItemStatus]) VALUES (33, 24, 6)
GO
SET IDENTITY_INSERT [dbo].[Item] OFF
GO

CodePudding user response：

My reading of your requirement is this:

For each itemId, find rows with itemStatus in (13, 14) that lie between a row with the same itemId and itemStatus 4, and a row with the same itemid and itemStatus 6, in order of id ascending.

Your current requirements do not state whether we should return rows with item status in (13, 14) where there is a prior 4 row, but no subsequent 6 row (the "end" of the itemid group is reached), or what to do if there is a subsequent 6 row, but no prior 4 row (the "start" of the itemid group is reached).

I make the following interpretation: itemStatus 4 opens a "status block". itemStatus 6 closes a "status block". There should be no rows with status 13 or 14 in an open block.

If that is a correct interpretation, all we really need to do is find rows with item status 13 or 14, and then find the nearest prior row in order of id descending with a status of either 4 or 6. If the status of that prior row is 4 then the rows are in an open block. If the status of that prior row is 6, or no prior row can be found, then the rows are not in an open block.

~~Note: In the comments you mentioned expected results including id 31 and 32. But your sample data does not include any row with id 31 or 32.~~ You have now provided these rows and I have added them to my data.


create table #items
(
   Id         int primary key,
   ItemId     int not null,
   ItemStatus int not null,
);

insert #items values
(2, 23, 4),
(3, 23, 7),
(4, 23, 6),
(5, 23, 13),
(6, 23, 14),
(7, 23, 4),
(8, 23, 6),
(9, 23, 3),
(10, 45, 2),
(11, 45, 4),
(12, 45, 7),
(13, 45, 13),
(14, 45, 14),
(15, 45, 13),
(16, 45, 6),
(17, 45, 3),
(18, 25, 2),
(19, 25, 4),
(20, 25, 7),
(21, 25, 6),
(23, 25, 13),
(24, 25, 14),
(25, 25, 4),
(26, 25, 13),
(27, 25, 14),
(28, 25, 6),
(29, 25, 3),
(30, 25, 4),
(31, 25, 13),
(32, 25, 14),
(33, 24, 6);

select      item13or14.*
from        #items item13or14
cross apply (
               select   top 1 itemStatus
               from     #items
               where    itemId = item13or14.itemId
                        and id < item13or14.id
                        and itemStatus in (4, 6)
               order by id desc
            ) prior4or6
where       item13or14.itemStatus in (13, 14)
            and prior4or6.itemStatus = 4;

/* produces:

Id    ItemId   ItemStatus
13    45       13
14    45       14
15    45       13
26    25       13
27    25       14
31    25       13
32    25       14

*/