S.No & year end

[ahassan99]

Hi there,

I have got a column named SNO. Its value should be a combination of the current year & and the serial no. E.G. 2007001,2007002,2007003 ....etc

My problem started with the new year 2008. I need to reset the counter when it is a new year to start from number 1 again. How do I check if it is a new year ?!

Also, the way I am giving new SNOs is that I count how many records in the table and then increment that by one, then concat it with the System.DateTime.Today.Year.

What is the best way of assigning SNO number in the format above and it automatically resets the number to 1 ???????

 

[giadich]

store the value of the year some where. every time you add a new record, grab the year, compare this with the stored year if the two match, increment the counter otherwise set it to 1 and concat the two values to create the sno.

 

[InertiaM]

Also, the way I am giving new SNOs is that I count how many records in the table and then increment that by one, then concat it with the System.DateTime.Today.Year.

Surely this will give you your New Year as well. For example, at the start of 2008 it should return a count of zero records - increment by one and you have 2008001

 

[Arg81]

Surely this will give you your New Year as well.

Not if he's not checking. If it's just concatinating the 2 values, he'll end up with

2007001
2007002
2007003
2008004

because it counted the records, it knew the next increment was 4 , but the year is 2008.

It might not be pretty but it might work, I'd do something like (pseudo code )

GoTo last record in table. If first four characters = system.dateTime.today.year then increment to next value. If not, then SNO = system.dateTime.today.year & "001"

 

[cjard]

Not if he's not checking. If it's just concatinating the 2 values, he'll end up with

2007001
2007002
2007003
2008004

because it counted the records, it knew the next increment was 4 , but the year is 2008.

Yeah, that's because he counts every record in the table. It would be smarter to count every record thatstarts with 2008, of which there will be none..


SELECT COUNT(*) + 1 AS new_sno FROM table WHERE sno LIKE currentYear() + '%'


So he has the solution already, its just not clever enough (right now he does:

SELECT COUNT(*) FROM table

 

[ahassan99]

It Works !!!!!!

Hi guys,

WOW, I am really thankful to all of you. I in fact had done in a mix o IF statement and While loop. Here is how I did it:

Function GetNewContainerSNO()
Dim tempyear, lastrecordyear, totrows, sno, x, yearcounter As Integer
Dim contsno As String
totrows = DsCont1.Tables("tablename").Rows.Count
contsno = Val(DsCont1.Tables("tablename").Rows(totrows - 1).Item("sno"))
lastrecordyear = Val(contsno.Substring(0, 4))


If System.DateTime.Today.Year = lastrecordyear Then
While x < totrows
contsno = DsCont1.Tables("tablename").Rows(x).Item("sno")
tempyear = contsno.Substring(0, 4)
If lastrecordyear = tempyear Then
yearcounter += 1
End If
x += 1
End While
yearcounter = yearcounter + 1
sno = Val(lastrecordyear & yearcounter.ToString("D3"))
Return sno
Exit Function
Else
totrows = 1
sno = Val(System.DateTime.Today.Year & totrows.ToString("D3"))
Return sno

End If
End Function

 

[cjard]

That sure looks a complicated solution to a simple problem..

 

[ahassan99]

How do you think I can make it simpler. First, I get the last record's year. Then I check if it is equal to current year, then I have to first know how many records I have with the current year then increment that by one. If it is not equal to current year, then I just start from one ....

How do you think I can make it simpler ???