CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ashley John
Ashley John

Reputation: 2453

How to group by Date Range starting from initial date

I have the following table structure

Key int
MemberID int
VisitDate DateTime

How can group all the dates falling with a given date range say 15 days..The first visit for the sameMember should be considered as the starting date.

eg

Key     ID     VisitDate(MM/dd/YY)
1        1      02/01/11
2        1      02/09/11
3        1      02/12/11
4        1      02/17/11
5        2      02/03/11
6        2      02/19/11

In this case the result should be

ID   StartDate      EndDate    
1    02/01/11       02/12/11    
1    02/17/11       02/17/11     
2    02/03/11       02/03/11     
2    02/19/11       02/19/11

Upvotes: 2

Views: 194

Answers (1)

Andriy M
Andriy M

Reputation: 77657

One way to do this would be to use window aggregating. Here's how:

  1. Setup:

    DECLARE @data TABLE (
  [Key] int, ID int, VisitDate date
);

INSERT INTO @data ([Key], ID, VisitDate)
SELECT 1, 1, '02/01/2011' UNION ALL
SELECT 2, 1, '02/09/2011' UNION ALL
SELECT 3, 1, '02/12/2011' UNION ALL
SELECT 4, 1, '02/17/2011' UNION ALL
SELECT 5, 2, '02/03/2011' UNION ALL
SELECT 6, 2, '02/19/2011';

  2. Query:

    WITH marked AS (
  SELECT
    *,
    Grp = DATEDIFF(DAY, MIN(VisitDate) OVER (PARTITION BY ID), VisitDate) / 15
  FROM @data
)
SELECT
  ID,
  StartDate = MIN(VisitDate),
  EndDate   = MAX(VisitDate)
FROM marked
GROUP BY ID, Grp
ORDER BY ID, StartDate

  3. Output:

    ID          StartDate  EndDate
----------- ---------- ----------
1           2011-02-01 2011-02-12
1           2011-02-17 2011-02-17
2           2011-02-03 2011-02-03
2           2011-02-19 2011-02-19

Basically, for each row, the query is calculating the difference of days between VisitDate and the first VisitDate for the same ID and divides it by 15. The result is then used as a grouping criterion. Note that SQL Server uses integer division when both operands of the / operator are integers.

Upvotes: 3

Related Questions