I have a database that's not in production, so the main table being CustodyDetails, this table has a
ID int IDENTITY(1,1) PRIMARY KEY column and I'm looking for a way of adding another unique identifier that its not referenced in any other table, I would think by taking this in account the content of the column wouldn't be exactly an identity key.
This new identity column has a few specific details though, and here's where my problem starts.
The format is as follows:
XX/YY where XX is an auto incrementable value that resets/restarts every new year and YY is the last 2 digits of the current year
SELECT RIGHT(YEAR(GETDATE()), 2).
So for example lets pretend one record is added a day starting from the 28/12/2015 ending 03/01/2016, the column would look like:
ID ID2 DATE_ADDED 1 1/15 2015-12-28 2 2/15 2015-12-29 3 3/15 2015-12-30 4 4/15 2015-12-31 5 1/16 2016-01-01 6 2/16 2016-01-02 7 3/16 2016-01-03
I thought of using the frontend to parse the composite ID (ID2 in the example) get the last 2 digits and compare with the last 2 digits of the current year and then decide whether or not to start a new correlative. Of course it would be grand to be able to do it all on the database side.
EDIT 1: btw, I have also seen people using separate tables just to store parallel identity keys, so one table Identity key becomes a second table secondary Key, this sounds a bit dodgy but maybe this is the case such implementation comes in place?
EDIT 2: This extra ID is a legacy document reference that labels every file/record. I guess one could think of it as an special alias for the main ID.
The number of records this database handles yearly hasn't been out of the 100 in the last 20 years and is highly (really, extremely highly) improbable that it would, of course if it does go over 99 it the field will be able to carry on with the extra digit and the frontend/procedure will be able to go over 99, so its not like it changes things.
Of course some of these details I did't mention at the beginning because they would only narrow down the possibilities of solution to suite my specific need, tried to keep the problem range broader.