USE [db]
GO
–delcare variables
DECLARE @i int
DECLARE @lastrow int
DECLARE @curPoc int
DECLARE @curDir int
DECLARE @lastDir int
DECLARE @order int
DECLARE @pocs AS TABLE
( rownum int IDENTITY(1, 1) PRIMARY KEY NOT NULL
, pocID int NOT NULL
, fk_directorateID int NOT NULL
)
— this builds a var table that is pre-sorted according to criteria
INSERT INTO @pocs ( pocID, fk_directorateID )
SELECT A.pocId
, A.fk_directorateID
FROM t_poc A (NOLOCK)
WHERE A.isArchived = 0
ORDER BY A.fk_directorateID
, A.orderBy
, A.pocID
SELECT @lastrow = max(A.rownum)
FROM @pocs A
SET @i = 1
SET @lastDir = 0
SET @order = 0
–loop through all POCs in POC table
WHILE @i