Azher Aleem Azher Aleem - 1 year ago 65
SQL Question

C# sequence number issue in database

I have a column in the database table i.e. transaction Id. Its data type is var char(50). Now I want to save a row by combining the current date with a sequential number generated.

For Example

is the date so first transaction is saved like 10920161 and the next one like
and so on. The sequential number is reset to 1 after every day.

How can I do this?

Answer Source

In SQL Server, it can be as simple as:

transactionId =  
       (SELECT COUNT(1) 
        FROM tableName 
        WHERE CONVERT(DATE, dateColumn) = CONVERT(DATE, getdate())) + 1)

Get the Number of transactions for that date, and add one to it. Concat that with a formatted string of Today's Date.

If you already have 3 records for today's date, the result would be:


Running SQL-Fiddle

Edit: EF suggested snippet:

string dateStr = DateTime.Today.ToString("Mdyyyy");
DateTime todayMidnight = DateTime.Today;
DateTime tomorrowMidnight = DateTime.Today.AddDays(1);

obj.transactionId = dateStr + 
                      context.tableName.Count(x=> x.dateColumn >= todayMidnight 
                      && x.dateColumn < tomorrowMidnight) + 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download