Query help
Question
Answers
-
I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)
Explanation and Working Query:
/****************** DDL+DML */ Create table T1 (Id int identity primary key, VoucherNo varchar(10), TransNo varchar(10) ) Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1') select * from T1 /****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */ ;WITH cte AS ( SELECT * ,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER ( ORDER BY VoucherNo ) AS Grp FROM T1 ) SELECT TransNo ,MIN(VoucherNo) AS FirstVoucherNo ,MAX(VoucherNo) AS LastVoucherNo ,COUNT(*) AS Quantity FROM cte GROUP BY TransNo, Grp /* there is a mistake in the desired output on the WIKI: TransNo FirstVoucher LastVoucher Quantity trans1 V100 V104 5 trans2 V106 V106 1 last row should be trans1 V106 V106 1 */ /* why the query is Very Limited and how to fix it: ------------------------------------------------- 1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get: Insert into T1 values ('V1000','Trns1') Naomi's query output will be: Trns1 V1000 V104 5 Trns1 V100 V106 2 This is wrong! 2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type. if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add: Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin') Naomi's query output will be: NotWorkin V9 V9 1 NotWorkin V10 V10 1 Trns1 V1000 V104 5 Trns1 V100 V106 2 This is wrong! The currect output that we want is: NotWorkin v9 v10 2 Trns1 v100 v104 5 Trns1 v106 v106 1 Trns1 v1000 v1000 1 the sulotion is to use numbers. */ -- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number. -- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number -- and the format can be more complax... -- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was) ;WITH cte01 AS ( SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo FROM T1 ), cte02 as ( select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp from cte01 ) select TransNo , 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo ,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo ,COUNT(*) AS Quantity -- or the "Quantity" results we can use this -- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1) from cte02 GROUP BY TransNo, Grp /****************** Clean */ drop table T1 go* Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.I hope this is helpful
- Marked as answer by Samuel Lester - MSFTMicrosoft employee, Moderator Friday, August 2, 2013 8:43 AM
All replies
-
I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)
Explanation and Working Query:
/****************** DDL+DML */ Create table T1 (Id int identity primary key, VoucherNo varchar(10), TransNo varchar(10) ) Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1') select * from T1 /****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */ ;WITH cte AS ( SELECT * ,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER ( ORDER BY VoucherNo ) AS Grp FROM T1 ) SELECT TransNo ,MIN(VoucherNo) AS FirstVoucherNo ,MAX(VoucherNo) AS LastVoucherNo ,COUNT(*) AS Quantity FROM cte GROUP BY TransNo, Grp /* there is a mistake in the desired output on the WIKI: TransNo FirstVoucher LastVoucher Quantity trans1 V100 V104 5 trans2 V106 V106 1 last row should be trans1 V106 V106 1 */ /* why the query is Very Limited and how to fix it: ------------------------------------------------- 1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get: Insert into T1 values ('V1000','Trns1') Naomi's query output will be: Trns1 V1000 V104 5 Trns1 V100 V106 2 This is wrong! 2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type. if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add: Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin') Naomi's query output will be: NotWorkin V9 V9 1 NotWorkin V10 V10 1 Trns1 V1000 V104 5 Trns1 V100 V106 2 This is wrong! The currect output that we want is: NotWorkin v9 v10 2 Trns1 v100 v104 5 Trns1 v106 v106 1 Trns1 v1000 v1000 1 the sulotion is to use numbers. */ -- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number. -- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number -- and the format can be more complax... -- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was) ;WITH cte01 AS ( SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo FROM T1 ), cte02 as ( select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp from cte01 ) select TransNo , 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo ,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo ,COUNT(*) AS Quantity -- or the "Quantity" results we can use this -- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1) from cte02 GROUP BY TransNo, Grp /****************** Clean */ drop table T1 go* Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.I hope this is helpful
- Marked as answer by Samuel Lester - MSFTMicrosoft employee, Moderator Friday, August 2, 2013 8:43 AM
