Select one row per specific time

Question

I have a table that looks like this:

ID  UserID  DateTime             TypeID

1     1     1/1/2010 10:00:00      1
2     2     1/1/2010 10:01:50      1
3     1     1/1/2010 10:02:50      1
4     1     1/1/2010 10:03:50      1
5     1     1/1/2010 11:00:00      1
6     2     1/1/2010 11:00:50      1

I need to query all users where their typeID is 1, but have only one row per 15 mins

For example, the result should be:

1     1     1/1/2010 10:00:00      1
2     2     1/1/2010 10:01:50      1
5     1     1/1/2010 11:00:00      1
6     2     1/1/2010 11:00:50      1

IDs 3 & 4 are not shown because 15 min haven't been passed since the last record for the specific userID.

IDs 1 & 5 are shown because 15 minutes has been passed for this specific userID Same as for IDs 2 & 6.

How can I do it?

Thanks

Answer 1

Try this:

select * from 
(
      select ID, UserID, 
      Max(DateTime) as UpperBound, 
      Min(DateTime) as LowerBound, 
      TypeID 
      from the_table
      where TypeID=1
      group by ID,UserID,TypeID
) t 
where datediff(mi,LowerBound,UpperBound)>=15

EDIT: SINCE MY ABOVE ATTEMPT WAS WRONG, I'm adding one more approach using a Sql table-valued Function that does not require recursion, since, understandable, it's a big concern.

Step 1: Create a table-type as follows (LoginDate is the DateTime column in Shay's example - DateTime name conflicts with a SQL data type and I think it's wise to avoid these conflicts)

CREATE TYPE [dbo].[TVP] AS TABLE(
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [LoginDate] [datetime] NOT NULL,
    [TypeID] [int] NOT NULL
)
GO

Step 2: Create the following Function:

CREATE FUNCTION [dbo].[fnGetLoginFreq] 
(
    -- notice: TVP is the type (declared above)
    @TVP TVP readonly
)
RETURNS 
@Table_Var TABLE 
(
    -- This will be our result set
    ID int, 
    UserId int,
    LoginTime datetime,
    TypeID int,
    RowNumber int
)
AS
BEGIN
    --We will insert records in this table as we go through the rows in the
    --table passed in as parameter and decide that we should add an entry because
    --15' had elapsed between logins 
    DECLARE @temp  table
    (
        ID int,
        UserId int, 
        LoginTime datetime,
        TypeID int
    )
    -- seems silly, but is not because we need to add a row_number column to help
    -- in our iteration and table-valued paramters cannot be modified inside the function
    insert into @Table_var
    select ID,UserID,Logindate,TypeID,row_number() OVER(ORDER BY UserID,LoginDate) AS [RowNumber] 
    from @TVP order by UserID asc,LoginDate desc

    declare @Index int,@End int,@CurrentLoginTime datetime, @NextLoginTime datetime, @CurrentUserID int , @NextUserID int

    select @Index=1,@End=count(*) from @Table_var

    while(@Index<=@End)
    begin        
            select @CurrentLoginTime=LoginTime,@CurrentUserID=UserID from @Table_var where RowNumber=@Index
            select @NextLoginTime=LoginTime,@NextUserID=UserID from @Table_var where RowNumber=(@Index+1)

            if(@CurrentUserID=@NextUserID)
            begin
                if( abs(DateDiff(mi,@CurrentLoginTime,@NextLoginTime))>=15)
                begin   
                    insert into @temp
                    select ID,UserID,LoginTime,TypeID
                    from @Table_var
                    where RowNumber=@Index
                end     
            END
            else 
            bEGIN
                    insert into @temp
                    select ID,UserID,LoginTime,TypeID
                    from @Table_var
                    where RowNumber=@Index and UserID=@CurrentUserID 
            END

            if(@Index=@End)--last element?
            begin
                insert into @temp
                select ID,UserID,LoginTime,TypeID
                from @Table_var
                where RowNumber=@Index and not 
                abs((select datediff(mi,@CurrentLoginTime,max(LoginTime)) from @temp where UserID=@CurrentUserID))<=14
            end

            select @Index=@Index+1
    end 

    delete  from @Table_var

    insert into @Table_var
    select ID, UserID ,LoginTime ,TypeID ,row_number() OVER(ORDER BY UserID,LoginTime) AS 'RowNumber' 
    from @temp

    return 

END

Step 3: Give it a spin

declare @TVP TVP

INSERT INTO @TVP
select ID,UserId,[DateType],TypeID from Shays_table where TypeID=1 --AND any other date restriction you want to add 

select * from fnGetLoginFreq(@TVP) order by LoginTime asc

My tests returned this:

ID  UserId  LoginTime               TypeID  RowNumber
2   2       2010-01-01 10:01:50.000 1       3
4   1       2010-01-01 10:03:50.000 1       1
5   1       2010-01-01 11:00:00.000 1       2
6   2       2010-01-01 11:00:50.000 1       4