TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Dong Lam Trien
828
986
147.7k
Query get the largest ID number of the string ?
Apr 13 2017 10:35 PM
Suppose in SQL Server 2005 I have the TABTHUCHI data table has the following fields:
IDSOPHIEUTHUCHI // Primary Key
RIGHT
...
IDSOPHIEUTHUCHI
NGAY
PC-04/08/16-008
04/08/2016
PC-07/06/2016-006
07/06/2016
PC-08/10/2017-010
08/10/2017
PC-08/12/2017-012
08/12/2017
PC-10/01/2015-2
10/01/2015
PC-25/09/2017-009
25/09/2017
PT-01/02/2015-001
01/02/2015
PT-03/11/17-011
03/11/2017
PT-05/04/2015-004
05/04/2015
PT-15/07/2016-07
15/07/2016
PT-20/05/2016-005
20/05/2016
PT-31/03/2015-003
31/03/2015
At the IDSOPHIEUTHUCHI field, you will see the letter "PT" or "PC", the year will be 2 characters or 4 characters and the last digit must be PT-(dd)/(MM)/(yyyy) - (***)
Use (*) is the incremental number generated by the system, the length is determined by the number of asterisks, a maximum of 9.
Example:
HD (**) includes 99 numbers running from HD01, HD02 to HD99.
HD (**) includes 999 numbers running from HD001, HD002 to HD999.
Use (MM) to get the current month.
Use (years) to retrieve the current year (last two digits of the year).
Use (yyyy) to get the current year (all 4 numbers).
Use (dd) to get the current date.
According to the above data, you want to get the largest ID of 2015 "PT". Find the last digit of 2015: PT-05/04/2015-004, the largest ID 2015 will be 005, and assume The largest ID number of this date is 14/04/2015 will be: PT-14/04/2015-005, when taking the largest number you must also note Year can be 2 digits or 4 digits and format input.
If the input format: PT-(dd)/(MM)/(yy)-(***) output: PT-14/04/15-005
If the input format: PT-(dd)/(MM)/(yyyy)-(*****) output: PT-14/04/2015-00005
If the input format: PT-(***) output: PT-005
Similar to other years for "PT" or "PC" and the largest ID date.
If the "PT" format of the above data:
If the input format: PT-(dd)/(MM)/(yyyy)-(***) and "14/04/2016", the output: PT-14/04/2016-008
If the input format: PT-(dd)/(MM)/(yyyy)-(***) and "14/04/2017", the output: PT-14/04/2017-012
If the input format: PT-(***) and "14/04/2017" is output PT-012
If the "PC" format of the above data:
If the input format: PC-(dd)/(MM)/(yyyy)-(***) and "14/04/2015" is output: PC-14/04/2015-003
If the input format: PC-(dd)/(MM)/(yyyy)-(**) and "14/04/2016" then output: PC-14/04/2016-09
If the input format: PC-(dd)/(MM)/(yy)-(***) and "14/04/2017" then output: PC-14/04/17-013
How can SQL Server retrieve the largest ID in string form as shown above ?
Reply
Answers (
3
)
interview questions
While executing my code I get exception