如何确保表中插入/更新的日期没有重叠并且没有间隔? [英] How to make sure that dates inserted/updated in a table is not overlapped and contains no gaps?

问题描述

CREATE TABLE MyTable
(
ID int PRIMARY KEY,
Description varchar(max),
ActivationDate datetime,
DeactivationDate datetime,
CreatedOn datetime,
CreatedByUserID int,
Company varchar(50) 
) 

我想确保每个公司的激活和停用日期没有间隔，也没有重叠.

假设公司X的激活日期为2012年1月1日，停用日期为2012年12月31日.我想插入X公司的记录，将其激活日期设为2013年5月5日，停用日期设为2013年5月5日.在这种情况下，它不应该插入，因为它会给X公司造成空白.但是对于Y公司(作为新记录)，它可能就可以了.

我在触发器下面尝试过.它不允许每个公司出现重叠.但这允许差距.请帮助我解决这个问题.

请注意，启用/停用日期可能是过去或将来的日期.例如，如果X公司有2012年的记录(假设2012年1月1日至2012年12月31日)，则用户不仅可以输入2013年的日期，还可以输入2011年的日期. />

I would like to ensure that activation and deactivation dates have no gaps and no overlaps per Company.

Say for example Company X having activation date as 01/01/2012 and deactivation date as 12/31/2012. I would like to insert a record for company X as activation date as 05/05/2013 and deactivation date as 05/05/2013. In this case it should not allow to insert as it creates gaps for the company X. But it could be OK for company Y (as new record for it).

I tried below trigger. It is not allowing the overlaps per company as expected. But it allows gaps. Please help me to resolve that.

Please note that the activation/deactivation dates could be in past or future date. Say for example, if X company has record for the year 2012 (assuming 01/01/2012 to 12/31/2012), user could able to enter date for ther year not only for 2013 but also for the year 2011.

CREATE TRIGGER trDateValidation ON MyTable
INSTEAD OF INSERT, UPDATE
AS

      IF (EXISTS (
            SELECT iOut.ID
            FROM inserted iOut JOIN
                  (SELECT t.Company as Company, MIN(t.activationDate) minAactDate ,  MAX(t.DeactivationDate) maxDeactDate,  DATEADD(DAY, 1, MAX(t.DeactivationDate)) maxDeactDatePlusOne, DATEADD(DAY, -1, MIN(t.ActivationDate)) minActDateMinusOne


                  FROM MyTable t JOIN
                        inserted i
                              ON t.Company = i.Company
                  GROUP BY t.Company)q
                        ON iOut.Company = q.Company
           WHERE  (
            (((iOut.ActivationDate BETWEEN q.minAactDate AND q.maxDeactDate) OR (iOut.DeactivationDate BETWEEN q.minAactDate AND q.maxDeactDate)  ) AND (iOut.ActivationDate <> ISNULL(maxDeactDatePlusOne, iOut.ActivationDate  )))
            OR
            (((iOut.ActivationDate BETWEEN q.minAactDate AND q.maxDeactDate) OR (iOut.DeactivationDate BETWEEN q.minAactDate AND q.maxDeactDate)  ) AND (iOut.DeActivationDate <> ISNULL( minActDateMinusOne,iOut.DeActivationDate) ))
            )

            ))
            BEGIN
                  RAISERROR ('ActivationDate must be greater (1 day) then last DeactivationDate', 16, 1)
                  ROLLBACK TRANSACTION
                  RETURN
            END

      UPDATE MyTable
      SET ID = i.ID,
            Description = i.Description,
            ActivationDate = i.ActivationDate,
            DeactivationDate = i.DeactivationDate,
            CreatedOn = i.CreatedOn,
            CreatedByUserID = i.CreatedByUserID,
            Company = i.Company
      FROM MyTable t JOIN
            inserted i
                  ON t.ID = i.ID

      INSERT INTO MyTable (ID, Description,ActivationDate,DeactivationDate, CreatedOn,CreatedByUserID,Company)
      SELECT inserted.ID, inserted.Description,inserted.ActivationDate,inserted.DeactivationDate,
            inserted.CreatedOn,inserted.CreatedByUserID,inserted.Company
      FROM inserted
      WHERE inserted.ID NOT IN (
            SELECT ID
            FROM MyTable
            )

GO

我认为问题出在第一个条件所在.我尝试了很多方法，但是没有运气.您能帮我解决吗?谢谢.

I think the problem lies with First where condition. I tried many ways but no luck. Could you please help me to resolve it? Thanks.

推荐答案

尝试一下:

Try this:

--On Insert
Declare @act datetime,@dact datetime

Select @act=ActivationDate, @dact=DeactivationDate From Inserted

If Exists(Select * From MyTable Where Company=@cmp And @act<ActivationDate And DateAdd(d,1,@dact)<>ActivationDate)Raiserror('Your Error Message',16,1)
Else If Exists(Select * From MyTable Where Company=@cmp And @act>ActivationDate And DateAdd(d,1,DeactivationDate)<>@act)Raiserror('Your Error Message',16,1)
Else If Exists(Select * From MyTable Where Company=@cmp And @act=ActivationDate Or DeactivationDate=@dact)Raiserror('Your Error Message',16,1)
Else --Write for insert code or commit & return
--Rollback

--On Update
If Exists(Select * From MyTable Where Company=@cmp And (@act<>ActivationDate Or @act<>DeactivationDate))Raiserror('Your Error Message',16,1)
Else --Write your code

这篇关于如何确保表中插入/更新的日期没有重叠并且没有间隔?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！