March 22, 2016 at 10:56 am
I have an nvarchar column called rptnum in a table that I have created a .NET routine to insert a report number. This is built by getting the current year, then a '-', then a 3 digit number, another '-' and then an incrementing number (increment field in the table). I need to query that field and can't seem to get the where clause correct using datepart or year. As I say, the column is nvarchar, and not a date column so I am not even sure it will work, but here's the clause I am trying:
select isnull(max(increment),0) from activity where rptnum like (and here's where I do not know syntax) year-101-%
I am using isnull to establish the first record being there, then incrementing the increment field by 1 if it is.
obviously, this does not work, probably because either the year function will not work on a non-date field or the single-double quote thing. What I am trying to determine is the max rptnum where the rptnum is like the current year '-' a number (101 in this sample)'-' and an increment field. The field contents are similar to '2016-101-1' and 2016-213-1, 2, 3, etc.
Can anyone assist here with proper syntax or perhaps a different approach?
March 22, 2016 at 11:12 am
The year, the 3-digit number and the incrementing number should all be stored as separate columns. You then use a computed column to construct the rptnum column. That allows you to query each column value separately, making the query you need to do very easy again: where year = desired_year and number = desired_number.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 22, 2016 at 3:45 pm
David Hall-426383 (3/22/2016)
I have an nvarchar column called rptnum in a table that I have created a .NET routine to insert a report number. This is built by getting the current year, then a '-', then a 3 digit number, another '-' and then an incrementing number (increment field in the table). I need to query that field and can't seem to get the where clause correct using datepart or year. As I say, the column is nvarchar, and not a date column so I am not even sure it will work, but here's the clause I am trying:select isnull(max(increment),0) from activity where rptnum like (and here's where I do not know syntax) year-101-%
I am using isnull to establish the first record being there, then incrementing the increment field by 1 if it is.
obviously, this does not work, probably because either the year function will not work on a non-date field or the single-double quote thing. What I am trying to determine is the max rptnum where the rptnum is like the current year '-' a number (101 in this sample)'-' and an increment field. The field contents are similar to '2016-101-1' and 2016-213-1, 2, 3, etc.
Can anyone assist here with proper syntax or perhaps a different approach?
Would this work for you?
where rptnum like CONVERT(VARCHAR(4), YEAR(GETDATE())) + '-' + @ThreeDigitNumber + '-%'
_____________
Code for TallyGenerator
March 22, 2016 at 4:04 pm
Thanks very much..that works!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply