August 29, 2014 at 9:19 am
Hi All Experts,
Please consider this code block and inline comments to understand what i am trying to achieve.
--Do not consider this table please start reading from #Source.
CREATE TABLE #DoNotConsider(Name nvarchar(50),value int,increamentalvalue int)
INSERT INTO #DoNotConsider VALUES('ABC',10,80)
INSERT INTO #DoNotConsider VALUES('ABC',20,142)
/*
Consider the below table #Source.Which has column increamentalvalue, This column contains value in increamental format,
with no pattern in between them.
The records are in a cycle from Bat to Doll, Again Bat to Doll.....
*/
CREATE TABLE #SOURCE(Name nvarchar(50),increamentalvalue int,toy nvarchar(10))
INSERT INTO #SOURCE VALUES('ABC',23,'Bat')
INSERT INTO #SOURCE VALUES('ABC',70,'Ball')
INSERT INTO #SOURCE VALUES('ABC',80,'XXX')
INSERT INTO #SOURCE VALUES('ABC',105,'Doll') -- Set starts from Bat and end with Doll
INSERT INTO #SOURCE VALUES('ABC',111,'Bat')
INSERT INTO #SOURCE VALUES('ABC',120,'XXX')
INSERT INTO #SOURCE VALUES('ABC',142,'XXX')
INSERT INTO #SOURCE VALUES('ABC',145,'Doll')
INSERT INTO #SOURCE VALUES('ABC',111,'Bat')
INSERT INTO #SOURCE VALUES('ABC',120,'XXX')
INSERT INTO #SOURCE VALUES('ABC',142,'XXX')
INSERT INTO #SOURCE VALUES('ABC',145,'XXX')
/*Values get inserted from #Source to #Temp as one value for each cycle i.e. Bat to Doll. If No Doll record present still one record is
inserted because the cycle has started.
In #Temp table we have a column as increamentalvalue which contains increamentalvalue value of Doll toy.
If no Doll toy present then it takes the value of Bat.
*/
CREATE TABLE #TEMP (Name nvarchar(50),Value int,increamentalvalue int)
INSERT INTO #TEMP VALUES ('ABC',null,105) --105 because end of Doll
INSERT INTO #TEMP VALUES ('ABC',null,145) --145 because end of doll
INSERT INTO #TEMP VALUES ('ABC',null,111) --The value is 111 because no Doll is present , so the value of Bat
UPDATE #TEMP
SET Value = (--Block of code which return me increamentalvalue of that TOY BEFORE DOLL
--for simplification i have created a table known as #DoNotConsider which contains the result
--return by the code in this block.
SELECT Value,increamentalvalue,Name
FROM #DoNotConsider
)tmp
WHERE #TEMP.Name=tmp.Name
And
/*
I want to write the code for this condition.
In #Temp i have increamentalvalue of Doll toy and in tmp block of code i have increamentalvalue of toy BEDORE DOLL.
So how would i traverse Forward only for that cycle.
*/
August 29, 2014 at 1:00 pm
This question is more of like traversing to the next record from a cycle.
August 30, 2014 at 1:29 am
Here is a slightly verbose and hopefully self explanatory set based approach.
😎
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
/* Seed for an inline Tally table to do "set" based loops
*/
;WITH T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),(NULL),(NULL)
,(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
/* Sequentially marking the beginning and the end of each Group
*/
,BASE_SET_CONSTRUCT AS
(
SELECT
SR.ROW_KEY
,SR.Name
,SR.increamentalvalue
,SR.toy
,ROW_NUMBER() OVER
(
PARTITION BY SR.toy
ORDER BY SR.ROW_KEY
) AS GROUP_KEY
FROM dbo.TBL_SOURCE SR
)
/* Count the number of groups in the BASE_SET_CONSTRUCT set
*/
,NUMBER_OF_GROUPS AS
(
SELECT
MAX(BSC.GROUP_KEY) AS NUM_GROUPS
FROM BASE_SET_CONSTRUCT BSC
WHERE BSC.toy = 'Bat'
)
/* Generate a number sequence with equal number of elements as the number
of groups in the BASE_SET_CONSTRUCT set
*/
,NUMS(N) AS (SELECT TOP(SELECT NUM_GROUPS FROM NUMBER_OF_GROUPS) ROW_NUMBER()
OVER (ORDER BY (SELECT NULL)) AS N
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7
)
/* Using the NUMS sequence to retrieve the start and end value of
each group in the BASE_SET_CONSTRUCT set. The ROW_NUMBER function
assigns the value of 1 to the last entry in each group, exactly
what is asked for.
*/
,LAST_DOLL_OR_BAT AS
(
SELECT
NM.N
,ROW_NUMBER() OVER
(
PARTITION BY BSC.GROUP_KEY
ORDER BY BSC.ROW_KEY DESC
) AS BSC_RID
,BSC.ROW_KEY
,BSC.Name
,BSC.increamentalvalue
,BSC.toy
FROM NUMS NM
OUTER APPLY BASE_SET_CONSTRUCT BSC
WHERE BSC.GROUP_KEY = NM.N
AND BSC.toy IN ('Bat','Doll')
)
/* The next CTE will return the following set, what
remains is to get the desired increamentalvalue
of the previous toy.
N BSC_RID ROW_KEY Name increamentalvalue toy
-- -------- -------- ----- ----------------- -----
1 1 4 ABC 105 Doll
2 1 8 ABC 145 Doll
3 1 9 ABC 111 Bat
*/
,KEY_GROUP_MEMBERS AS
(
SELECT
LDOB.N
,LDOB.BSC_RID
,LDOB.ROW_KEY
,LDOB.Name
,LDOB.increamentalvalue
,LDOB.toy
FROM LAST_DOLL_OR_BAT LDOB
WHERE LDOB.BSC_RID = 1
)
/* Pulling everything together in a single query
*/
SELECT
KGM.Name
,KGM.increamentalvalue
,KGM.toy
,CASE
WHEN KGM.toy = 'Doll' THEN TS.Name
ELSE NULL
END AS PREV_NAME
,CASE
WHEN KGM.toy = 'Doll' THEN TS.increamentalvalue
ELSE NULL
END AS PREV_increamentalvalue
,CASE
WHEN KGM.toy = 'Doll' THEN TS.toy
ELSE NULL
END AS PREV_toy
FROM KEY_GROUP_MEMBERS KGM
INNER JOIN dbo.TBL_SOURCE TS
ON KGM.ROW_KEY = (TS.ROW_KEY + 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
Edit: Typo
August 30, 2014 at 6:24 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply