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

    Question

  • 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.

    Wednesday, August 28, 2013 10:59 PM

Answers

  • try this,

    with cte
    as
    (
    select [KEY],start_datetime,end_datetime,1 as recodringno from [dbo].[recordings] where [KEY] =1
    union all
    select a.[KEY],a.start_datetime,a.end_datetime
    ,case when DATEDIFF(MINUTE,b.end_datetime,a.end_datetime)>3 then b.recodringno+1 else b.recodringno  end
    
    as recodringno from 
    [dbo].[recordings] a 
    inner join cte b on a.[KEY]=b.[KEY]+1 
    
    )
    select 'recodring'+cast(recodringno as varchar(10)) as recodringno
    ,MIN(start_datetime)start_datetime,MAX(end_datetime) end_datetime from cte group by recodringno


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by jdp12383 Thursday, August 29, 2013 4:51 AM
    Thursday, August 29, 2013 1:17 AM

All replies

  • try this,

    with cte
    as
    (
    select [KEY],start_datetime,end_datetime,1 as recodringno from [dbo].[recordings] where [KEY] =1
    union all
    select a.[KEY],a.start_datetime,a.end_datetime
    ,case when DATEDIFF(MINUTE,b.end_datetime,a.end_datetime)>3 then b.recodringno+1 else b.recodringno  end
    
    as recodringno from 
    [dbo].[recordings] a 
    inner join cte b on a.[KEY]=b.[KEY]+1 
    
    )
    select 'recodring'+cast(recodringno as varchar(10)) as recodringno
    ,MIN(start_datetime)start_datetime,MAX(end_datetime) end_datetime from cte group by recodringno


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by jdp12383 Thursday, August 29, 2013 4:51 AM
    Thursday, August 29, 2013 1:17 AM
  • Awesome, many thanks Sarat Babu, your reply really helped me,

    Can we achieve this without CTE?


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

    Thursday, August 29, 2013 1:38 AM
  • Ofcourse, with a bit more coding work.

    The above query uses Recursive CTE (not just CTE), we can write logic using WHILE or CURSOR to achieve the same.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 29, 2013 2:17 AM