Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to maintain the dates of a history table aligned with no gaps?

Avatar of marcodelphi2010
marcodelphi2010Flag for Brazil asked on
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005
17 Comments1 Solution418 ViewsLast Modified:
Hi,

I have to mantain a history table, where given a new entry or a record is updated, the table is recreated or realigned (only for records with the same ID). There should be no gaps between the records. Each record has a StartDate and EndDate. Below is the schema of the table:

CREATE TABLE [dbo].[History] (
  [HistoryID] bigint IDENTITY(1, 1) NOT NULL,
  [ID] int NOT NULL,
  [StartDate] datetime NOT NULL,
  [EndDate] datetime NULL,
  [Status] tinyint NULL,
  CONSTRAINT [History_pk] PRIMARY KEY CLUSTERED ([HistoryID]),
  CONSTRAINT [History_uq] UNIQUE ([ID], [StartDate])
)
ON [PRIMARY]
GO

Here is a sample data with the dates aligned:

The DateFormat is dd/mm/yyyy

HistoryID      ID      StartDate      EndDate      Status
1      1      01/01/2010      10/01/2010      1
2      1      11/01/2010      15/01/2010      2
3      1      16/01/2010      20/01/2010      3

Rules:
1.Get rid of any rows that are entirely within the range of the new row being inserted or updated.
example:

INSERT INTO History(ID,StartDate,EndDate,Status)
VALUES(1,'01/01/2010','17/01/2010',2)

The end ResultSet should be as follows:
      
HistoryID      ID      StartDate      EndDate      Status
6      1      01/01/2010      17/01/2010      2
3      1      18/01/2010      20/01/2010      3

Note that the record with HistoryID=3 have the StartDate increased by 1 because of the EndDate of the previous record(6) are greater than the StartDate of the record below(3), which range was from 16/01/2010 to 20/01/2010.

The same behavior should apply if the user makes an update on any record on any field (StartDate,EndDate,Status).

Following the previous resultset, lets INSERT more data into the table.

HistoryID      ID      StartDate      EndDate      Status
6      1      01/01/2010      17/01/2010      2
3      1      18/01/2010      20/01/2010      3

INSERT INTO History(ID,StartDate,EndDate,Status)
VALUES(1,'05/01/2010','10/01/2010',1)

ResultSet after the insert:

HistoryID      ID      StartDate      EndDate      Status
6      1      01/01/2010      04/01/2010      2
7      1      05/01/2010      10/01/2010      1
8      1      11/01/2010      17/01/2010      2
3      1      18/01/2010      20/01/2010      3

Now the user changes the record with HistoryID=3, with the folowing UPDATE:
UPDATE
  SET StartDate='17/01/2010', EndDate='19/01/2010', Status=1
WHERE HistoryID=3

Resultset:

HistoryID      ID      StartDate      EndDate      Status
6      1      01/01/2010      04/01/2010      2
7      1      05/01/2010      10/01/2010      1
8      1      11/01/2010      16/01/2010      2
3      1      17/01/2010      19/01/2010      1
9      1      20/01/2010      20/01/2010      3


Another INSERT:

INSERT INTO History(ID,StartDate,EndDate,Status)
VALUES(1,'01/01/2010','18/01/2010',1)

Resultset:

HistoryID      ID      StartDate      EndDate      Status
10      1      01/01/2010      19/01/2010      1
9      1      20/01/2010      20/01/2010      3

Note that the records 7,8,3 were deleted. The new added record takes the EndDate modified to 19/01/2010 because of the EndDate of record no 3 had the same status, that means, that there is no need to keep or modify the record no 3 either, just take the EndDate of it and put it on the newly inserted record.

I dont know how to achive this result together with the behavior exposed here. I dont know if its better to do it as a trigger or to write a procedure for inserting records and do all the job in it.

That´s it. If there is any doubt about the rules, please, let me know.

Thanks!

Best Regards,

Marco André
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5anFlag of Germany imageSenior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answers