max not giving the latest one

  • Here's my contribution.

    DECLARE @test-2 TABLE --CREATE TABLE #test

    (

    createdon datetime,

    kms_quoteorder varchar(15)

    )

    INSERT INTO @test-2 VALUES ('2015-06-10 14:03:04.000',NULL)

    INSERT INTO @test-2 VALUES ('2015-07-22 15:13:39.000','15-37666')

    INSERT INTO @test-2 VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')

    INSERT INTO @test-2 VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')

    INSERT INTO @test-2 VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')

    INSERT INTO @test-2 VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')

    -- build the string with the two parts created in ca2.

    SELECT QuoteOrder + CASE WHEN MAX(Rev) > 0 THEN '/Rev' + CONVERT(VARCHAR(15), MAX(Rev)) ELSE '' END

    FROM @test-2

    -- find out if/where the "Rev" part is in the string

    CROSS APPLY (SELECT PATINDEX('%Rev[0-9]%', kms_quoteorder)) ca1(Pos)

    -- separate the two parts of kms_quoteorder into quoteorder and rev. Convert rev to integer.

    CROSS APPLY (SELECT LEFT(kms_quoteorder, CASE WHEN ca1.Pos > 0 THEN ca1.Pos-2 ELSE LEN(kms_quoteorder) END),

    CONVERT(INTEGER, CASE WHEN ca1.POS > 0 THEN SUBSTRING(kms_quoteorder, ca1.Pos + 3, LEN(kms_quoteorder)) ELSE '' END)

    ) ca2(QuoteOrder, Rev)

    GROUP BY QuoteOrder

    This produces these results:

    NULL

    15-35553/Rev10

    15-37666

    15-37779/Rev8

    How does this work for you?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I have one more question to this code you helped me with. I also want the createdon displayed but when I added it after select I get and error:

    Msg 207, Level 16, State 1, Line 17

    Invalid column name 'createdon'.

    CREATE TABLE #test

    (

    createdon datetime,

    kms_quoteorder varchar(15),

    test varchar(2)

    )

    INSERT INTO #test VALUES ('2015-06-10 14:03:04.000',NULL,'nn')

    INSERT INTO #test VALUES ('2015-07-22 15:13:39.000','15-37666','nn')

    INSERT INTO #test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7','nn')

    INSERT INTO #test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8','nn')

    INSERT INTO #test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9','nn')

    INSERT INTO #test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10','nn')

    SELECT createdon,

    [QuoteOrder] AS [kms_quoteorder]

    ,[MAX_kms_quoteorder] = CASE WHEN RevNo != 0 THEN [QuoteOrder] + '/Rev' + CAST(RevNo AS VARCHAR(2))

    ELSE [QuoteOrder] END

    FROM

    (SELECT

    [QuoteOrder] = CASE WHEN CHARINDEX('/Rev', kms_quoteorder) > 0

    THEN LEFT(kms_quoteorder, CHARINDEX('/Rev', kms_quoteorder)-1)

    ELSE kms_quoteorder END

    ,[RevNo] = MAX(CASE WHEN CHARINDEX('/Rev', kms_quoteorder) > 0

    THEN CAST(RIGHT(kms_quoteorder, LEN(kms_quoteorder) - (CHARINDEX('/Rev', kms_quoteorder)+3)) AS INT)

    ELSE 0 END)

    FROM #TEST as

    WHERE kms_quoteorder IS NOT NULL

    GROUP BY (CASE WHEN CHARINDEX('/Rev', kms_quoteorder) > 0

    THEN LEFT(kms_quoteorder, CHARINDEX('/Rev', kms_quoteorder)-1)

    ELSE kms_quoteorder END)

    ) [A]

    ORDER BY [kms_quoteorder]

    --select * from #test

    DROP TABLE #Test

  • The createdon column is not included in the derived table (subquery).

    I'm not sure if you need to include it, or if you need to join the derived table to the original table. That depends on the results you're looking for.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Not sure how I would add it to the derived subquery

  • lcarrethers (8/26/2015)


    Not sure how I would add it to the derived subquery

    Not sure how to do it either if you don't give sample data and expected results.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • A bit late to the party and surprised nobody else commented on this yet. The reason you are having this problem in the first place is because you have two pieces of data in a single intersection. This violates 1NF and causes untold amount of pain. If you can separate the QuoteNumber and Revision into two columns this becomes simple because the revision number can be an integer so there are no gymnastics required to get the most recent revision.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Also a bit late to the party, but why won't the created on column work?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Sean Lange (8/26/2015)


    A bit late to the party and surprised nobody else commented on this yet. The reason you are having this problem in the first place is because you have two pieces of data in a single intersection. This violates 1NF and causes untold amount of pain. If you can separate the QuoteNumber and Revision into two columns this becomes simple because the revision number can be an integer so there are no gymnastics required to get the most recent revision.

    Well, to be fair, someone has already mentioned it:

    Alvin Ramard (8/11/2015)


    ...Would also be much easier, if the Rev # was stored as an integer in a separate column.

    Definitely worth a second mention, though 🙂

  • Jacob Wilkins (8/26/2015)


    Sean Lange (8/26/2015)


    A bit late to the party and surprised nobody else commented on this yet. The reason you are having this problem in the first place is because you have two pieces of data in a single intersection. This violates 1NF and causes untold amount of pain. If you can separate the QuoteNumber and Revision into two columns this becomes simple because the revision number can be an integer so there are no gymnastics required to get the most recent revision.

    Well, to be fair, someone has already mentioned it:

    Alvin Ramard (8/11/2015)


    ...Would also be much easier, if the Rev # was stored as an integer in a separate column.

    Definitely worth a second mention, though 🙂

    haha I missed through the long list of responses. 🙂

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • yeah it was designed poorly just trying to help them get some reporting from it by the max number.

Viewing 10 posts - 31 through 39 (of 39 total)

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