CODEWITHSUNDEEP
sqlsql-servert-sql
Leon
Leon

Reputation: 137

Change number automatically with function

I have a simple function that get a number and sum + 1: 

ALTER FUNCTION [dbo].[GetNextProjectNumber] ()  
RETURNS INT
AS  
BEGIN
    DECLARE @LegacyKey INT

    SELECT @LegacyKey = (MAX(CAST(LegacyKey AS INT)) + 1) 
    FROM Project; 

    RETURN @LegacyKey;
END

So every year we change @LegacyKey number for new one manually like:

In 2017: 18000
In 2018: 19000
In 2019: 20000
etc..

This happen every 1st of October, so I want to know if its possible to change it automatically.

Today value should be 20000, once I change it it should sum + 1 since October of 2020, in October of 2020 it should change to 21000

How can I achieve that?

Upvotes: 0

Views: 59

Answers (2)

Cameron Tinker
Cameron Tinker

Reputation: 9789

Here's another alternative:

select (year(dateadd(month, 3, getDate())) - 2000) * 1000

Example 1: 

select (year(dateadd(month, 3, '2019-09-30 13:29:07.433')) - 2000) * 1000

Returns

19000

Example 2:

select (year(dateadd(month, 3, '2020-10-01 13:29:07.433')) - 2000) * 1000

Returns

21000

By adding 3 months to the current date, we are able to ensure that the 'next' year is selected instead of the current year when multiplying by 1000.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The number would appear to be:

select (year(dateadd(month, -10, getdate())) - 2017) * 1000 + 17000

Actually, I'm not sure if you want + 17000 or + 18000 but the idea is the same. (Does the change on 2019-10-01 produce the 2020 number or the 2019 number?)

Upvotes: 1

Related Questions