Post reply

Help Needed for SQL Query

  • July 20, 2011 at 2:37 am

    #1355421

    Hello Yatish

    This works. The principle is the same as the previous version.

    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)

    INSERT INTO #History VALUES (1101795,265806,5,'NW',11)

    INSERT INTO #History VALUES (1119363,265806,5,'NW',10)

    INSERT INTO #History VALUES (1182858,265806,4,'A+',9)

    INSERT INTO #History VALUES (1237349,265806,3,'A',8)

    INSERT INTO #History VALUES (1350251,265806,3,'A',7)

    INSERT INTO #History VALUES (1442353,265806,3,'A',6)

    INSERT INTO #History VALUES (1610381,265806,3,'A',5)

    INSERT INTO #History VALUES (1931359,265806,2,'B',4)

    INSERT INTO #History VALUES (2030510,265806,5,'NW',3)

    INSERT INTO #History VALUES (2138415,265806,5,'NW',2)

    INSERT INTO #History VALUES (2457793,265806,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 LetterID ORDER BY Historyid)

    FROM #History),

    Calculator2 AS (

    SELECT Historyid, ID, LetterID, letter,

    Newseq = MAX(HistoryID) OVER(PARTITION BY ID, (rn2-rn1), LetterID)

    FROM Calculator1)

    SELECT Historyid, ID, letterId, letter,

    SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY Newseq)

    FROM Calculator2

    ORDER BY ID, Historyid

    DROP TABLE #History

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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 20, 2011 at 4:01 am

    #1355448

    Hi ChrisM@Work,

    I tried the solution you found, but it is not grouping one set of letters History records, as with the below data ,

    CREATE TABLE #History (

    HistoryID INT,

    Id INT,

    letterId INT,

    Letter VARCHAR(2),

    HistoryRank INT )

    INSERT INTO #History VALUES (1101795,265806,5,'NW',11)

    INSERT INTO #History VALUES (1119363,265806,5,'NW',10)

    INSERT INTO #History VALUES (1182858,265806,4,'A+',9)

    INSERT INTO #History VALUES (1237349,265806,3,'A',8)

    INSERT INTO #History VALUES (1350251,265806,3,'A',7)

    INSERT INTO #History VALUES (1442353,265806,3,'A',6)

    INSERT INTO #History VALUES (1610381,265806,3,'A',5)

    INSERT INTO #History VALUES (1931359,265806,2,'B',4)

    INSERT INTO #History VALUES (2030510,265806,5,'NW',3)

    INSERT INTO #History VALUES (2138415,265806,5,'NW',2)

    INSERT INTO #History VALUES (2457793,265806,3,'A',1)

    INSERT INTO #History VALUES (95692,265805,5,'NW',5)

    INSERT INTO #History VALUES (99605,265805,5,'NW',4)

    INSERT INTO #History VALUES (111363,265805,5,'NW',3)

    INSERT INTO #History VALUES (182858,265805,6,'W',2)

    INSERT INTO #History VALUES (2237349,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

    ;WITH Calculator1 AS (

    SELECT Historyid, ID, letterId, letter,

    rn1 = ROW_NUMBER() OVER(ORDER BY Historyid),

    rn2 = ROW_NUMBER() OVER(PARTITION BY LetterID ORDER BY Historyid)

    FROM #History),

    Calculator2 AS (

    SELECT Historyid, ID, LetterID, letter,

    Newseq = MAX(HistoryID) OVER(PARTITION BY ID, (rn2-rn1), LetterID)

    FROM Calculator1)

    SELECT Historyid, ID, letterId, letter,

    SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY Newseq)

    FROM Calculator2

    ORDER BY ID, Historyid

    DROP TABLE #History

    The Output is

    Historyid ID letterId letter SequenceNumber

    ----------- ----------- ----------- ------ --------------------

    956923 240651 4 A+ 1

    1156588 240651 4 A+ 2

    1237361 240651 4 A+ 3

    1305863 240651 3 A 4

    1442363 240651 3 A 4

    95692 265805 5 NW 1

    99605 265805 5 NW 1

    111363 265805 5 NW 1

    182858 265805 6 W 2

    2237349 265805 6 W 3

    1101795 265806 5 NW 1

    1119363 265806 5 NW 1

    1182858 265806 4 A+ 2

    1237349 265806 3 A 3

    1350251 265806 3 A 4

    1442353 265806 3 A 4

    1610381 265806 3 A 4

    1931359 265806 2 B 5

    2030510 265806 5 NW 6

    2138415 265806 5 NW 6

    2457793 265806 3 A 7

    But I am looking for output as below,

    Historyid ID letterId letter SequenceNumber

    ----------- ----------- ----------- ------ --------------------

    956923 240651 4 A+ 1

    1156588 240651 4 A+ 1

    1237361 240651 4 A+ 1

    1305863 240651 3 A 2

    1442363 240651 3 A 2

    95692 265805 5 NW 1

    99605 265805 5 NW 1

    111363 265805 5 NW 1

    182858 265805 6 W 2

    2237349 265805 6 W 2

    1101795 265806 5 NW 1

    1119363 265806 5 NW 1

    1182858 265806 4 A+ 2

    1237349 265806 3 A 3

    1350251 265806 3 A 3

    1442353 265806 3 A 3

    1610381 265806 3 A 3

    1931359 265806 2 B 4

    2030510 265806 5 NW 5

    2138415 265806 5 NW 5

    2457793 265806 3 A 6

    if look at what is the O/p of the result and required O/P, you will see the difference it is Producing with different O/P. in this case history ID column is unique but it is not necessary that it will be sequential for all ID, it may mix like

    HistoryID 1 for ID 1,

    History ID 2 for ID 1,

    HistoryID 3 for ID 2,

    History ID 4 for ID 3

    like this. in this data ID and History Rank combination will also be unique as for every History records of ID the Rank will be find and the order for rank will always latest History record with Rank 1 and the oldest History Rank will be max History Rank.

    I am trying to Group all History records in such way that if same letter then this will be group one then if letter is changed group will get incremented by one even if the same letter is used before for Old History Records but since in between them there is one other letter the new group will start for these

    e.g. IF History of letters is like A,A,A,NW,A then O/P will be A,NW,A. first A will be group 1 NW will be group 2 and next A will group 3.

    Please let me know if I need to explain more.

    Thank you

    Yatish

  • July 20, 2011 at 4:25 am

    #1355455

    Hi Yatish

    Thanks for providing more sample data. Here's a modified version of the same query which deals with it:

    ;WITH Calculator1 AS (

    SELECT Historyid, ID, letterId, letter,

    rn1 = ROW_NUMBER() OVER(ORDER BY ID, Historyid),

    rn2 = ROW_NUMBER() OVER(PARTITION BY ID, letter ORDER BY ID, Historyid)

    FROM #History),

    Calculator2 AS (

    SELECT Historyid, ID, LetterID, letter,

    rn1, rn2, X = (rn1-rn2), -- workings

    Newseq = MAX(HistoryID) OVER(PARTITION BY ID, LetterID, (rn1-rn2))

    FROM Calculator1)

    SELECT Historyid, ID, letterId, letter,

    rn1, rn2, X, Newseq, -- workings

    SequenceNumber = DENSE_RANK() OVER(PARTITION BY ID ORDER BY Newseq)

    FROM Calculator2

    ORDER BY ID, Historyid

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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 20, 2011 at 5:24 am

    #1355468

    Hi ChrisM@Work,

    Thank you Very much , your solution is working perfectly as it is required for me. Thank you for your help.

    Regards,

    Yatish

  • July 22, 2011 at 3:43 am

    #1356899

    yatish.patil (7/20/2011)

    Hi ChrisM@Work,

    Thank you Very much , your solution is working perfectly as it is required for me. Thank you for your help.

    Regards,

    Yatish

    The question now is, if presented with a similar problem, have you learned enough to solve it on your own?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply