Post reply

sort by serialcode

  • January 5, 2014 at 8:46 am

    #1677591

    sayedkhalid99 (1/4/2014)

    thanks for your feed back jeff, since i already implemented the first solution provided when i received the code in my project just wanted to change that rather then new code due to shortage of time and deadlines, later on i decided to use your solution and worked perfectly.

    i will take your suggestion on

    Make your code bullet proof and scalable and just assume that it will happen when you can least afford it to.

    And I'd stop recaclulating things that won't change over and over again.

    thanks for your continuous support to community members.

    Thanks for the feedback. Heh... yeah. I do understand deadlines and I believe you've done it the right way. You "got out of the woods" by doing something that would meet the deadline. Most people make the mistake of stopping there. You didn't. You're taking it to the next step and made it so it wouldn't become an emergency deadline in the future. Well done.

    Just as a follow up, I have to ask... will there ever be the possibility that the "serial" will ever contain letters? If so, post back and we'll take it to the next level.

    --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)
    Intro to Tally Tables and Functions

  • January 6, 2014 at 2:21 am

    #1677668

    Jeff Moden (1/4/2014)

    sayedkhalid99 (1/3/2014)

    what change shall i bring in here so that if the serial don't have any decimal point say , it gives error in left function.

    DECLARE @Result TABLE (Serial nvarchar(10),Title varchar(20))

    INSERT INTO @Result

    SELECT '1.1','a' UNION ALL

    SELECT '1.2.1','b' UNION ALL

    SELECT '1.2.2','C' UNION ALL

    SELECT '1.2.3.4','G' UNION ALL

    SELECT '1.11','B' UNION ALL

    SELECT '1.2.33.4.5','Extra row' UNION ALL

    SELECT '2.3','B' UNION ALL

    SELECT '2.11','B' UNION ALL

    SELECT '2.2','C' UNION ALL

    SELECT '1.5','E' UNION ALL

    SELECT '1','E'

    With absolutely no disrespect intended or implied towards anyones fine work on this thread, my recommendation would be to not do it in any way that has such a small limit on the number of levels because there will be a drop-everything panic if a 6th or 7th level ever shows up. Don't say it won't happen. Make your code bullet proof and scalable and just assume that it will happen when you can least afford it to.

    As a bit of a sidebar, if it were me, I'd stop recaclulating things that won't change over and over again. I'd turn my code into a function and add a persisted computed column that contains the function to the table so that I don't ever have to worry about calculating a sort order on something that's almost perfectly static.

    As a hidden benefit of using the code I posted, you'll also have the DelimitedSplit8K function, which you find dozens of other uses for.

    There are compelling reasons for using the DelimitedSplit8K function in this case and I have to agree with Jeff that it's the best tool for the job - but I have a question: what is the source of this data? Is it persisted over time as a permanent table, or is it the output of another query, as the older post from 2011 might suggest?

    “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

Viewing 2 posts - 16 through 16 (of 16 total)

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