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

You can use a recursive CTE for this though I would also evaluate a cursor if the result set is at all large as it may work out more efficient.

I've left out the ID column in my answer. If you really need it it would be possible to add it. It just makes the anchor part of the recursive CTE a bit more unwieldy.

DECLARE @T TABLE
(
ID INT PRIMARY KEY,
UserID INT,
[DateTime] DateTime,
TypeID INT
)
INSERT INTO @T
SELECT 1,1,'20100101 10:00:00', 1 union all
SELECT 2,2,'20100101 10:01:50', 1 union all
SELECT 3,1,'20100101 10:02:50', 1 union all
SELECT 4,1,'20100101 10:03:50', 1 union all
SELECT 5,1,'20100101 11:00:00', 1 union all
SELECT 6,2,'20100101 11:00:50', 1;


WITH RecursiveCTE
     AS (SELECT UserID,
                MIN([DateTime]) As [DateTime],
                1               AS TypeID
         FROM   @T
         WHERE  TypeID = 1
         GROUP  BY UserID
         UNION ALL
         SELECT UserID,
                [DateTime],
                TypeID
         FROM   (
                --Can't use TOP directly
                SELECT T.*,
                       rn = ROW_NUMBER() OVER (PARTITION BY T.UserID ORDER BY
                            T.[DateTime])
                 FROM   @T T
                        JOIN RecursiveCTE R
                          ON R.UserID = T.UserID
                             AND T.[DateTime] >=
                                 DATEADD(MINUTE, 15, R.[DateTime])) R
         WHERE  R.rn = 1)

Answer 2

How about this, it's fairly straight forward and gives you the result you need:

SELECT ID, UserID, [DateTime], TypeID
FROM Users
WHERE Users.TypeID = 1
  AND NOT EXISTS (
    SELECT TOP 1 1 
    FROM Users AS U2 
    WHERE U2.ID <> Users.ID 
      AND U2.UserID = Users.UserID 
      AND U2.[DateTime] BETWEEN DATEADD(MI, -15, Users.[DateTime]) AND Users.[DateTime] 
      AND U2.TypeID = 1)

The NOT EXISTS restricts to only show records that have no record within 15minutes before them, so you will see the first record in a block rather than one every 15mins.

Edit: Since you want to see one every 15mins this should do without using recursion:

SELECT Users.ID, Users.UserID, Users.[DateTime], Users.TypeID 
FROM
  (
    SELECT MIN(ID) AS ID, UserID, 
      DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0) AS [DateTime]
    FROM Users
    GROUP BY UserID, DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0)
  ) AS Dates
  INNER JOIN Users AS Users ON Users.ID = Dates.ID
WHERE Users.TypeID = 1
  AND NOT EXISTS (
    SELECT TOP 1 1
    FROM
      (
        SELECT MIN(ID) AS ID, UserID, 
          DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0) AS [DateTime]
        FROM Users
        GROUP BY UserID, DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0)
      ) AS Dates2
      INNER JOIN Users AS U2 ON U2.ID = Dates2.ID
    WHERE U2.ID <> Users.ID 
      AND U2.UserID = Users.UserID 
      AND U2.[DateTime] BETWEEN DATEADD(MI, -15, Users.[DateTime]) AND Users.[DateTime] 
      AND U2.TypeID = 1
  )
ORDER BY Users.DateTime

If this doesn't work please post more sample data so that I can see what is missing.

Edit2 same as directly above but just using CTE now instead for improved readability and help improve maintainability, also I improved it to highlighted where you would also restrict the Dates table by whatever DateTime range that you would be restricting to the main query:

WITH Dates(ID, UserID, [DateTime])
AS
(
  SELECT MIN(ID) AS ID, UserID, 
    DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0) AS [DateTime]
  FROM Users
  WHERE Users.TypeID = 1 
  --AND Users.[DateTime] BETWEEN @StartDateTime AND @EndDateTime
  GROUP BY UserID, DATEADD(minute, DATEDIFF(minute,0,[DateTime]) / 15 * 15, 0)
)

SELECT Users.ID, Users.UserID, Users.[DateTime], Users.TypeID 
FROM Dates
  INNER JOIN Users ON Users.ID = Dates.ID
WHERE Users.TypeID = 1 
  --AND Users.[DateTime] BETWEEN @StartDateTime AND @EndDateTime
  AND NOT EXISTS (
    SELECT TOP 1 1
    FROM Dates AS Dates2
      INNER JOIN Users AS U2 ON U2.ID = Dates2.ID
    WHERE U2.ID <> Users.ID 
      AND U2.UserID = Users.UserID 
      AND U2.[DateTime] BETWEEN DATEADD(MI, -15, Users.[DateTime]) AND Users.[DateTime] 
      AND U2.TypeID = 1
  )
ORDER BY Users.DateTime

Also as a performance note, whenever dealing with something that might end up being recursive like this potentially could be (from other answers), you should straight away be considering if you are able to restrict the main query by a date range in general even if it's a whole year or longer range

Answer 3

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