July 11, 2011 at 3:33 am
Hello All,
I need help to Solve one query , the code block is as below
Create Table #Test
(
Historyid INT,
ID INT,
LetterNo INT,
letter Varchar(1),
SequenceNumber INT Null
)
DECLARE @Counter INT
INSERT INTO #Test VALUES(1,1,1,'C',null)
INSERT INTO #Test VALUES(2,1,1,'C',null)
INSERT INTO #Test VALUES(3,1,2,'B',null)
INSERT INTO #Test VALUES(4,1,2,'B',null)
INSERT INTO #Test VALUES(5,1,2,'B',null)
INSERT INTO #Test VALUES(6,1,6,'W',null)
INSERT INTO #Test VALUES(7,1,2,'B',null)
INSERT INTO #Test VALUES(8,1,2,'B',null)
INSERT INTO #Test VALUES(9,1,2,'B',null)
INSERT INTO #Test VALUES(10,1,6,'W',null)
INSERT INTO #Test VALUES(11,1,6,'W',null)
SET @Counter = 0
UPDATE OH
SET @Counter = SequenceNumber = (
CASE
WHEN O.LetterNo IS NULLTHEN @Counter
WHEN O.Historyid IS NULLTHEN @Counter
WHEN O.LetterNo = M.LetterNoTHEN @Counter
WHEN O.LetterNo > M.LetterNoTHEN @Counter + 1
ELSE @Counter
END
)
FROM #Test M
JOIN #Test OH ON M.ID = OH.ID
OUTER APPLY
(
SELECT TOP 1 O.*
FROM #Test O
WHERE M.ID = O.ID
AND M.Historyid < O.Historyid
AND O.Historyid = OH.Historyid
ORDER BY O.Historyid ASC
) O
SELECT *
FROM #Test
DROP TABLE #Test
I need to update the SequenceNumber such as below,
Historyid ID LetterNo letter SequenceNumber
----------- ----------- ----------- ------ --------------
1 1 1 C 0
2 1 1 C 0
3 1 2 B 1
4 1 2 B 1
5 1 2 B 1
6 1 6 W 2
7 1 2 B 3
8 1 2 B 3
9 1 2 B 3
10 1 6 W 4
11 1 6 W 4
Can any one please help me to solve this query.
Thank you
Yatish
July 11, 2011 at 3:51 am
Something like this?
;with cte as
(
select *,
historyid - row_number() over(partition by letterNo order by historyid) row
from #Test
),
order_grps as
(
select row, row_number() over(order by min(historyid))-1 as row2 from cte group by row
)
update cte
set SequenceNumber=row2
from cte inner join order_grps on cte.row=order_grps.row
July 11, 2011 at 4:44 am
Hi,
Thank you for your solution, I have tried for below data for which this is not working,
INSERT INTO #Test VALUES(1,1,1,'C',null)
INSERT INTO #Test VALUES(2,1,1,'C',null)
INSERT INTO #Test VALUES(3,1,2,'B',null)
INSERT INTO #Test VALUES(4,1,4,'A+',null)
INSERT INTO #Test VALUES(5,1,2,'B',null)
INSERT INTO #Test VALUES(6,1,1,'C',null)
INSERT INTO #Test VALUES(7,1,6,'W',null)
INSERT INTO #Test VALUES(8,1,1,'C',null)
INSERT INTO #Test VALUES(9,1,2,'B',null)
INSERT INTO #Test VALUES(10,1,5,'NW',null)
INSERT INTO #Test VALUES(11,1,6,'W',null)
For above data the output should be as below
Historyid ID LetterNo letter SequenceNumber
------- ---- ----------- ----------- ------
1 1 1 C 0
2 1 1 C 0
3 1 2 B 1
4 1 4 A+ 2
5 1 2 B 3
6 1 1 C 4
7 1 6 W 5
8 1 1 C 6
9 1 2 B 7
10 1 5 NW 8
11 1 6 W 9
Thank you
Yatish
July 11, 2011 at 6:10 am
You will need to put the groups in order.
Something like the following should work:
WITH Grps
AS
(
SELECT Historyid, SequenceNumber
,HistoryId - ROW_NUMBER() OVER (PARTITION BY Letter ORDER BY historyid) AS grp
FROM #Test
)
, MinHists
AS
(
SELECT Historyid, SequenceNumber
,MIN(Historyid) OVER (PARTITION BY grp) AS MinHist
FROM Grps
)
,SequenceNumbers
AS
(
SELECT Historyid, SequenceNumber
,DENSE_RANK() OVER (ORDER BY MinHist) - 1 AS SeqNo
FROM MinHists
)
UPDATE SequenceNumbers
SET SequenceNumber = SeqNo
July 12, 2011 at 12:03 am
Hello All,
I will explain the output desired for me, if we consider below data
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(1,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(2,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(3,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(4,1,3,'A')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(5,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(6,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(7,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(8,1,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(9,1,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(10,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(11,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(12,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(13,2,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(14,2,6,'W')
Then what I am expecting is that
Historyid ID LetterNo letter SequenceNumber
----------- ----------- ----------- ------ --------------
1 1 1 C 0
2 1 1 C 0
3 1 2 B 1
4 1 3 A 2
5 1 2 B 3
6 1 2 B 3
7 1 1 C 4
8 1 6 W 5
9 1 6 W 5
10 2 2 B 0
11 2 2 B 0
12 2 2 B 0
13 2 6 W 1
14 2 6 W 1
Now you will see the row 1 and 2 having letter 'C' this will be group 0, next the letter is changes so new group will be considered for this, in row 4 again the letter is changed so new group, now again we have letter 'B' so here we will not consider this in the row 3 group but instead a new group will be considered for row 5 and 6..similarly for all other rows.
Also you will find the at row 10 new Id is started...the same logic is need to applied for the new ID as well. while doing so it should again start from 0.
below is the current code block were I am working...I need help from you to solve this query.
Create Table #Test
(
Historyid INT,
ID INT,
LetterNo INT,
letter Varchar(2),
SequenceNumber INT default(0) NOT NULL
)
DECLARE @Counter INT
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(1,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(2,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(3,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(4,1,3,'A')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(5,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(6,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(7,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(8,1,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(9,1,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(10,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(11,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(12,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(13,2,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(14,2,6,'W')
ALTER TABLE #Test ADD HistoryRank INT DeFault(0) NOT NULL
Update T1
SET T1.HistoryRank = T2.NewHistoryRank
FROM #Test T1
JOIN
(
SELECT T2.*,ROw_Number() OVER (Partition BY T2.ID Order BY T2.HistoryId DESC) AS NewHistoryRank
FROM #Test T2
) T2 ON T2.HistoryId = T1.HistoryId
SET @Counter = 0
UPDATE OH
SET @Counter = SequenceNumber = ISNull((
CASE
--WHEN OH.HistoryRank = 2THEN 0
--WHEN M.LetterNo IS NULLTHEN 0
WHEN OH.LetterNo <> M.LetterNoTHEN @Counter + 1
ELSE
@Counter
END
),0)
FROM #Test M
OUTER APPLY
(
SELECT TOP 1 O.*
FROM #Test O
WHERE M.Id = O.Id
AND M.Historyid < O.Historyid
ORDER BY O.Historyid ASC
) O
JOIN #Test OH ON O.Id = OH.Id AND O.Historyid = OH.Historyid
WHERE M.Id = OH.Id
SELECT * FROM #Test ORDER BY Id
DROP TABLE #Test
Please let me know what changes need so that this can worked as required.
Thank you
Yatish
July 12, 2011 at 4:41 am
;WITH Calculator1 AS (
SELECT Historyid, ID, LetterNo, letter,
rn1 = ROW_NUMBER() OVER(ORDER BY Historyid),
rn2 = ROW_NUMBER() OVER(PARTITION BY Letter ORDER BY Historyid)
FROM #Test),
Calculator2 AS (
SELECT Historyid, ID, LetterNo, letter,
NewSet = (rn2-rn1)
FROM Calculator1)
SELECT Historyid, ID, LetterNo, letter,
SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY NewSet desc, LetterNo desc)
FROM Calculator2 ORDER BY Historyid
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2011 at 4:48 am
HI ChrisM@Work,
Thank you for your query Solution, but when I tested the output for the data as below.
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(1,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(32,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(3,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(14,1,3,'A')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(15,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(36,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(457,1,1,'C')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(58,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(7629,1,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(1874,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(3980,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(4094,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(4212,2,2,'B')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(4922,2,6,'W')
INSERT INTO #Test(Historyid,ID,LetterNo,letter) VALUES(2123114,2,6,'W')
Historyid ID LetterNo letter SequenceNumber
----------- ----------- ----------- ------ --------------------
1 1 1 C 1
3 1 2 B 2
14 1 3 A 3
15 1 2 B 4
36 1 2 B 5
58 1 2 B 5
32 1 1 C 6
7629 1 2 B 7
457 1 1 C 8
1874 2 2 B 1
3980 2 2 B 1
4094 2 2 B 1
4212 2 2 B 1
4922 2 6 W 2
2123114 2 6 W 3
But I was looking for out put below
Historyid ID LetterNo letter SequenceNumber
----------- ----------- ----------- ------ --------------------
1 1 1 C 1
3 1 2 B 2
14 1 3 A 3
15 1 2 B 4
36 1 2 B 4
58 1 2 B 4
32 1 1 C 5
7629 1 2 B 6
457 1 1 C 7
1874 2 2 B 1
3980 2 2 B 1
4094 2 2 B 1
4212 2 2 B 1
4922 2 6 W 2
2123114 2 6 W 2
You can see the difference between both. Thank you for your help.
Thank you
Yatish
July 12, 2011 at 4:54 am
yatish.patil (7/12/2011)
HI ChrisM@Work,Thank you for your query Solution, but when I tested the output for the data as below.
...
You can see the difference between both. Thank you for your help.
Thank you
Yatish
Output your results in HistoryID order and see what happens.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2011 at 5:18 am
Hi ChrisM@Work,
Thanks for your reply, I did the change and below is the output for the same
Historyid ID LetterNo letter SequenceNumber
----------- ----------- ----------- ------ --------------------
1 1 1 C 1
3 1 2 B 2
14 1 3 A 3
15 1 2 B 4
32 1 1 C 6
36 1 2 B 5
58 1 2 B 5
457 1 1 C 8
1874 2 2 B 1
3980 2 2 B 1
4094 2 2 B 1
4212 2 2 B 1
4922 2 6 W 2
7629 1 2 B 7
2123114 2 6 W 3
If you look at the Last two rows for ID 2 the letter is 'W' and is not changes still the Sequence number is changed for the Same.
Please let me know if you are getting my point, IF you think I need to explain more Please let me know.
Thank you
Yatish
July 12, 2011 at 5:24 am
yatish.patil (7/12/2011)
Hi ChrisM@Work,Thanks for your reply, I did the change and below is the output for the same
...
If you look at the Last two rows for ID 2 the letter is 'W' and is not changes still the Sequence number is changed for the Same.
Please let me know if you are getting my point, IF you think I need to explain more Please let me know.
Thank you
Yatish
Check your ID column.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2011 at 6:50 am
Hi ChrisM@Work,
Thank you for helping me on this query, I have checked it with different letter's combination and other fields values and it is working. Thank you for Your Help.
Regards
Yatish
July 12, 2011 at 6:58 am
You're welcome. Thank you for the feedback. Always be sure to vigorously check any code you obtain from any forum.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 19, 2011 at 11:33 pm
Hi Ken McKelvey,
I have tried you solution but it is not working for the data below,
Create TABLE #History
(
HistoryID INT,
Id INT,
letterId INT,
Letter VARCHAR(2),
HistoryRank INT
)
INSERT INTO #History VALUES (1101795,265805,5,'NW',11)
INSERT INTO #History VALUES (1119363,265805,5,'NW',10)
INSERT INTO #History VALUES (1182858,265805,4,'A+',9)
INSERT INTO #History VALUES (1237349,265805,3,'A',8)
INSERT INTO #History VALUES (1350251,265805,3,'A',7)
INSERT INTO #History VALUES (1442353,265805,3,'A',6)
INSERT INTO #History VALUES (1610381,265805,3,'A',5)
INSERT INTO #History VALUES (1931359,265805,2,'B',4)
INSERT INTO #History VALUES (2030510,265805,5,'NW',3)
INSERT INTO #History VALUES (2138415,265805,5,'NW',2)
INSERT INTO #History VALUES (2457793,265805,3,'A',1)
ALTER TABLE #History ADD SequenceNumber INT NULL
;WITH Grps
AS
(
SELECT Historyid, SequenceNumber
,HistoryId - ROW_NUMBER() OVER (PARTITION BY Letter ORDER BY historyid) AS grp
FROM #History
)
, MinHists
AS
(
SELECT Historyid, SequenceNumber
,MIN(Historyid) OVER (PARTITION BY grp) AS MinHist
FROM Grps
)
,SequenceNumbers
AS
(
SELECT Historyid, SequenceNumber
,DENSE_RANK() OVER (ORDER BY MinHist) - 1 AS SeqNo
FROM MinHists
)
UPDATE SequenceNumbers
SET SequenceNumber = SeqNo
SELECT *
FROM #History
order by HistoryID
DROP TABLE #History
AS if you run this query you will find that the output is just ranking the records but they are not required as the history id will be unique for these records. but I need to group each same letter records..this means I am looking for below output with above data.
HistoryID Id letterId Letter HistoryRank SequenceNumber
----------- ----------- ----------- ------ ----------- --------------
1101795 265805 5 NW 11 0
1119363 265805 5 NW 10 0
1182858 265805 4 A+ 9 1
1237349 265805 3 A 8 2
1350251 265805 3 A 7 2
1442353 265805 3 A 6 2
1610381 265805 3 A 5 2
1931359 265805 2 B 4 3
2030510 265805 5 NW 3 4
2138415 265805 5 NW 2 4
2457793 265805 3 A 1 5
Please let me know if I need to provide more details. Thank you for your suggestions.
Yatish
July 19, 2011 at 11:37 pm
Hi ChrisM@Work,
I while testing your suggestion I found for some data it is not working. I will explain this,
when I tried you solution for the below data,
Create TABLE #History
(
HistoryID INT,
Id INT,
letterId INT,
Letter VARCHAR(2),
HistoryRank INT
)
INSERT INTO #History VALUES (1101795,265805,5,'NW',11)
INSERT INTO #History VALUES (1119363,265805,5,'NW',10)
INSERT INTO #History VALUES (1182858,265805,4,'A+',9)
INSERT INTO #History VALUES (1237349,265805,3,'A',8)
INSERT INTO #History VALUES (1350251,265805,3,'A',7)
INSERT INTO #History VALUES (1442353,265805,3,'A',6)
INSERT INTO #History VALUES (1610381,265805,3,'A',5)
INSERT INTO #History VALUES (1931359,265805,2,'B',4)
INSERT INTO #History VALUES (2030510,265805,5,'NW',3)
INSERT INTO #History VALUES (2138415,265805,5,'NW',2)
INSERT INTO #History VALUES (2457793,265805,3,'A',1)
ALTER TABLE #History ADD SequenceNumber INT NULL
;WITH Calculator1 AS (
SELECT Historyid, ID, letterId, letter,
rn1 = ROW_NUMBER() OVER(ORDER BY Historyid),
rn2 = ROW_NUMBER() OVER(PARTITION BY Letter ORDER BY Historyid)
FROM #History),
Calculator2 AS (
SELECT Historyid, ID, letterId, letter,
NewSet = (rn2-rn1)
FROM Calculator1)
SELECT Historyid, ID, letterId, letter,
SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY NewSet desc, letterId desc)
FROM Calculator2 ORDER BY Historyid
DROP TABLE #History
This has produced output as below,
Historyid ID letterId letter SequenceNumber
----------- ----------- ----------- ------ --------------------
1101795 265805 5 NW 1
1119363 265805 5 NW 1
1182858 265805 4 A+ 2
1237349 265805 3 A 3
1350251 265805 3 A 3
1442353 265805 3 A 3
1610381 265805 3 A 3
1931359 265805 2 B 6
2030510 265805 5 NW 4
2138415 265805 5 NW 4
2457793 265805 3 A 5
You will find here that the Sequence Number is not correctly for letter 'B' and next records.
the output i required is as below
Historyid ID letterId letter SequenceNumber
----------- ----------- ----------- ------ --------------------
1101795 265805 5 NW 1
1119363 265805 5 NW 1
1182858 265805 4 A+ 2
1237349 265805 3 A 3
1350251 265805 3 A 3
1442353 265805 3 A 3
1610381 265805 3 A 3
1931359 265805 2 B 4
2030510 265805 5 NW 5
2138415 265805 5 NW 5
2457793 265805 3 A 6
July 20, 2011 at 2:30 am
Hello,
I was able to work on the solution but it is not completely what I want, still there are few issues, I thought I should update with my latest changes, as below,
Create TABLE #History
(
HistoryID INT,
Id INT,
letterId INT,
Letter VARCHAR(2),
HistoryRank INT
)
INSERT INTO #History VALUES (956924,265805,5,'NW',5)
INSERT INTO #History VALUES (996005,265805,5,'NW',4)
INSERT INTO #History VALUES (1119363,265805,5,'NW',3)
INSERT INTO #History VALUES (1182858,265805,6,'W',2)
INSERT INTO #History VALUES (1237349,265805,6,'W',1)
INSERT INTO #History VALUES (956923,240651,4,'A+',5)
INSERT INTO #History VALUES (1156588,240651,4,'A+',4)
INSERT INTO #History VALUES (1237361,240651,4,'A+',3)
INSERT INTO #History VALUES (1305863,240651,3,'A',2)
INSERT INTO #History VALUES (1442363,240651,3,'A',1)
ALTER TABLE #History ADD SequenceNumber INT NULL
DECLARE @Counter INT
SET @Counter = 0
;WITH History AS
(
SELECT *
FROM #History
)
UPDATE H
SET @Counter = SequenceNumber = CASE
WHEN O.HistoryRank = 1THEN 0
WHEN H.letterId = O.letterIdTHEN @Counter
WHEN H.letterId <> O.letterIdTHEN @Counter + 1
ELSE @Counter
END
FROM History O
OUTER APPLY
(
SELECT *
FROM History H
WHERE O.ID = H.ID AND
H.HistoryID =
(
SELECT TOP 1 HistoryID
FROM History A
WHERE A.ID = O.ID
AND A.ID = H.ID
AND A.HistoryID > O.HistoryID
)
) H
WHERE O.ID = H.ID
UPDATE #History
SET SequenceNumber = 0
WHERE SequenceNumber IS NULL
SELECT * FROM #History
DROP TABLE #History
BUT this code has a little bit issue, when there are multiple ID values in the temp table it is not working correctly but same data is passed through the Temp table then it works.
Currently above query output is as below
HistoryID Id letterId Letter HistoryRank SequenceNumber
----------- ----------- ----------- ------ ----------- --------------
956924 265805 5 NW 5 0
996005 265805 5 NW 4 0
1119363 265805 5 NW 3 0
1182858 265805 6 W 2 1
1237349 265805 6 W 1 1
956923 240651 4 A+ 5 0
1156588 240651 4 A+ 4 1
1237361 240651 4 A+ 3 1
1305863 240651 3 A 2 2
1442363 240651 3 A 1 2
If you look at HistoryId 1156588 and 1237361 these should have Sequence Number 0
but the Sequence Number field is updated with letter is not changed. but if we insert only 240651 id's records in temp table it works well.
Please suggest me how this issue can be fixed or is there any better way produce the required output.
Thank you
Yatish
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply