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

5 Jan 2006 7:06 PM

BLetts

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 do
this - I'd rather not have to maintain two extra columns just for the index.

5 Jan 2006 7:35 PM

Trey Walpole

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:
Show quote

> 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 do
> this - I'd rather not have to maintain two extra columns just for the index.

5 Jan 2006 8:02 PM

Mark Williams

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

--

Show quote

"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 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 do
> this - I'd rather not have to maintain two extra columns just for the index.

5 Jan 2006 8:30 PM

Mark Williams

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.

Show quote

"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 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 do
> this - I'd rather not have to maintain two extra columns just for the index.

5 Jan 2006 8:53 PM

Steve Kass

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:

Show quote

>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 do
>this - I'd rather not have to maintain two extra columns just for the index.
>
>

5 Jan 2006 9:34 PM

Trey Walpole

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:
Show quote

> 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 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 do this - I'd rather not have to maintain two
>> extra columns just for the index.
>>
>>

5 Jan 2006 10:16 PM

Steve Kass

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:

Show quote

> 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:
>>
>>> 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 do this - I'd rather not have to
>>> maintain two extra columns just for the index.
>>>
>>>

5 Jan 2006 11:35 PM

Erland Sommarskog

Steve Kass (sk***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx