CODEWITHSUNDEEP
sqlcountsql-server-2012
Alfons
Alfons

Reputation: 147

Counting occurences within a range of my table

I have the following question when I try to sum the number of occurrences within an specific time frame.

Let's say I have the following table:

     Date                New
     2013-01-01           1
     2013-01-01           0
     2013-01-01           0                                  
     2013-01-01           1
     2013-01-02           1
     2013-01-02           0
     2013-01-03           1
     2013-01-03           1
     2013-01-04           0
     2013-01-04           1
     2013-01-05           1

Where I want to count the number of 'New' that occur every two days.

In the example provided above, the result I'd be expecting to get would be as follows:

     Date                 Result
     2013-01-01/02           3
     2013-01-02/03           3
     2013-01-03/04           3
     2013-01-04/05           2

Where you can see that I am counting the number of occurrences that happen every two days.

Please note that I am working with a big table (> 10m lines) and I would need > 50 different ranges to be produced (considering each grouping of 2 days as a range).

I am working with SQL Server 2012.

Thanks in advance for your help!

Upvotes: 0

Views: 496

Answers (2)

R_Scott
R_Scott

Reputation: 143

Gordon Linoff's answer is better for SQL 2012, but I thought I'd take a stab at an alternative that would work in previous versions because this is an interesting problem. 

SELECT #table1.[Date], (SUM(new) + t.cnt2) AS cnt
FROM #table1
INNER JOIN (SELECT [Date], SUM(new) AS cnt2 
           from #table1 
           GROUP BY #table1.[date]) t ON #table1.[date] = t.[date]-1
GROUP BY #table1.[date], t.cnt2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270411

Here is one method that aggregates the data once and then uses window functions to do the calculation. The calculation just adds the previous value, when the previous date is one less than the current date:

select date,
       (cnt + (case when DATEDIFF(day, prevdate, date) = 1 then prevcnt else 0 end)) as Result
from (select date, SUM(new) as cnt,
             lag(DATE) over (order by date) as prevdate,
             lag(SUM(new)) over (order by date) as prevcnt
      from t
      group by date
     ) t

Upvotes: 1

Related Questions