Datetime constraint: is there a better way to do this?

I have a table that contains a datetime field. It's meant to keep track of
monthly equipment inspections.
There can be only one inspection per month, and I need to keep track of the
day it happens. Currently I have one datetime field that keeps track of the
date the inspection happened.
The trick is the constraint that there's only one inspection a month. I know
I could put a "Year" and "Month" column in the table and put a unique index
on those two fields. However, I was wondering if there was a better way to d
o
this - I'd rather not have to maintain two extra columns just for the index.you could make them computed columns and still use a unique constraint
then you wouldn't have to write code to update them when the date is
added [or changed if entered wrong at first, etc.]
e.g. [minimal ddl]
create table inspections (
insp_date datetime not null,
insp_year as year(insp_date),
insp_month as month(insp_date),
unique (insp_year, insp_month)
)
BLetts wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||You could enforce this constaint through the use of an INSTEAD OF trigger
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(DATEDIFF(mm, e.InspectionDate, i.InspectionDate))
FROM EquipmentInspection e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
Keep in mind that this will allow an inspection to be inserted on February
1, 2006 , even if there is another inspection scheduled on January 30, 2006.
If you want it to be more like a real month (that is, 30 days), try
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(DATEDIFF(dd, e.InspectionDate, i.InspectionDate))
FROM EquipmentInspection e, inserted i
IF (@.timediff < 30)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
"BLetts" wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||Sorry, my trigger code requires a little modification; need to throw in the
ABS function to get the absolute value of the time-difference.
CREATE TRIGGER checkInspectionDate ON EquipmentInspections
FOR INSERT, UPDATE
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(ABS(DATEDIFF(mm, e.InspectionDate, i.InspectionDate))
)
FROM EquipmentInspection e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR ('Only a single inspection can occur in each month.', 16, 1)
ROLLBACK TRANSACTION
END
I tested the following trigger on the employee table in the pubs database...
CREATE TRIGGER checkHireDate ON employee
FOR INSERT
AS
DECLARE @.timediff int
SELECT @.timediff = MIN(ABS(DATEDIFF(mm, e.hire_date, i.hire_date)))
FROM employee e, inserted i
IF (@.timediff = 0)
BEGIN
RAISERROR('Cannot hire two people in the same month',16,1)
ROLLBACK TRANSACTION
END
--This will violate the constraint in the trigger
INSERT INTO employee VALUES
('MCD77999M','Mark','O','Williams',1,10,
'0736','1992-08-30')
If you posted to this forum through TechNet, and you found my answers
helpful, please mark them as answers.
"BLetts" wrote:
> I have a table that contains a datetime field. It's meant to keep track of
> monthly equipment inspections.
> There can be only one inspection per month, and I need to keep track of th
e
> day it happens. Currently I have one datetime field that keeps track of th
e
> date the inspection happened.
> The trick is the constraint that there's only one inspection a month. I kn
ow
> I could put a "Year" and "Month" column in the table and put a unique inde
x
> on those two fields. However, I was wondering if there was a better way to
do
> this - I'd rather not have to maintain two extra columns just for the index.[/colo
r]|||Here's a repro that does what Trey suggests, but using a datetime
to keep track of the month. The ISNULL is so the engine knows
that the month column is not null:
create table inspections (
item int not null, -- references a table of inspectables
dt datetime not null default getdate(),
dt_month as isnull(dateadd(month,datediff(month,0,dt
),0),0),
primary key(item,dt_month)
)
go
insert into inspections(item) values(101)
insert into inspections(item) values(102)
go
insert into inspections(item) values(102)
go
select * from inspections
go
drop table inspections
Steve Kass
Drew University
BLetts wrote:

>I have a table that contains a datetime field. It's meant to keep track of
>monthly equipment inspections.
>There can be only one inspection per month, and I need to keep track of the
>day it happens. Currently I have one datetime field that keeps track of the
>date the inspection happened.
>The trick is the constraint that there's only one inspection a month. I kno
w
>I could put a "Year" and "Month" column in the table and put a unique index
>on those two fields. However, I was wondering if there was a better way to
do
>this - I'd rather not have to maintain two extra columns just for the index
.
>|||actually, i tried that first, but got this error:
Server: Msg 1933, Level 16, State 1, Line 1
Cannot create index because the key column 'dt_month' is
non-deterministic or imprecise.
which is odd to me, since the docs state that dateadd and datediff are
deterministic.
Steve Kass wrote:
> Here's a repro that does what Trey suggests, but using a datetime
> to keep track of the month. The ISNULL is so the engine knows
> that the month column is not null:
> create table inspections (
> item int not null, -- references a table of inspectables
> dt datetime not null default getdate(),
> dt_month as isnull(dateadd(month,datediff(month,0,dt
),0),0),
> primary key(item,dt_month)
> )
> go
> insert into inspections(item) values(101)
> insert into inspections(item) values(102)
> go
> insert into inspections(item) values(102)
> go
> select * from inspections
> go
> drop table inspections
> Steve Kass
> Drew University
> BLetts wrote:
>|||Sorry. I only tested this on 2005, where it works. There have
been some changes in how determinism is determined... On 2000, I
guess this solution just won't work. :( On 2005, the output is this:
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__inspections__7C6F7215'. Cannot
insert duplicate key in object 'dbo.inspections'.
The statement has been terminated.
item dt
dt_month
-- ---
---
101 2006-01-05 17:16:28.780
2006-01-01 00:00:00.000
102 2006-01-05 17:16:28.797
2006-01-01 00:00:00.000
SK
Trey Walpole wrote:
> actually, i tried that first, but got this error:
> Server: Msg 1933, Level 16, State 1, Line 1
> Cannot create index because the key column 'dt_month' is
> non-deterministic or imprecise.
> which is odd to me, since the docs state that dateadd and datediff are
> deterministic.
>
> Steve Kass wrote:
>|||Steve Kass (skass@.drew.edu) writes:
> Here's a repro that does what Trey suggests, but using a datetime
> to keep track of the month. The ISNULL is so the engine knows
> that the month column is not null:
And here is a variation of that repro that works on SQL 2000:
create table inspections (
item int not null, -- references a table of inspectables
dt datetime not null default getdate(),
dt_month as isnull(convert(char(6), dt, 112), '')
primary key(item,dt_month)
)
go
insert into inspections(item) values(101)
insert into inspections(item) values(102)
go
insert into inspections(item) values(102)
go
select * from inspections
go
drop table inspections
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx