COUNT with DATE INTERVALS

Question

I have this table called Table1 as follows:

    UserID   Date 
    1        01/01/09
    1        14/01/09
    1        25/01/09
    1        01/02/09
    1        15/02/09
    2        02/02/09
    2        15/02/09

I am trying to return a result that counts the number of times between the MIN(Date) and 30 days after the MIN(Date) which is DATEADD(day,30,MIN(DATE)). So it would look something like this:

    UserID   Count
    1        3 
    2        2

This code below is wrong but it expresses what I am trying to achieve:

SELECT COUNT(1) AS Count
FROM Table1
GROUP BY UserID
WHERE Date BETWEEN MIN(Date) AND DATEADD(day,30,MIN(DATE))

Answer 1

Try this

DECLARE @table TABLE(
     UserID INT,
     DDate DATETIME
)

INSERT INTO @table (UserID,DDate) SELECT 1, '01 Jan 2009'
INSERT INTO @table (UserID,DDate) SELECT 1, '14 Jan 2009'
INSERT INTO @table (UserID,DDate) SELECT 1, '25 Jan 2009'
INSERT INTO @table (UserID,DDate) SELECT 1, '01 Feb 2009'
INSERT INTO @table (UserID,DDate) SELECT 1, '15 Feb 2009'
INSERT INTO @table (UserID,DDate) SELECT 2, '02 Feb 2009'
INSERT INTO @table (UserID,DDate) SELECT 2, '15 Feb 2009'


SELECT  t.UserID,
     COUNT(t.UserID)
FROM    @table t INNER JOIN
     (
      SELECT UserID,
        MinDate,
        DATEADD(dd, 30, MinDate) MinDataAdd30
      FROM (
         SELECT UserID,
           MIN(DDate) MinDate
         FROM @table
         GROUP BY UserID
        ) MINDates
     ) DateRange ON t.UserID = DateRange.UserID
WHERE   t.DDate BETWEEN DateRange.MinDate AND DateRange.MinDataAdd30
GROUP BY t.UserID

Answer 2

I think you'll need to use a subquery to get the minimum date. I've shown it below as a separate query into a variable as I'd probably turn this into a table-valued function.

DECLARE @STARTDATE DATETIME

SELECT @STARTDATE = MIN(DATE) FROM Table1

SELECT COUNT(1) AS Count
FROM Table1
GROUP BY UserID
WHERE Date BETWEEN @STARTDATE AND DATEADD(day,30,@STARTDATE)

Answer 3

SELECT a.UserID, COUNT(a.UserID) AS [Count]
FROM Table1 AS a
    INNER JOIN
    (
        SELECT UserID, MIN([Date]) AS MinDate
        FROM Table1
        GROUP BY UserID
    ) AS b
        ON a.UserID = b.UserID
WHERE [Date] BETWEEN MinDate AND DATEADD(day, 30, MinDate)
GROUP BY a.UserID

Answer 4

I would do it like this:

select a.UserID, count(case when DDate - MinDate <= 30 then 1 end) as Count
from (
    select UserID, min(DDate) MinDate
    from Table1
    group by UserID
) a
inner join Table1 t on a.UserID = t.UserID
group by a.UserID