CODEWITHSUNDEEP
sqlsql-servervb.net
Silentbob
Silentbob

Reputation: 3065

How to reset a custom ID to 1 at the beginning of each month

I have an ID that I create in both the asp.net vb app and SQL Server that has a format of MM/YYYY/## where the #'s are a integer. The integer is incremented by 1 through out the month as users generate forms so currently it is 08/2013/39.

The code I use for this is as follows

    Dim get_end_rfa As String = get_RFA_number()
    Dim pos As Integer = get_end_rfa.Trim().LastIndexOf("/") + 1
    Dim rfa_number = get_end_rfa.Substring(pos)
    Convert.ToInt32(rfa_number)
    Dim change_rfa As Integer = rfa_number + 1

    Dim rfa_date As String = Format(Now, "MM/yyyy")

    Dim rfa As String = rfa_date + "/" + Convert.ToString(change_rfa)

    RFA_number_box.Text = rfa

Public Function get_RFA_number() As String
    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("AnalyticalNewConnectionString").ConnectionString)
    conn.Open()
    Dim cmd As New SqlCommand("select TOP 1 RFA_Number from New_Analysis_Data order by submitted_date desc", conn)
    Dim RFA As String = (cmd.ExecuteScalar())
    conn.Close()
    Return RFA
End Function

I need to reset the integer to 1 at the beginning of each month. How do I go about this?

Upvotes: 1

Views: 992

Answers (3)

very9527
very9527

Reputation: 959

Select the max(record) from your table, and compare the currentDate with the record's Date. When they are different. reset the ID = 1 . The SQL like this

select ID, recordDate 
from TABLE
where ID = (select max(ID) from TABLE) 
where datename(YYYY ,getdate()) = datename(YYYY ,recordDate()) 
and datename(MM ,getdate()) = datename(MM ,recordDate())

Upvotes: 0

Philip Kelley
Philip Kelley

Reputation: 40339

@very9527’s method works (+1), but datepart manipulations are awkward. An outline of another option based on the original code:

  • get the most recent value from the database, get_end_rfa
  • compare the leftmost 7 characters with the "now" value, rfa_date
  • if they are equal, it’s the same year/month, the first item has already been created, use an incremented value
  • if they are not equal, it’s a new month and the “first” item for the month has not yet been set, so reseed it with 1

Do you have to worry about concurrency? What happens if two instances simultaneously try to add the “next” value?

Upvotes: 0

Silentbob
Silentbob

Reputation: 3065

using a combination of the answer above and some further research I am using the code below. This resolves my query.

declare @mydate as date

    set @mydate = CONVERT(char(10), GetDate(),103)
    if convert(char(10), (DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)), 126) = CONVERT(char(10), GetDate(),126)
    select right(convert(varchar, @mydate, 103), 7) + '/01'
    else
    select TOP 1 RFA_Number 
    from New_Analysis_Data 
    order by submitted_date desc

Upvotes: 0

Related Questions