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

# When to use a cursor

I have a table of events (SQL Server 2000) with a start date/time and some other columns, some of which can be summed.

```Id  counter  starttime
1    17     '23 jan 2003 11:20:00'
1    43     '23 jan 2003 11:24:45'
1    26     '23 jan 2003 11:32:23'
1    33     '23 jan 2003 12:15:20'
1    45     '23 jan 2003 12:18:43'```

How can I group events which started within say 30 minutes of each other? The result I want is to sum the first 3, as they all occur within 30 minutes of the first event, then sum the last two as they are a new event set, and get the results :

```Id  counter  starttime
1    86     '23 jan 2003 11:20:00'
1    78     '23 jan 2003 12:15:20'```

I understand how to group by Id, and sum(counter) etc., but can't get my head around grouping by (or having) all events occurring within the 30 minute time frame from the first :-O.

NB the 30 minutes is an arbitrary number chosen to illustrate this example, the actual number will probably be different and need to be 'tuned'.

I can see how I could do this by running through the table in a cursor (or an external c# app etc.), and summing in the loop, but hope that it's possible without this overhead.

Short answer: it may be possible without the "overhead" of a cursor, but there will be other overheads that I'm sure you will not want, because they are several orders of magnitude larger.

Your requirement to "sum the first 3, as they all occur within 30 minutes of the first event" is quite easy to comprehend. Unfortunately, I think it's an example of petitio principii (to beg the question). We instinctively use process logic when considering problems of this sort. We cannot really help ourselves, because of our background in programming languages:

2. set the accumulator to the record's counter value, and save the time
3. get the next record
5. compare the saved time to this record's time
6. if within 30 minutes, add the counter to the accumulator, and go back to 3
7. output the accumulated value and time
8. if this wasn't the last record, go to 2

This logic is very easy to implement with a scripting language and a cursor. In SQL Server, use Transact-SQL. You end up making one really efficient pass of the table, and Bob's your uncle.

Obtaining the same result using set-based logic, where you do not specify how to get what you want, but simply what you want, is fantastically more difficult in this particular situation.

First of all, we need to think of the groups. How do we group the individual rows?

If we wanted to group them according to fixed time slots, e.g. 11:00 to 11:30, 11:30 to 12:00, 12:00 to 12:30, and so on, then the solution is straightforward, and all we need is to "generate" the appropriate time slots and do a LEFT JOIN. See Grouping output by week (24 October 2002), which uses weeks instead of 30-minute intervals.

But I get the sense that your example doesn't want to use pre-set time slots, and instead, you wish to group the rows based on the time values in the data. This is hard. The query has to create groups based on the lowest time in the group being later than 30 minutes after the next lower time. I can foresee multiple subqueries. Perhaps a theta join. I'm getting a headache just thinking about where to start. No, let's not even go there. Use a cursor.

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Ascend boosts enterprise data governance with data lineage

The new capabilities of Ascend Govern bring data lineage and enterprise data governance as well as the ability to try and figure ...

• ### The business benefits of enterprise data governance and MDM

Data leaders from prominent large organizations provide insights into data governance best practices and benefits, at ...

• ### How data governance and data management work together

Data governance is an important part of data management. These recommendations will help build a strong complementary ...

• ### SAS analytics platform benefiting from AI investment

Fueled by SAS' investment in AI and augmented intelligence features, the long-established vendor strengthened its BI platform ...

• ### Trends and top use cases for streaming data analytics

As more enterprises adopt real-time analytics, new infrastructure and best practices are appearing. Here are some trending ...

• ### COVID-19 effect on analytics software development an unknown

With COVID-19 forcing software engineers to work remotely rather than together in teams, they'll need to find new ways of ...

## SearchSAP

• ### SAP S/4HANA migration: Critical advice for moving off ECC

With the end of SAP ECC support looming in 2027, organizations must make some tough decisions. Here's a look at your choices.

• ### How to pick a SuccessFactors implementation partner

Selecting the right SuccessFactors implementation partner is a critical step in the journey to deploying the system. Check out ...

• ### 6 critical steps of a successful SAP S/4HANA migration

A successful S/4HANA project starts with knowing why your organization should even make the move, then nailing down whether S/4 ...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### 5 Jenkins alternatives for Java developers

In need of an alternative to Jenkins as part of a CI/CD environment? Consider options from Atlassian, GitLab, Spinnaker, ...

• ### Work from home tips from distributed development teams during COVID-19

The distributed dev community often works remotely. Pick up these approaches, such as an asynchronous schedule and video ...

• ### How to refactor the God object antipattern

Loose coupling can inadvertently create a God object problem in an object-oriented software system. Here's how to get rid of the ...

## SearchDataCenter

• ### Tech giants aim supercomputers, clouds at finding a COVID-19 cure

The new COVID-19 HPC consortium comprised of tech giants, national labs and academia are using supercomputers and clouds to speed...

• ### Top vendor SDDC certification programs for admins

If you decide to grow your knowledge of software-defined data center technologies, VMware, Nuage Networks and Microsoft offer ...

• ### Build up your knowledge of leaf-spine network technology

As organizations deal with challenges of single tree path networks, many admins are considering leaf-spine architectures. Learn ...

## SearchContentManagement

• ### Vendors offer free remote work technology for telecommuting

It's more than a marketing ploy: Cloud technology vendors, seeing their own issues enabling remote work, extend paid products to ...

• ### How businesses should deal with enterprise search issues

Enterprise search issues frequently complicate user experience with ECM systems. While users may face many problems, they also ...

• ### Box, Dropbox shore up clouds; Box-Microsoft integrations go live

Online document collaboration platforms Box and Dropbox shore up cloud bandwidth, security and customer support as remote work ...

## SearchHRSoftware

• ### Farmers Insurance piloting human-like VR training

VR training has become a best practice at Farmers Insurance Exchange. Its claims adjusters are being exposed to more scenarios ...

• ### Virtual hiring, avoiding layoffs part of HR's pandemic response

In the face of a pandemic, many firms are trying to avoid layoffs and even hire. They are using virtual tools, including testing ...

• ### Transformational HR in 2020: 7 strategies for agility and tech mastery

CHROs and their teams must grow their people, business and technology skills as they focus on curating the employee experience. ...

Close