Sql Date Grouping with avaliable dates in database

Question

ID         dateandtime  EmailID
73  6/8/2014 00:00:00   2
74  6/9/2014 00:00:00   2
75  6/10/2014 00:00:00  2
76  6/11/2014 00:00:00  2
77  6/12/2014 00:00:00  2
78  6/13/2014 00:00:00  2
79  6/14/2014 00:00:00  2
80  6/16/2014 00:00:00  2
81  6/17/2014 00:00:00  4
82  6/18/2014 00:00:00  4
83  6/19/2014 00:00:00  4
84  6/20/2014 00:00:00  4
89  6/27/2014 00:00:00  4
90  6/28/2014 00:00:00  4
91  6/29/2014 00:00:00  4
92  6/30/2014 00:00:00  4
93  6/1/2014 00:00:00   4
94  6/2/2014 00:00:00   4
95  6/3/2014 00:00:00   2
96  6/4/2014 00:00:00   2
97  6/5/2014 00:00:00   2
98  6/6/2014 00:00:00   2
99  6/7/2014 00:00:00   2
100 6/21/2014 00:00:00  4
101 6/22/2014 00:00:00  4
102 6/23/2014 00:00:00  4
103 6/24/2014 00:00:00  4
104 7/1/2014 00:00:00   4
105 7/2/2014 00:00:00   4
106 7/3/2014 00:00:00   4
121 7/6/2014 00:00:00   2
122 7/7/2014 00:00:00   2
123 7/8/2014 00:00:00   2

above record through i can generate below group of all date like as

StartDate - EndDate    EmailID
6/1/2014 -  6/2/2014     4
6/3/2014 -  6/14/2014    2
6/16/2014 - 6/16/2014    2
6/17/2014 - 6/24/2014    4
6/27/2014 - 6/30/2014    4 
7/1/2014  - 7/3/2014     4 

i trying to find, for each EmailID, the contiguous blocks of dates? any one solve this question thanks in advance

Answer 1

This is an example of identifying groups of things that occur in order. In this case, the groups are in order by date and you want consecutive dates.

You can find the group by using the different in two sequential values. Enumerate the values by date. Then enumerate the values by date and emailId. The difference is a constant for sequential values with the same emailId. Here is how it would work in your case:

select min(TheDate) as StartDate, max(TheDate) as EndDate, emailId
from (select t.*,
             dateadd(day, - row_number() over (order by TheDate), theDate) as grp
      from table t
     ) t
group by grp, emailId;

Answer 2

As I understand you question you can use the following code:

select min([Start date - End date]) as StartDate, max([Start date - End date]) as EndSare, EmailID from dbo.T group by EmaiIID

Answer 3

This will do the trick for you. First identify the range starts and then the range ends and at the same time set a range identity. Last merge the range start and end sets.

DECLARE @Data TABLE (
    [ID] INT,
    [Date] DATE,
    [EmailID] INT
)
INSERT INTO
    @Data
VALUES
    (  73, '2014-06-08', 2 ),
    (  74, '2014-06-09', 2 ),
    (  75, '2014-06-10', 2 ),
    (  76, '2014-06-11', 2 ),
    (  77, '2014-06-12', 2 ),
    (  78, '2014-06-13', 2 ),
    (  79, '2014-06-14', 2 ),
    (  80, '2014-06-16', 2 ),
    (  81, '2014-06-17', 4 ),
    (  82, '2014-06-18', 4 ),
    (  83, '2014-06-19', 4 ),
    (  84, '2014-06-20', 4 ),
    (  89, '2014-06-27', 4 ),
    (  90, '2014-06-28', 4 ),
    (  91, '2014-06-29', 4 ),
    (  92, '2014-06-30', 4 ),
    (  93, '2014-06-01', 4 ),
    (  94, '2014-06-02', 4 ),
    (  95, '2014-06-03', 2 ),
    (  96, '2014-06-04', 2 ),
    (  97, '2014-06-05', 2 ),
    (  98, '2014-06-06', 2 ),
    (  99, '2014-06-07', 2 ),
    ( 100, '2014-06-21', 4 ),
    ( 101, '2014-06-22', 4 ),
    ( 102, '2014-06-23', 4 ),
    ( 103, '2014-06-24', 4 ),
    ( 104, '2014-07-01', 4 ),
    ( 105, '2014-07-02', 4 ),
    ( 106, '2014-07-03', 4 ),
    ( 121, '2014-07-06', 2 ),
    ( 122, '2014-07-07', 2 ),
    ( 123, '2014-07-08', 2 );

WITH RangeStart AS (
    SELECT
        l.[Date] AS Start,
        l.EmailID AS EmailId,
        ROW_NUMBER() OVER (ORDER BY l.[Date]) AS [RangeId]
    FROM
        @Data as l
        LEFT OUTER JOIN @Data as r on r.[Date] = DATEADD(day, -1, l.[Date])
            AND r.EmailID = l.EmailID
    WHERE
        r.[Date] IS NULL
), RangeEnd AS (
    SELECT
        l.[Date] AS [End],
        l.EmailID AS EmailId,
        ROW_NUMBER() OVER (ORDER BY l.[Date]) AS [RangeId]
    FROM
        @Data as l
        LEFT OUTER JOIN @Data as r on r.[Date] = DATEADD(day, +1, l.[Date])
            AND r.EmailID = l.EmailID
    WHERE
        r.[Date] IS NULL
)
SELECT
    RangeStart.Start,
    RangeEnd.[End],
    RangeStart.EmailId
FROM
    RangeStart
    JOIN RangeEnd ON RangeEnd.RangeId = RangeStart.RangeId

The result is not as you might expect, however from what I can se correct.