title: Auto increment a column value based on another column of a database table
body: <p>I am creating a university course and results management system on c# .net. I have created this table on database.</p>
  
  <pre><code>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
  ) 
  </code></pre>
  
  <p>Values are:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>User only gives the Name,Email,Department and Date. ID and RegistrationNo is auto generateing.</p>
  
  <p>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'</p>