Azher Aleem Azher Aleem - 1 month ago 9
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

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

How can I do this?

Answer

In SQL Server, it can be as simple as:

transactionId =  
CONCAT(DATEPART(MONTH, GETDATE()), 
       DATEPART(DAY, GETDATE()), 
       DATEPART(YEAR, GETDATE()),
       (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:

100920164

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;
Comments