CODEWITHSUNDEEP
sqlt-sqlsql-server-2012increment
MrBovineJoni
MrBovineJoni

Reputation: 336

SQL Server counter column that increments when a new record has been added

I'm working on a stored procedure that needs to keep track of reports that have run each day. If a report has run multiple times, I need a column that increments each time the report is run. I'm not sure how to make it keep track of ReportID or ReportName without explicitly naming each report to keep track of. Using SQL Server 2012, let's call the table ProcessMaster.

Time  ReportID   ReportName  (Increment Column)
----  --------   ----------  ------------------
7:00    2145      Job1               1
7:30    2145      Job1               2
7:30    2146      Another            1
8:00    2145      Job1               3
8:30    2146      Another            2

Upvotes: 2

Views: 219

Answers (1)

Bulat
Bulat

Reputation: 6969

There is an option of not having that column at all, by generating rank on the fly:

SELECT *, 
  ROW_NUMBER() OVER (PARTITION BY ReportId ORDER BY Time) as IncrementColumn
FROM ProcessMaster

If you want that field to be stored you have options:

  • Write a trigger
  • Increment in your application layer

Upvotes: 3

Related Questions