And now for the Window function solution with somewhat increased verbosity in order to explain how it works. It is more efficient than the previous code as it only scans the table once. The down side (if any) is the increased complexity of the code.
Note that this code will only work on SQL Server 2012 and later.
😎
USE tempdb;
GO
SET NOCOUNT ON;
/* To rebuild the table, first execute the line below by highlighting it + F5,
and then the rest of the code.
DROP TABLE dbo.TBL_SOURCE
*/
IF (SELECT OBJECT_ID('dbo.TBL_SOURCE')) IS NULL
BEGIN
/* As there is an implied order in the set, an order directive must be added.
Otherwise there is no way of controling the order of the elements within
the set. To achieve this, an identity column is added which provides a
sequence of natural numbers in the order of the inserts.
*/
CREATE TABLE dbo.TBL_SOURCE
(
ROW_KEY INT IDENTITY(1,1) NOT NULL
,Name NVARCHAR(50) NOT NULL
,increamentalvalue INT NOT NULL
,toy NVARCHAR(10) NOT NULL
);
/* Populate the table with the sample data */
INSERT INTO dbo.TBL_SOURCE
(
Name
,increamentalvalue
,toy
)
/* When using the VALUES construct, there is no need to repeate the
insert statement.
*/
VALUES
('ABC',23,'Bat') ,('ABC',70,'Ball'),('ABC',80,'XXX') ,('ABC',105,'Doll')
,('ABC',111,'Bat'),('ABC',120,'XXX'),('ABC',142,'XXX'),('ABC',145,'Doll')
,('ABC',111,'Bat'),('ABC',120,'XXX'),('ABC',142,'XXX'),('ABC',145,'XXX');
END
/* CTE BASE_SET_CONSTRUCT
Using a running total to mark each group
Output
ROW_KEY Name increamentalvalue toy GR_ID
-------- ----- ----------------- ----- ------
1 ABC 23 Bat 1
2 ABC 70 Ball 1
3 ABC 80 XXX 1
4 ABC 105 Doll 1
5 ABC 111 Bat 2
6 ABC 120 XXX 2
7 ABC 142 XXX 2
8 ABC 145 Doll 2
9 ABC 111 Bat 3
10 ABC 120 XXX 3
11 ABC 142 XXX 3
12 ABC 145 XXX 3
*/
;WITH BASE_SET_CONSTRUCT AS
(
SELECT
SR.ROW_KEY
,SR.Name
,SR.increamentalvalue
,SR.toy
/*
This section provides the group key for each group by a running
total of a iterated base value for each 'Bat' element
*/
,SUM(CASE
WHEN SR.toy = 'Bat' THEN 1 -- Bat is the first element
ELSE 0 -- Ignore the other elements
END) OVER
(
PARTITION BY (SELECT NULL)
ORDER BY SR.ROW_KEY
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS GR_ID
FROM dbo.TBL_SOURCE SR
)
/* CTE GROUPED_SET
Using the LAST_VALUE function to retrieve the last 'Doll' or 'Bat' in the set.
Output
GROUP_RID ROW_KEY Name increamentalvalue toy GR_ID B_ROW_KEY B_Name B_increamentalvalue B_toy
---------- -------- ----- ----------------- ----- ------ ----------- ------- ------------------- ------
3 3 ABC 80 XXX 1 4 ABC 105 Doll
2 2 ABC 70 Ball 1 4 ABC 105 Doll
1 1 ABC 23 Bat 1 4 ABC 105 Doll
4 4 ABC 105 Doll 1 4 ABC 105 Doll
3 7 ABC 142 XXX 2 8 ABC 145 Doll
2 6 ABC 120 XXX 2 8 ABC 145 Doll
1 5 ABC 111 Bat 2 8 ABC 145 Doll
4 8 ABC 145 Doll 2 8 ABC 145 Doll
4 12 ABC 145 XXX 3 9 ABC 111 Bat
3 11 ABC 142 XXX 3 9 ABC 111 Bat
2 10 ABC 120 XXX 3 9 ABC 111 Bat
1 9 ABC 111 Bat 3 9 ABC 111 Bat
*/
,GROUPED_SET AS
(
SELECT
/*
This row_number will mark the first element of each group by 1,
used in the final output as a filter.
*/
ROW_NUMBER() OVER
(
PARTITION BY BSC.GR_ID
ORDER BY BSC.ROW_KEY ASC
) AS GROUP_RID
,BSC.ROW_KEY
,BSC.Name
,BSC.increamentalvalue
,BSC.toy
,BSC.GR_ID
/*
Getting the last value of the group if the entry is either 'Bat' or 'Doll'.
This is achieved by pushing all other entries at the beginning of the sort
order by negating the ROW_KEY value.
*/
,LAST_VALUE(BSC.ROW_KEY) OVER
(
PARTITION BY BSC.GR_ID
ORDER BY CASE
WHEN BSC.toy IN ('Bat','Doll') THEN BSC.ROW_KEY
ELSE -BSC.ROW_KEY
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS B_ROW_KEY
,LAST_VALUE(BSC.Name) OVER
(
PARTITION BY BSC.GR_ID
ORDER BY CASE
WHEN BSC.toy IN ('Bat','Doll') THEN BSC.ROW_KEY
ELSE -BSC.ROW_KEY
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS B_Name
,LAST_VALUE(BSC.increamentalvalue) OVER
(
PARTITION BY BSC.GR_ID
ORDER BY CASE
WHEN BSC.toy IN ('Bat','Doll') THEN BSC.ROW_KEY
ELSE -BSC.ROW_KEY
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS B_increamentalvalue
,LAST_VALUE(BSC.toy) OVER
(
PARTITION BY BSC.GR_ID
ORDER BY CASE
WHEN BSC.toy IN ('Bat','Doll') THEN BSC.ROW_KEY
ELSE -BSC.ROW_KEY
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS B_toy
FROM BASE_SET_CONSTRUCT BSC
)
/* CTE FINAL_SET
Add the toy before the last 'Doll', if there is not 'Doll' in the set
the value is set to NULL.
Output
GROUP_RID Name increamentalvalue toy PREV_NAME PREV_increamentalvalue PREV_toy
---------- ----- ----------------- ----- ---------- ---------------------- ---------
1 ABC 105 Doll ABC 80 XXX
2 ABC 105 Doll ABC 80 XXX
3 ABC 105 Doll ABC 80 XXX
4 ABC 105 Doll NULL NULL NULL
1 ABC 145 Doll ABC 142 XXX
2 ABC 145 Doll ABC 142 XXX
3 ABC 145 Doll ABC 142 XXX
4 ABC 145 Doll NULL NULL NULL
1 ABC 111 Bat NULL NULL NULL
2 ABC 111 Bat NULL NULL NULL
3 ABC 111 Bat NULL NULL NULL
4 ABC 111 Bat NULL NULL NULL
*/
,FINAL_SET AS
(
SELECT
GS.GROUP_RID
,GS.B_Name AS Name
,GS.B_increamentalvalue AS increamentalvalue
,GS.B_toy AS toy
,CASE
WHEN (GS.B_ROW_KEY - GS.ROW_KEY) > 0 THEN LEAD(GS.Name, ABS((GS.B_ROW_KEY - GS.ROW_KEY) - 1)) OVER (ORDER BY GS.ROW_KEY)
ELSE NULL
END AS PREV_NAME
,CASE
WHEN (GS.B_ROW_KEY - GS.ROW_KEY) > 0 THEN LEAD(GS.increamentalvalue, ABS((GS.B_ROW_KEY - GS.ROW_KEY) - 1)) OVER (ORDER BY GS.ROW_KEY)
ELSE NULL
END AS PREV_increamentalvalue
,CASE
WHEN (GS.B_ROW_KEY - GS.ROW_KEY) > 0 THEN LEAD(GS.toy, ABS((GS.B_ROW_KEY - GS.ROW_KEY) - 1)) OVER (ORDER BY GS.ROW_KEY)
ELSE NULL
END AS PREV_toy
FROM GROUPED_SET GS
)
/*
The final result set is then obtained by filtering the FINAL_SET on FS.GROUP_RID = 1
*/
SELECT
FS.Name
,FS.increamentalvalue
,FS.toy
,FS.PREV_NAME
,FS.PREV_increamentalvalue
,FS.PREV_toy
FROM FINAL_SET FS
WHERE FS.GROUP_RID = 1;
Results
Name increamentalvalue toy PREV_NAME PREV_increamentalvalue PREV_toy
----- ----------------- ----- ---------- ---------------------- ---------
ABC 105 Doll ABC 80 XXX
ABC 145 Doll ABC 142 XXX
ABC 111 Bat NULL NULL NULL