Start by adding two columns to your Table:
The first is an INT IDENTITY column called
Ident
, autoincrementing by one
The second is a DATE column called
InsDat
which defaults to GETDATE()
Then when you want the rows with a daily sequence use this:
SELECT m.Ident, m.InsDat, m.OtherColumnList, m.Ident - s.MinSeq + 1 AS Seq FROM MyTable m
JOIN (SELECT Insdat, MIN(Ident) AS MinSeq FROM MyTable GROUP BY InsDat) s ON s.InsDat = m.InsDat
What you get is an integer column which starts at 1 each day - you can format that to a leading zered string if you need to, or let your presentation layer handle that.
Ident InsDat Val Seq
1 2017-07-05 A 1
2 2017-07-05 A 2
3 2017-07-05 A 3
4 2017-07-05 A 4
5 2017-07-05 A 5
6 2017-07-06 B 1
7 2017-07-06 B 2
8 2017-07-06 B 3
9 2017-07-06 B 4
10 2017-07-06 B 5
11 2017-07-06 B 6
12 2017-07-07 C 1
13 2017-07-07 C 2
14 2017-07-07 C 3
15 2017-07-07 C 4
16 2017-07-08 D 1