August 10, 2015 at 2:44 pm
CREATE TABLE test
(
createdon datetime,
kms_quoteorder varchar(15)
)
INSERT INTO test VALUES ('2015-06-10 14:03:04.000','NULL')
INSERT INTO test VALUES ('2015-07-22 15:13:39.000','15-37666')
INSERT INTO test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')
INSERT INTO test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')
INSERT INTO test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')
INSERT INTO test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')
select * from test
drop table test
Here is more test data
August 10, 2015 at 2:54 pm
Based on the above sample data, what is the expected results?
August 10, 2015 at 2:55 pm
Also, is the string 'Null' a real Null or a string?
August 10, 2015 at 2:58 pm
Overcomplicated this thread has become. Weak was the force.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 2:59 pm
The NULL is a real NULL
and I'm expecting the max kms_quoteorder
so if there are no /REV then I should only see the number.
The /REV stands for Revision. Poorly designed, I know. So we had a order number Revised 10 times at one point. They are wanting the see the latest
August 10, 2015 at 3:34 pm
lcarrethers (8/10/2015)
The NULL is a real NULLand I'm expecting the max kms_quoteorder
so if there are no /REV then I should only see the number.
The /REV stands for Revision. Poorly designed, I know. So we had a order number Revised 10 times at one point. They are wanting the see the latest
What should it return for this row? INSERT INTO test VALUES ('2015-06-10 14:03:04.000','NULL')
Don't give me a generic answer. Type out the actual values.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 3:38 pm
I don't think they want to see any NULL values.
August 10, 2015 at 3:44 pm
lcarrethers (8/10/2015)
I don't think they want to see any NULL values.
FYI, the NULL in this INSERT is NOT a null. It is a string whose value is the string (word) NULL.
INSERT INTO test VALUES ('2015-06-10 14:03:04.000','NULL')
August 10, 2015 at 3:48 pm
They want to ignore these NULLS, blank values in that column. so if the word null is typed in or blank, they don't want to see them
August 10, 2015 at 3:51 pm
What's the maximum number of digits that the number after REV can be? Will it always be an integer?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 10, 2015 at 3:54 pm
99 and it will always be a integer
August 11, 2015 at 8:11 am
This is a very messy query to right. If only the Rev # had a leading zero when necessary, then this would be simple.
Would also be much easier, if the Rev # was stored as an integer in a separate column.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 8:28 am
If this doesn't give a full solution, it might give an idea.
with base as (
select
*,
rn = row_number() over (partition by left(kms_quoteorder,charindex('/',kms_quoteorder + '/') - 1)
order by CAST( CASE WHEN kms_quoteorder NOT LIKE '%/Rev%' THEN 0
WHEN RIGHT( kms_quoteorder, 2) LIKE 'v[0-9]' THEN RIGHT( kms_quoteorder, 1)
ELSE RIGHT( kms_quoteorder, 2) END AS int) desc)
from
test
)
select createdon, kms_quoteorder
from base
where rn = 1;
August 11, 2015 at 8:41 am
Take a look at this. It may not be exactly what you need, but it's a start.
CREATE TABLE #test
(
createdon datetime,
kms_quoteorder varchar(15)
)
INSERT INTO #test VALUES ('2015-06-10 14:03:04.000',NULL)
INSERT INTO #test VALUES ('2015-07-22 15:13:39.000','15-37666')
INSERT INTO #test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')
INSERT INTO #test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')
INSERT INTO #test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')
INSERT INTO #test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')
SELECT
[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
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]
DROP TABLE #Test
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 3:00 pm
Thanks, this helps
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply