3

I am creating a university course and results management system on c# .net. I have created this table on database.

CREATE TABLE Students (
  [ID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
  [RegistrationNo] AS ([Department] + '-' + CAST(YEAR([Date]) AS char(4)) + '-' + RIGHT('00' + CONVERT(varchar(3), ID), 3)),
  [Name] [varchar](40) NOT NULL,
  [Email] [varchar](50) NOT NULL,
  [Department] [varchar](20) NOT NULL,
  [Date] [date] NOT NULL
)

Values are:

ID | RegistrationNo | Name   | Email            | Department | Date
1  | CSE-2018-001   | Robiul | robiul@mail.com  | CSE        | 2018-05-05
2  | CSE-2018-002   | Arman  | arman@mail.com   | CSE        | 2018-05-10
3  | EEE-2018-003   | Sayed  | sayed@mail.com   | EEE        | 2018-06-10
4  | EEE-2017-004   | Sazzad | sazzad@mail.com  | EEE        | 2017-06-10

User only gives the Name,Email,Department and Date. ID and RegistrationNo is auto generateing.

But I want to increment the RegistrationNo based on Department and Year of Date. For example CSE-2017's first ID's RegistrationNo will like this 'CSE-2017-001' and the next will 'CSE-2017-002'. EEE-2018-003 will be 'EEE-2018-001'

Robiul
  • 55
  • 6
  • Ok. So what's the issue? – Chetan May 20 '18 at 08:38
  • I think if you add ',' before your email it will compile will have desired output – Eldho May 20 '18 at 08:49
  • 1
    You won't be able to base this off the ID field as it will always be unique and will increment by 1 each new record. You would probably want to create a `Sequence` column that holds your Department/Year sequence. Then you would search for the max number based on the current Department and Year you're inserting and then add 1 to it. – Charles May May 20 '18 at 10:21
  • @Charles May, can you give me the query to add this sequence? I cant do it. – Robiul May 20 '18 at 11:08
  • My suggestion is to implement your own sequence table, described here: https://stackoverflow.com/a/12784166/6305294 . – Alex May 20 '18 at 22:26

0 Answers0