August 11, 2015 at 4:17 pm
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
August 26, 2015 at 7:15 am
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
August 26, 2015 at 8:18 am
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.
August 26, 2015 at 8:26 am
Not sure how I would add it to the derived subquery
August 26, 2015 at 8:41 am
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.
August 26, 2015 at 8:48 am
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/
August 26, 2015 at 9:05 am
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/
August 26, 2015 at 9:22 am
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 🙂
August 26, 2015 at 10:15 am
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/
August 26, 2015 at 10:29 am
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