Geek Answers Handbook

What is the most efficient way to group by day to overlap date ranges (SQL Server 2008)?

Consider a simplified table T1 as follows:

CREATE TABLE dbo.T1 (
    id        INTEGER       NOT NULL
    ,measure  NUMERIC(15,2) NOT NULL
    ,begin_dt DATE          NOT NULL
    ,end_dt   DATE          NOT NULL
);

Suppose constraints / business logic ensure that although each identifier can have multiple entries, there are no overlapping date ranges for one identifier and no date range spaces for one identifier. eg.

id   | measure |  begin_dt  |   end_dt
-----------------------------------------
1    |  100.00 | 2012-05-07 | 2012-05-30
1    |  200.00 | 2012-05-31 | 2013-10-11
1    |   50.00 | 2013-10-12 | 2013-10-13
1    |    0.00 | 2013-10-14 | 9999-12-31
2    | 1234.56 | 2002-02-25 | 9999-12-31
3    |    9.87 | 2014-01-31 | 2014-02-15
3    |   50.00 | 2014-02-16 | 2015-01-04
3    |    0.00 | 2015-01-05 | 9999-12-31
...

Now my goal is to create a result set that shows one record for each unique begin_dt in T1, as well as an id counter with a positive measure value and the sum of the measure field over the entire identifier for which this date is between begin_dt and end_dt. So, something like the following:

    dt     | count_of_ids | sum_of_measure 
-------------------------------------------
2002-02-25 |      1       |   1234.56 
2012-05-07 |      2       |   1334.56 
2012-05-31 |      2       |   1434.56 
2013-10-12 |      2       |   1284.56 
2013-10-14 |      1       |   1234.56 
2014-01-31 |      2       |   1244.43 
2014-02-16 |      2       |   1284.56
2015-01-05 |      1       |   1234.56
...

My current solution is essentially the following:

SELECT *
FROM (
    SELECT DISTINCT t1.begin_dt AS dt
    FROM dbo.T1 AS t1
) AS dt_s
CROSS APPLY (
    SELECT COUNT(t1.id)     AS count_of_ids
           ,SUM(t1.measure) AS sum_of_measure
    FROM dbo.T1 AS t1
    WHERE t1.measure > 0
          AND dt_s.dt BETWEEN t1.begin_dt AND t1.end_dt
) AS t1_x
ORDER BY dt_s.dt DESC;

3,5 ( ~ 10MM, ~ 2500 ​​ , ) - , 10 .

( UDFs/CTEs/ ..), , , . , , . !

+4
1

:

SELECT  t1.begin_dt AS dt,COUNT(t2.id) AS count_of_ids,SUM(t1.measure) AS sum_of_measure
    FROM dbo.T1 AS t1
    JOIN dbo.T1 AS t2 ON t1.begin_dt BETWEEN t2.begin_dt AND t2.end_dt
    GROUP BY t1.begin_dt;

, , begin_dt, end_dt . , !

0

More articles:


All Articles