sql per month identity

Question

is it possible to create a per month identity ? basically what I need is

create table
(
id identity primary key
regDate DateTime not null,
regDateNumber int not null
)

and when I insert data I need set the regDateNumber as an identity in the scope of the regDate's month

like this:

id   regDate    regDateNumber
1    1 jan 2008 1
2    3 jan 2008 2
3    7 jan 2008 3
4    1 feb 2008 1
5    1 feb 2008 2
6    1 aug 2008 1
7    1 aug 2008 2
8    1 sep 2008 1
9    1 sep 2008 2

Answer 1

You cannot do it with identity, so you may compute on fly. Something like:

select id, regDate, 
row_number() over(partition by year(regDate), month(regDate) as regDateNumber order by id) 
from t

Answer 2

You could simply add a computed column to your table:

ALTER TABLE dbo.YourTable
  ADD regMonth AS MONTH(regDate) PERSISTED

and be done with it. This will be computed, persisted (e.g. not recomputed on every access, but only when regDate changes), contains the MONTH information for the date, and can even be indexed for speed.

Update: ok, so now I get it - your "identity per month" is a bit unclear and misleading. What you want is to sequentially number the entries for each month, right?

One way to do it is not doing it / not storing it, but using burnall's approach with the ROW_NUMBER() function.

Otherwise, you'd have to do an AFTER INSERT trigger which calculates the new value upon INSERT - but that gets a) messy and b) will cost you quite a bit of performance when you're doing lots of inserts.

My recommendation would be to use burnall's function and let that run e.g. once every hour or so to update those fields. Do not try to figure this out on the fly, upon INSERT - your performance will be awful.

Answer 3

I believe you can achieve this by using a stored procedure:

CREATE PROCEDURE month_indentity
   @myInput  DateTime
AS
SELECT COUNT(*) + 1 FROM mytable
WHERE MONTH(regDate) = MONTH(@myInput)
    AND YEAR(regDate) = YEAR(@myInput)
GO

Answer 4

create table #table (iid int identity (1, 1), dy int, mth varchar(3))

insert into #table (dy, mth)
select 1, 'jan' union all
select 2, 'jan' union all
select 20, 'jan' union all
select 10, 'jan' union all
select 1, 'feb' union all
select 3, 'feb' union all
select 28, 'feb' 

select T.dy, T.mth, 
  (select count(X.iid) from #table X where X.mth = T.mth and X.iid < T.iid) + 1 as month_rank
from #table T
order by T.mth, month_rank

I wouldn't store the value as a calculated field since it can change over time and it violates normalization rules.