Q
Problem solve Get help with specific problems with your technologies, process and projects.

# Maximum of 5-day running totals

## I have a table of dates and number of events per date. I need to find a 5-day range where the most number of events happened. I just couldn't figure out a way to sum up the values of events-per-date for every 5 rows. This means all possible 5 consecutive rows, but dates are not necessarily consecutive (days where no events occurred are not kept).

I have a table of dates and number of events per date. I need to find a 5-day range where the most number of events...

happened. I just couldn't figure out a way to sum up the values of events-per-date for every 5 rows. This means all possible 5 consecutive rows, but dates are not necessarily consecutive (days where no events occurred are not kept).

```eventdate  numevents
2006-06-16    3
2006-06-17    3
2006-06-18    3
2006-06-19    3
2006-06-20    4
2006-06-21    4
2006-06-22    4
2006-06-23    4
2006-06-24    3
2006-06-25    2
2006-06-26    2
2006-06-27    2
2006-06-30    2
2006-07-01    2
2006-07-04    1```

This is only a very small section of the table.

What an interesting problem. Thanks for sending it in.

The solution requires a self-join. What we need to do is join the table to itself, with a special condition: each row must be joined only to itself and the immediately preceeding four rows. In other words, we join each row to the top 5 rows that have an eventdate that is equal to or less than that row. Please make sure you understand this statement of the problem before looking at the following SQL:

```select t1.eventdate
, t1.numevents
, t2.eventdate
, t2.numevents
from events as t1
inner
join events as t2
on t2.eventdate <= t1.eventdate
and 5 >=
( select count(*)
from events
where eventdate
between t2.eventdate
and t1.eventdate )
order
by t1.eventdate
, t2.eventdate```

The above query produces the following results:

```t1             t2
2006-06-16  3  2006-06-16  3

2006-06-17  3  2006-06-16  3
2006-06-17  3  2006-06-17  3

2006-06-18  3  2006-06-16  3
2006-06-18  3  2006-06-17  3
2006-06-18  3  2006-06-18  3

2006-06-19  3  2006-06-16  3
2006-06-19  3  2006-06-17  3
2006-06-19  3  2006-06-18  3
2006-06-19  3  2006-06-19  3

2006-06-20  4  2006-06-16  3
2006-06-20  4  2006-06-17  3
2006-06-20  4  2006-06-18  3
2006-06-20  4  2006-06-19  3
2006-06-20  4  2006-06-20  4

2006-06-21  4  2006-06-17  3
2006-06-21  4  2006-06-18  3
2006-06-21  4  2006-06-19  3
2006-06-21  4  2006-06-20  4
2006-06-21  4  2006-06-21  4

and so on```

Notice that the first four t1 dates do not have five preceeding t2 dates. We'll come back to that in a moment.

Now that we know we're looking at the right data, we can do the next step, which is to add up the numevents values for the joined t2 rows. We do this by changing the query into a GROUP BY query:

```select t1.eventdate
, count(*)          as rows
, sum(t2.numevents) as total
from events as t1
inner
join events as t2
on t2.eventdate <= t1.eventdate
and 5 >=
( select count(*)
from events
where eventdate
between t2.eventdate
and t1.eventdate )
group
by t1.eventdate```

The above query produces the following results:

```eventdate  rows total
2006-06-16  1     3
2006-06-17  2     6
2006-06-18  3     9
2006-06-19  4    12
2006-06-20  5    16
2006-06-21  5    17
2006-06-22  5    18
2006-06-23  5    19
2006-06-24  5    19
2006-06-25  5    17
2006-06-26  5    15
2006-06-27  5    13
2006-06-30  5    11
2006-07-01  5    10
2006-07-04  5     9 ```

Notice that we can add HAVING COUNT(*)=5 to this query if we wish to eliminate the first four dates.

The final step is to take the maximum of the total column, and there are a number of ways to do it, which we don't really have room to discuss. The easiest is to add ORDER BY total DESC to the above query, and, by inspection, take the first result row. Caution: look for ties for the maximum, as there are in this case for the 23rd and 24th.

This was last published in October 2006

## Content

Find more PRO+ content and other member only offers, here.

#### Have a question for an expert?

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Streaming tool from StreamSets eyes data in motion for GDPR

StreamSets software for inspecting big data brings governance to data in motion. Such capabilities may find more use as the ...

• ### Data expert: GDPR deadline is an opportunity, not a burden

There is stress as the EU's General Data Protection Regulation compliance deadline nears, but the GDPR privacy movement is a good...

In big data news, we find Google TPUs, or Tensor Processing Units, offered as a cloud service, while LinkedIn is open sourcing a ...

• ### Rethinking analytics processes spurs enterprise innovation

By taking a fresh look at the makeup of their analytics organizations, enterprises can innovate their business models and take ...

• ### Diversified data sets for analytics deliver top results

Analytics teams should focus on data diversity to ensure that their projects deliver the most meaningful insights -- but they ...

• ### How to boost the value of BI in today's analytics landscape

Traditional BI reporting still gives businesses valuable information. But its value can be increased by incorporating it into a ...

## SearchSAP

• ### SAP Ariba Live focuses on procurement for purpose

SAP Ariba Live 2018 focused on the idea that businesses can use procurement technology to do good in the world; for example, by ...

• ### SAP debuts consumption-based pricing model for SAP Cloud

SAP Cloud Platform is now available as a consumption-based model, an alternative to the subscription model. SAP also updated the ...

SAP paid \$2.4 billion to acquire lead-to-money vendor CallidusCloud, and analysts agree that the significant price may be worth ...

## SearchSQLServer

SQL Operations Studio simplifies routine administration of SQL Server and Azure SQL databases, making database development and ...

• ### Meltdown and Spectre fixes eyed for SQL Server performance issues

Microsoft has responded to the Spectre and Meltdown chip vulnerabilities with patches and other fixes. But IT teams need to sort ...

• ### Five SQL Server maintenance steps you should take -- ASAP

Putting off SQL Server administration tasks can lead to database problems. Enact these often-neglected maintenance items to help ...

## TheServerSide.com

• ### Why the Waterfall or Agile debate will be around forever

Which is the right methodology to use for your project: Waterfall or Agile? The industry may be at peak Agile, as the ...

• ### Chef's InSpec 2.0 brings compliance automation to the cloud

Enterprises have been quick to adopt automation tools for development and deployment but only recently have organizations started...

• ### Application security vulnerabilities are often known exploits

How hard is it to secure an enterprise application? It's not hard, especially given the fact that most application security ...

## SearchDataCenter

• ### IBM cloud services to secure mainframes out to the edge

Big Blue will introduce IBM cloud services that use blockchain, containers and its z14 mainframes to deliver improved security ...

• ### Four disadvantages of hyper-converged infrastructure systems

Problems with scalability and unexpected licensing costs can create problems for organizations that deploy hyper-converged ...

• ### IBM Power9 servers seek market inroads to AI, cloud

IBM follows up its first Power9 server with a raft of systems designed to appeal to a wider array of markets -- most notably, AI ...

## SearchContentManagement

• ### Scrivito unveils serverless CMS product

By building the CMS with ReactJS, Scrivito gained attraction with development community, according to an analyst.

• ### Content personalization tools sharpen focus on customers

Content personalization isn't new; Amazon weaponized it, and Jeff Bezos is the world's richest man. New tools are putting it ...

• ### Leading brands see the need for personalized content

Content personalization continues to expand within companies as maturing technologies make it a viable marketing option for ...

## SearchHRSoftware

• ### How people analytics can improve HR effectiveness

Getting insight into your workforce can reveal everything from training issues to the reasons for turnover or missed corporate ...

• ### At Ceridian, role of CIO requires constant learning, adjusting

You might say Warren Perlman, CIO at Ceridian, a global HCM software company, has been preparing for the role of CIO all his life...

• ### Human resources help desk multiplies HR staff efficiency

Here's how a college used a human resources help desk to better serve its 5,000-plus faculty and staff and enabled HR to focus on...

Close