T-SQL to retrieve records after comparing two columns of the same table

Hi,



I have created a database and a table in SQL Server 2005 Express. This table stores webcam recordings. Each record is a recording up to 3 min. I am trying to retrieve one record per continuous recording. If there is gap more than 3 min then it would be considered as a separate recording. Below is the script.

CREATE TABLE [dbo].[recordings](
        [key] [bigint] IDENTITY(1,1) NOT NULL,
        [filename] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [start_datetime] [datetime] NOT NULL,
        [end_datetime] [datetime] NOT NULL,
        [deleted] [bit] NOT NULL CONSTRAINT [DF_recordings_deleted]  DEFAULT ((0)),
 CONSTRAINT [PK_recordings] PRIMARY KEY CLUSTERED 
(
        [key] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f1', '2013-08-26 00:00:00', '2013-08-26 00:03:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f2', '2013-08-26 00:03:01', '2013-08-26 00:06:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f3', '2013-08-26 00:06:01', '2013-08-26 00:09:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f4', '2013-08-26 00:14:00', '2013-08-26 00:17:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f5', '2013-08-26 00:17:01', '2013-08-26 00:20:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f6', '2013-08-26 00:20:01', '2013-08-26 00:23:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f7', '2013-08-26 00:30:00', '2013-08-26 00:33:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f8', '2013-08-26 00:33:01', '2013-08-26 00:36:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f9', '2013-08-26 00:36:01', '2013-08-26 00:39:00', 0);

INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f10', '2013-08-26 00:44:00', '2013-08-26 00:47:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f11', '2013-08-26 00:47:01', '2013-08-26 00:50:00', 0);
INSERT INTO [recordings] ([filename], [start_datetime], [end_datetime], [deleted]) VALUES ('f12', '2013-08-26 00:50:01', '2013-08-26 00:53:00', 0);

The result should be like below,

recording1     2013-08-26 00:00:00     2013-08-26 00:09:00
recording2     2013-08-26 00:14:00     2013-08-26 00:23:00
recording3     2013-08-26 00:30:00     2013-08-26 00:39:00
recording4     2013-08-26 00:44:00     2013-08-26 00:53:00 

I really appreciate your help resolving this.

---

Regards, Jignesh Please vote as helpful if my question/answer help you finding your solution.