select RIGHT('00' + CAST(@RowCount AS varchar(3)), 3)
Maximum students allowed per year 999 ONLY !!
On Fri, Nov 20, 2009 at 9:37 AM, rbr <[email protected]> wrote:
> So, as you can see, there are many ways to solve this issue. I am
> assuming you are trying to generate the Student_ID
> on insert? A stored procedure would be best if possible.
>
> In that case, I would do something very similar to what Cerebrus
> recommended:
>
> Begin
>
> DECLARE @RowCount int
> DECLARE @StuNum varchar
>
> SET @RowCount = (SELECT COUNT(1) from Student_Details where datepart
> (year, Dateof_Admission)= datepart(year, @Dateof_Admission))
>
> SET @StuNum = (select 'T' + right(DATEPART(yy, current_timestamp), 2)+
> '-' + RIGHT('00' + CAST(@RowCount AS varchar(3)), 3))
>
> INSERT Student_Details
> (
> Student_ID,
> Fname,
> Lname,
> Full_Name, --
> Address,
> DOB,
> ContactNO,
> email,
> additional_detail,
> Course_ID,
> Dateof_Admission,
> Dateof_Graduation
> )
> VALUES
> (
> @StuNum,
> @Fname,
> @Lname,
> @Full_Name, --
> @Address,
> @DOB,
> @ContactNO,
> @email,
> @additional_detail,
> @Course_ID,
> @Dateof_Admission,
> @Dateof_Graduation,
> )
>
> END
>
> Let me know if this workd for you. I'm sure we can figure something
> out.
>
> rbr
>
> On Nov 18, 9:12 am, Cerebrus <[email protected]> wrote:
> > Possibly something like :
> >
> > ---
> > SELECT MAX(SUBSTRING(Student_ID, 5, 4))
> > FROM Student_Details
> > WHERE SUBSTRING(Student_ID, 2, 2) = @TwoDigitYear
> > ---
> >
> > Example param value: @TwoDigitYear = '09'
> >
> > This would give you something like '0118'. You then know that the next
> > registration number for that year should be '0119' (You could also
> > return the value incremented by one in SQL itself.)
> >
> > On Nov 18, 6:36 pm, nu1silva <[email protected]> wrote:
> >
> >
> >
> > > ok here we go again.... :(
> >
> > > asked this before an got some great answers but i'm still not able to
> > > get it right :(
> >
> > > first of all,
> > > im doin the project on Visual Studio 2005 C#.NET and using MSSQL
> > > 2005.
> >
> > > the objective is to register a student and save data on the DB.
> >
> > > what i want to do is create a custom Registration number or the
> > > students that register for a course
> > > examples,
> > > T(year)-(student number)
> > > so itll be like >>> T09-0118, T09-0001, T09-1234
> >
> > > how to get the input>>
> >
> > > T >> we must ad that
> > > 09 >> should get from date
> > > - >> we add
> > > 0001 >> (where can i get that???)
> >
> > > and it would be great if i can create the Registration Number before i
> > > insert it to the database.
> >
> > > im still learning and would appreciate the help :)
> > > Thanks- Hide quoted text -
> >
> > - Show quoted text -
>
