Merging row records where the end date of previous record is equal to start date of next record.

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 22, 2017 at 5:42 am

  #324235

  Hi guys,

  I'm writing a script on SQL and my aim is to merge row records which have the same location name and record group (i.e. IDHAAndD).

  The condition for merge is where the end date of the previous record is equal to the start date of the next record, then merge the two dates so that it has only one record,
   which will now have the start date of the first record and the last date of the next record.

  There are few scenarios that i need to highlight, i.e. where the client moves to another location between the records needs to be counted as 3 different location stays.

  Here is a script to create some sample data. With 2 clients records.

  Create table #TestTable

  (
  IDHAAndD varchar(50),
  IDClient    varchar(50),
  IDStay varchar(50),
  LocationName Varchar(50),
  StartDate    Varchar(50),
  EndDate    Varchar(50)
  )
  ---sample client 1
  InSert Into #TestTable (IDHAAndD,IDClient,IDStay ,LocationName ,StartDate    ,EndDate)
  VALUES
   ('1111111','1000','1111497','7 11 Dell','201308311002','201309031430'),
  ('1111111','1000','1111499','7 11 Dell','201307292343','201308311002'),
  ('1111111','1000','1111458','7 11 Dell','201307261750','201307292343'),
  ('1111111','1000','1111467','7 11 Dell','201307241223','201307241430'),
  ('1111111','1000','1111460','7 11 Elgar','201307242340','201307261750'),
  ('1111111','1000','1111465','7 11 Elgar','201307241430','201307242340'),
  ('1111111','1000','1111466','7 11 Elgar','201307231727','201307241223'),
  ('1111111','1000','1111468','7 11 Wing','201307211814','201307231727'),
  ('2222222','1000','1111457','7 11 Dell','201403021019','201404071457'),
  ('3333333','1000','1111487','7 11 Andy','201310242222','201310251611'),
  ('3333333','1000','1111478','7 11 Clear','201312081114','201312091348'),
  ('3333333','1000','1111461','7 11 Dell','201402041800','201402271300'),
  ('3333333','1000','1111463','7 11 Dell','201312111055','201402041800'),
  ('3333333','1000','1111464','7 11 Dell','201312111052','201312111052'),
  ('3333333','1000','1111479','7 11 Dell','201312091348','201312111050'),
  ('3333333','1000','1111480','7 11 Dell','201310251800','201312081114'),
  ('3333333','1000','1111485','7 11 Dell','201310251611','201310251800'),
  ('3333333','1000','1111488','7 11 Dell','201309041641','201310091807'),
  ('3333333','1000','1111462','XX 7 11 Fen','201312111052','201312111055'),
  ('3333333','1000','1111477','XX 7 11 Fen','201312111050','201312111052'),
  ('3333333','1000','1111486','XX 7 11 Fen','201310091807','201310242222');
  ---sample client 2
  InSert Into #TestTable (IDHAAndD,IDClient,IDStay ,LocationName ,StartDate    ,EndDate)
  VALUES
    ('1200001','1011','2222697','XX 7 11 Arr','201308311002','201309031430'),
  ('1200001','1011','2222698','XX 7 11 Arr','201307292343','201308311002'),
  ('1200001','1011','2222699','XX 7 11 Arr','201307261750','201307292343'),
  ('1200003','1011','2222700','7 11 Wing','201307241223','201307241430'),
  ('1200003','1011','2222701','7 11 Black','201307242340','201307261750'),
  ('1200002','1011','2222702','7 11 Black','201307241430','201307242340'),
  ('1200004','1011','2222703','7 11 Black','201307231727','201307241223'),
  ('1200005','1011','2222704','7 11 Black','201307211814','201307231727'),
  ('1200007','1011','2222705','7 11 Black','201403021019','201404071457'),
  ('1200006','1011','2222706','7 11 Andy','201310242222','201310251611'),
  ('1200006','1011','2222707','7 11 Clear','201312081114','201312091348'),
  ('1200006','1011','2222708','7 11 Andy','201402041800','201402271300'),
  ('1200006','1011','2222709','7 11 Clear','201312111055','201402041800'),
  ('1200006','1011','2222710','XX 7 11 Fenny','201312111052','201312111052'),
  ('1200006','1011','2222711','7 11 Andy','201312091348','201312111050'),
  ('1200006','1011','2222712','7 11 Andy','201310251800','201312081114'),
  ('1200006','1011','2222713','7 11 Clear','201310251611','201310251800'),
  ('1200006','1011','2222714','7 11 MG L','201309041641','201310091807'),
  ('1200006','1011','2222715','7 11 Clear','201312111052','201312111055');

  Please let me know if you have any questions.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 22, 2017 at 6:30 am

  #1947728

  me thinks it would be useful if you could please post your expected results.....will save any misunderstanding on your explanation.
  Also....are you really storing dates as varchar?
  Also....what defines the "previous" record...is your sort based on IDHAAndID and the StartDate?

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 22, 2017 at 6:54 am

  #1947732

  See if this helps

  WITH C1 AS (
SELECT IDHAAndD,IDClient,IDStay,LocationName,StartDate,EndDate,
 CASE WHEN StartDate = LAG(EndDate) OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY StartDate, EndDate) THEN 0 ELSE 1 END AS isstart
FROM #TestTable
),
C2 AS (
SELECT IDHAAndD,IDClient,IDStay,LocationName,StartDate,EndDate,isstart,
   SUM(isstart) OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY StartDate, EndDate ROWS UNBOUNDED PRECEDING) AS grp
FROM C1)
SELECT IDHAAndD,IDClient,
   MAX(CASE WHEN isstart = 1 THEN IDStay END) AS RecordID,
   LocationName,
  MIN(StartDate) AS StartDate,
  MAX(EndDate) AS EndDate
FROM C2
GROUP BY IDHAAndD,IDClient,LocationName,grp
ORDER BY IDHAAndD,IDClient,StartDate;


  Changed to produce correct results, but still requires 2012+

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 22, 2017 at 10:30 am

  #1947804

  Hi J Livingston SQL,  thanks for your response. the answer to your questions are 
  -I will post expected results shorlty
  -No i don't store dates as varcahr: but for the ease of the sample i have converted the datetime to varchar
  -Yes, my sort is based on DHAAndID and the StartDate: the previous record is the same client in same locationName and  same DHAAndID

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 22, 2017 at 10:34 am

  #1947805

  Hi Mark Cowne,
  Thanks for the response, i have reviewed your script out but the LAG syntax does not work with my version of SQL - 2008 R2.

  Is there an alternate syntax in 2008 R2 version?

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 22, 2017 at 10:38 am

  #1947807

  akinwandeb - Thursday, June 22, 2017 10:34 AM

  Hi Mark Cowne,
  Thanks for the response, i have reviewed your script out but the LAG syntax does not work with my version of SQL - 2008 R2.

  Is there an alternate syntax in 2008 R2 version?

  you posted in SQL 2016 forum.....so thats why Mark gave you a possible solution using LAG.
  Suggest you repost with datetime datatype (NOT varchar...it doesnt make it easier) for your set up script 
  and your expected results as you have promised

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 22, 2017 at 11:08 am

  #1947812

  Hi J Livingston,

  Here is the revised script with the updated datetime values. he script is giving me error for the date conversion " conversion failed when converting date and /or time from character string" . - Please help with this

  Create table #TestTable

  (
  IDHAAndD varchar(50),
  IDClient    varchar(50),
  IDStay varchar(50),
  LocationName Varchar(50),
  StartDate    DateTime,
  EndDate    DateTime
  )
  ---sample client 1
  InSert Into #TestTable (IDHAAndD,IDClient,IDStay ,LocationName ,StartDate    ,EndDate)
  VALUES
    ('1111111','1000','1111497','7 11 Dell','31/08/2013 10:02','03/09/2013 14:30'),
  ('1111111','1000','1111499','7 11 Dell','29/07/2013 23:43','31/08/2013 10:02'),
  ('1111111','1000','1111458','7 11 Dell','26/07/2013 17:50','29/07/2013 23:43'),
  ('1111111','1000','1111467','7 11 Dell','24/07/2013 12:23','24/07/2013 14:30'),
  ('1111111','1000','1111460','7 11 Elgar','24/07/2013 23:40','26/07/2013 17:50'),
  ('1111111','1000','1111465','7 11 Elgar','24/07/2013 14:30','24/07/2013 23:40'),
  ('1111111','1000','1111466','7 11 Elgar','23/07/2013 17:27','24/07/2013 12:23'),
  ('1111111','1000','1111468','7 11 Wing','21/07/2013 18:14','23/07/2013 17:27'),
  ('2222222','1000','1111457','7 11 Dell','02/03/2014 10:19','07/04/2014 14:57'),
  ('3333333','1000','1111487','7 11 Andy','24/10/2013 22:22','25/10/2013 16:11'),
  ('3333333','1000','1111478','7 11 Clear','08/12/2013 11:14','09/12/2013 13:48'),
  ('3333333','1000','1111461','7 11 Dell','04/02/2014 18:00','27/02/2014 13:00'),
  ('3333333','1000','1111463','7 11 Dell','11/12/2013 10:55','04/02/2014 18:00'),
  ('3333333','1000','1111464','7 11 Dell','11/12/2013 10:52','11/12/2013 10:52'),
  ('3333333','1000','1111479','7 11 Dell','09/12/2013 13:48','11/12/2013 10:50'),
  ('3333333','1000','1111480','7 11 Dell','25/10/2013 18:00','08/12/2013 11:14'),
  ('3333333','1000','1111485','7 11 Dell','25/10/2013 16:11','25/10/2013 18:00'),
  ('3333333','1000','1111488','7 11 Dell','04/09/2013 16:41','09/10/2013 18:07'),
  ('3333333','1000','1111462','XX 7 11 Fen','11/12/2013 10:52','11/12/2013 10:55'),
  ('3333333','1000','1111477','XX 7 11 Fen','11/12/2013 10:50','11/12/2013 10:52'),
  ('3333333','1000','1111486','XX 7 11 Fen','09/10/2013 18:07','24/10/2013 22:22');
  ---sample client 2
  InSert Into #TestTable (IDHAAndD,IDClient,IDStay ,LocationName ,StartDate    ,EndDate)
  VALUES
    ('1200001','1011','2222697','XX 7 11 Arr','31/08/2013 10:02','03/09/2013 14:30'),
  ('1200001','1011','2222698','XX 7 11 Arr','29/07/2013 23:43','31/08/2013 10:02'),
  ('1200001','1011','2222699','XX 7 11 Arr','26/07/2013 17:50','29/07/2013 23:43'),
  ('1200003','1011','2222700','7 11 Wing','24/07/2013 12:23','24/07/2013 14:30'),
  ('1200003','1011','2222701','7 11 Black','24/07/2013 23:40','26/07/2013 17:50'),
  ('1200002','1011','2222702','7 11 Black','24/07/2013 14:30','24/07/2013 23:40'),
  ('1200004','1011','2222703','7 11 Black','23/07/2013 17:27','24/07/2013 12:23'),
  ('1200005','1011','2222704','7 11 Black','21/07/2013 18:14','23/07/2013 17:27'),
  ('1200007','1011','2222705','7 11 Black','02/03/2014 10:19','07/04/2014 14:57'),
  ('1200006','1011','2222706','7 11 Andy','24/10/2013 22:22','25/10/2013 16:11'),
  ('1200006','1011','2222707','7 11 Clear','08/12/2013 11:14','09/12/2013 13:48'),
  ('1200006','1011','2222708','7 11 Andy','04/02/2014 18:00','27/02/2014 13:00'),
  ('1200006','1011','2222709','7 11 Clear','11/12/2013 10:55','04/02/2014 18:00'),
  ('1200006','1011','2222710','XX 7 11 Fenny','11/12/2013 10:52','11/12/2013 10:52'),
  ('1200006','1011','2222711','7 11 Andy','09/12/2013 13:48','11/12/2013 10:50'),
  ('1200006','1011','2222712','7 11 Andy','25/10/2013 18:00','08/12/2013 11:14'),
  ('1200006','1011','2222713','7 11 Clear','25/10/2013 16:11','25/10/2013 18:00'),
  ('1200006','1011','2222714','7 11 MG L','04/09/2013 16:41','09/10/2013 18:07'),
  ('1200006','1011','2222715','7 11 Clear','11/12/2013 10:52','11/12/2013 10:55');

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 22, 2017 at 11:29 am

  #1947817

  like this ??  ...still need expected results

  SET DATEFORMAT DMY;
CREATE TABLE #TestTable
(IDHAAndD  VARCHAR(50),
IDClient  VARCHAR(50),
IDStay   VARCHAR(50),
LocationName VARCHAR(50),
StartDate  DATETIME,
EndDate  DATETIME
);
---sample client 1
INSERT INTO #TestTable (IDHAAndD,IDClient,IDStay ,LocationName ,StartDate ,EndDate)
VALUES
('1111111','1000','1111497','7 11 Dell','31/08/2013 10:02','03/09/2013 14:30'),
('1111111','1000','1111499','7 11 Dell','29/07/2013 23:43','31/08/2013 10:02'),
('1111111','1000','1111458','7 11 Dell','26/07/2013 17:50','29/07/2013 23:43'),
('1111111','1000','1111467','7 11 Dell','24/07/2013 12:23','24/07/2013 14:30'),
('1111111','1000','1111460','7 11 Elgar','24/07/2013 23:40','26/07/2013 17:50'),
('1111111','1000','1111465','7 11 Elgar','24/07/2013 14:30','24/07/2013 23:40'),
('1111111','1000','1111466','7 11 Elgar','23/07/2013 17:27','24/07/2013 12:23'),
('1111111','1000','1111468','7 11 Wing','21/07/2013 18:14','23/07/2013 17:27'),
('2222222','1000','1111457','7 11 Dell','02/03/2014 10:19','07/04/2014 14:57'),
('3333333','1000','1111487','7 11 Andy','24/10/2013 22:22','25/10/2013 16:11'),
('3333333','1000','1111478','7 11 Clear','08/12/2013 11:14','09/12/2013 13:48'),
('3333333','1000','1111461','7 11 Dell','04/02/2014 18:00','27/02/2014 13:00'),
('3333333','1000','1111463','7 11 Dell','11/12/2013 10:55','04/02/2014 18:00'),
('3333333','1000','1111464','7 11 Dell','11/12/2013 10:52','11/12/2013 10:52'),
('3333333','1000','1111479','7 11 Dell','09/12/2013 13:48','11/12/2013 10:50'),
('3333333','1000','1111480','7 11 Dell','25/10/2013 18:00','08/12/2013 11:14'),
('3333333','1000','1111485','7 11 Dell','25/10/2013 16:11','25/10/2013 18:00'),
('3333333','1000','1111488','7 11 Dell','04/09/2013 16:41','09/10/2013 18:07'),
('3333333','1000','1111462','XX 7 11 Fen','11/12/2013 10:52','11/12/2013 10:55'),
('3333333','1000','1111477','XX 7 11 Fen','11/12/2013 10:50','11/12/2013 10:52'),
('3333333','1000','1111486','XX 7 11 Fen','09/10/2013 18:07','24/10/2013 22:22'),
---sample client 2
('1200001','1011','2222697','XX 7 11 Arr','31/08/2013 10:02','03/09/2013 14:30'),
('1200001','1011','2222698','XX 7 11 Arr','29/07/2013 23:43','31/08/2013 10:02'),
('1200001','1011','2222699','XX 7 11 Arr','26/07/2013 17:50','29/07/2013 23:43'),
('1200003','1011','2222700','7 11 Wing','24/07/2013 12:23','24/07/2013 14:30'),
('1200003','1011','2222701','7 11 Black','24/07/2013 23:40','26/07/2013 17:50'),
('1200002','1011','2222702','7 11 Black','24/07/2013 14:30','24/07/2013 23:40'),
('1200004','1011','2222703','7 11 Black','23/07/2013 17:27','24/07/2013 12:23'),
('1200005','1011','2222704','7 11 Black','21/07/2013 18:14','23/07/2013 17:27'),
('1200007','1011','2222705','7 11 Black','02/03/2014 10:19','07/04/2014 14:57'),
('1200006','1011','2222706','7 11 Andy','24/10/2013 22:22','25/10/2013 16:11'),
('1200006','1011','2222707','7 11 Clear','08/12/2013 11:14','09/12/2013 13:48'),
('1200006','1011','2222708','7 11 Andy','04/02/2014 18:00','27/02/2014 13:00'),
('1200006','1011','2222709','7 11 Clear','11/12/2013 10:55','04/02/2014 18:00'),
('1200006','1011','2222710','XX 7 11 Fenny','11/12/2013 10:52','11/12/2013 10:52'),
('1200006','1011','2222711','7 11 Andy','09/12/2013 13:48','11/12/2013 10:50'),
('1200006','1011','2222712','7 11 Andy','25/10/2013 18:00','08/12/2013 11:14'),
('1200006','1011','2222713','7 11 Clear','25/10/2013 16:11','25/10/2013 18:00'),
('1200006','1011','2222714','7 11 MG L','04/09/2013 16:41','09/10/2013 18:07'),
('1200006','1011','2222715','7 11 Clear','11/12/2013 10:52','11/12/2013 10:55');

  select * from #TestTable

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 22, 2017 at 11:33 am

  #1947820

  Here is what my expected results are meant to look like for both sample clients
  1

  IDHAAndD	IDClient	RecordID	LocationName	StartDate	EndDate
  1111111	1000	1111468	7 11 Wing	21/07/2013 18:14	23/07/2013 17:27
  1111111	1000	1111466	7 11 Elgar	23/07/2013 17:27	24/07/2013 12:23
  1111111	1000	1111467	7 11 Dell	24/07/2013 12:23	24/07/2013 14:30
  1111111	1000	1111465	7 11 Elgar	24/07/2013 14:30	26/07/2013 17:50
  1111111	1000	1111458	7 11 Dell	26/07/2013 17:50	03/09/2013 14:30
  3333333	1000	1111488	7 11 Dell	04/09/2013 16:41	09/10/2013 18:07
  3333333	1000	1111486	XX 7 11 Fen	09/10/2013 18:07	24/10/2013 22:22
  3333333	1000	1111487	7 11 Andy	24/10/2013 22:22	25/10/2013 16:11
  3333333	1000	1111485	7 11 Dell	25/10/2013 16:11	08/12/2013 11:14
  3333333	1000	1111478	7 11 Clear	08/12/2013 11:14	09/12/2013 13:48
  3333333	1000	1111479	7 11 Dell	09/12/2013 13:48	11/12/2013 10:50
  3333333	1000	1111477	XX 7 11 Fen	11/12/2013 10:50	11/12/2013 10:52
  3333333	1000	1111464	7 11 Dell	11/12/2013 10:52	11/12/2013 10:52
  3333333	1000	1111462	XX 7 11 Fen	11/12/2013 10:52	11/12/2013 10:55
  3333333	1000	1111463	7 11 Dell	11/12/2013 10:55	27/02/2014 13:00
  2222222	1000	1111457	7 11 Dell	02/03/2014 10:19	07/04/2014 14:57

  IDHAAndD	IDClient	RecordID	LocationName	StartDate	EndDate
  1200005	1011	2222704	7 11 Black	21/07/2013 18:14	23/07/2013 17:27
  1200004	1011	2222703	7 11 Black	23/07/2013 17:27	24/07/2013 12:23
  1200003	1011	2222700	7 11 Wing	24/07/2013 12:23	24/07/2013 14:30
  1200002	1011	2222702	7 11 Black	24/07/2013 14:30	24/07/2013 23:40
  1200003	1011	2222701	7 11 Black	24/07/2013 23:40	26/07/2013 17:50
  1200001	1011	2222699	XX 7 11 Arr	26/07/2013 17:50	03/09/2013 14:30
  1200006	1011	2222714	7 11 MG L	04/09/2013 16:41	09/10/2013 18:07
  1200006	1011	2222706	7 11 Andy	24/10/2013 22:22	25/10/2013 16:11
  1200006	1011	2222713	7 11 Clear	25/10/2013 16:11	25/10/2013 18:00
  1200006	1011	2222712	7 11 Andy	25/10/2013 18:00	08/12/2013 11:14
  1200006	1011	2222707	7 11 Clear	08/12/2013 11:14	09/12/2013 13:48
  1200006	1011	2222711	7 11 Andy	09/12/2013 13:48	11/12/2013 10:50
  1200006	1011	2222710	XX 7 11 Fenny	11/12/2013 10:52	11/12/2013 10:52
  1200006	1011	2222715	7 11 Clear	11/12/2013 10:52	04/02/2014 18:00
  1200006	1011	2222708	7 11 Andy	04/02/2014 18:00	27/02/2014 13:00
  1200007	1011	2222705	7 11 Black	02/03/2014 10:19	07/04/2014 14:57

  Thanks

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  June 23, 2017 at 3:35 am

  #1947903

  Here's a 2008 version

  WITH Starts AS (
SELECT IDClient,IDHAAndD,LocationName,StartDate,IDStay,
   ROW_NUMBER() OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY StartDate) AS rn
FROM #testtable t
WHERE NOT EXISTS(SELECT * FROM #testtable t2
      WHERE t2.IDClient=t.IDClient
                  AND t2.IDHAAndD=t.IDHAAndD
                    AND t2.LocationName=t.LocationName
                    AND t2.IDStay<>t.IDStay
                    AND t2.EndDate = t.StartDate)
),
Ends AS (
SELECT IDClient,IDHAAndD,LocationName,EndDate,
   ROW_NUMBER() OVER(PARTITION BY IDClient,IDHAAndD,LocationName ORDER BY EndDate) AS rn
FROM #testtable t
WHERE NOT EXISTS(SELECT * FROM #testtable t2
      WHERE t2.IDClient=t.IDClient
                  AND t2.IDHAAndD=t.IDHAAndD
                    AND t2.LocationName=t.LocationName
                    AND t2.IDStay<>t.IDStay
                    AND t2.StartDate = t.EndDate)
)
SELECT s.IDHAAndD,s.IDClient,s.IDStay AS RecordID,s.LocationName, s.StartDate, e.EndDate
FROM Starts s
INNER JOIN Ends e ON e.IDClient = s.IDClient AND e.IDHAAndD = s.IDHAAndD AND e.LocationName = s.LocationName AND e.rn = s.rn
ORDER BY IDHAAndD,IDClient,StartDate;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• akinwandeb

  SSC Eights!

  Points: 893

  More actions

  June 23, 2017 at 4:43 am

  #1947911

  Mark Cowne
  

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  June 23, 2017 at 5:09 pm

  #1948025

  Mark Cowne - Thursday, June 22, 2017 6:54 AM

  See if this helps
  You prevent gaps in a date sequence with DDL. The best way is Alexander Kuznetsov's contiguous dates constraint idiom.
  
  CREATE TABLE Events
  (event_id CHAR(10) NOT NULL,
  previous_event_end_date DATE NOT NULL
  CONSTRAINT Chained_Dates
  REFERENCES Events (event_end_date),
  event_start_date DATE NOT NULL,
  event_end_date DATE UNIQUE, -- null means event in progress
  PRIMARY KEY (event_id, event_start_date),
  CONSTRAINT Event_Order_Valid
  CHECK (event_start_date <= event_end_date),
  CONSTRAINT Chained_Dates
  CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
  -- CHECK (previous_event_end_date + INTERVAL '01' DAYS) = event_start_date)
  );

  -- disable the Chained_Dates constraint
  ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;

  -- insert a starter row
  INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
  VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');

  -- enable the constraint in the table
  ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;

  -- this works
  INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
  VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

  -- this fails
  INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
  VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');
  [

  Please post DDL and follow ANSI/ISO standards when asking for help.