Hi amitsinghr,
Refer the below test query for your referee implement it by using stored procedure. Where you can set Customer Id and at insert time you can pass it to related Table.
SQL
-- CREATED TEMPARARY TABLE
DECLARE @TESTTable AS TABLE(customerId VARCHAR(20),NAME VARCHAR(50))
INSERT INTO @TESTTable VALUES('Cust1502170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1502170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1502170003','Name 3')
INSERT INTO @TESTTable VALUES('Cust1502170004','Name 4')
INSERT INTO @TESTTable VALUES('Cust1502170005','Name 5')
INSERT INTO @TESTTable VALUES('Cust1402170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1402170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1402170003','Name 3')
INSERT INTO @TESTTable VALUES('Cust1302170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1302170002','Name 2')
INSERT INTO @TESTTable VALUES('Cust1202170001','Name 1')
INSERT INTO @TESTTable VALUES('Cust1102170001','Name 1')
-- Declare currentdate in variable @DateTime
DECLARE @DateTime DATETIME
SET @DateTime = (SELECT GETDATE())
--SET @DateTime = '2017-02-14 12:13:48.407'
--SET @DateTime = '2017-02-13 12:13:48.407'
--SET @DateTime = '2017-02-12 12:13:48.407'
--SET @DateTime = '2017-02-11 12:13:48.407'
--SET @DateTime = '2017-02-10 12:13:48.407'
-- FROM Here test quey is started
DECLARE @CustomerId VARCHAR(20)
DECLARE @LastCustomerId VARCHAR(5)
DECLARE @Day AS VARCHAR(2) -- to store day valeue
DECLARE @Month AS VARCHAR(2) -- to store month value
DECLARE @Year AS VARCHAR(4) -- to store year value
DECLARE @CureectDatecustomerCount VARCHAR(5)
DECLARE @customerTextPrefix VARCHAR(20) -- It will store CustomerId Prefix text
DECLARE @customerTextNamePrefix VARCHAR(20) -- to store CustomerPrefix text
SET @customerTextNamePrefix = 'Cust' -- starting value for customerPrefix text
SET @Day = RIGHT('0'+cast(DATEPART(DD,@DateTime) AS varchar(2)), 2) -- to store Day vlaue
SET @Month = RIGHT('0'+cast(DATEPART(MM,@DateTime) AS varchar(2)), 2) -- to store month value
SET @Year = (DATEPART(YY,@DateTime)) -- Store Year Value
-- concatinate the all string also get only last two value from @year variable
SET @customerTextPrefix = @customerTextNamePrefix + @Day + @Month + SUBSTRING(@Year,(LEN(@Year) - 2 + 1),2)
--match it with the count matching with prefix value and add pluse one in it so it always give next prefix number
-- Check existance of record if exist in table
IF EXISTS(SELECT customerId
FROM @TESTTable WHERE customerId
LIKE @customerTextPrefix +'%')
BEGIN
-- select last customerId value only last 4 number from last customerid by CurrentDate prefix value
SELECT TOP 1 @LastCustomerId = substring(customerId,(LEN(customerId) - 3),4) + 1
FROM @TESTTable
WHERE customerId LIKE @customerTextPrefix +'%'
order by substring(customerId,(LEN(customerId) - 3),4) DESC
-- SET customer Id with CurrentDate prefix value
SET @CustomerId = @customerTextPrefix + RIGHT('0000'+cast(@LastCustomerId AS varchar(4)), 4)
END
ELSE
BEGIN
-- SET customer Id first CustomerId for with CurrentDate prefix value
SELECT @CustomerId = @customerTextPrefix + '0001'
END
-- Use the @CustomerId variable value as to display or to insert in any statement
SELECT @CustomerId
Screenshot