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.

This was last published in March 2003

## 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

• ### Deft preprocessing marks deep learning techniques for data preparation

Deep learning techniques for data preparation include exploration of data sets and algorithms. This calls for more than a bit of ...

• ### DMBOK2 eyes evolving data management best practices, challenges

In a Q&A, DAMA president Sue Geuens talks about the second edition of the group's DMBOK reference book and how data management ...

• ### MongoDB Atlas footprint grows bigger with Azure, Google cloud support

MongoDB has expanded cloud coverage for its Atlas hosted database service, with Azure and Google versions joining an initial ...

• ### Examining the KNIME Analytics Platform for big data analytics

KNIME offers open source data analytics, reporting and integration tools, as well as commercial software that can help build more...

• ### Machine learning platforms comparison: Amazon, Azure, Google, IBM

The platform war over machine learning tools is heating up. Use our features comparison chart to see how four top vendors stack ...

• ### Exploring Oracle Advanced Analytics

Oracle Advanced Analytics and other data analytics tools, including Oracle R Enterprise and Oracle Data Miner, enable business ...

## SearchSAP

• ### SAP BW/4HANA application could streamline finance and planning

SAP Business Planning and Consolidation for BW/4HANA aims to simplify and consolidate enterprise financial planning; Business ...

• ### Choosing SAP HR module complicated by S/4HANA, SuccessFactors options

A SuccessFactors consultant explains the benefits and challenges of adding to or replacing on-premises SAP HCM with the newer ERP...

• ### Move to SAP S/4HANA Cloud improves sales app's security and performance

Emplay, an SAP Startup Focus partner, moved its Sales GPS app to SAP S/4HANA Cloud and saw improvements in performance, ...

## SearchSQLServer

• ### A closer look at Python-SQL Server 2017 integration

Do you know everything you need to take advantage of SQL Server 2017's support of Python?

• ### How much do you really know about SQL Server 2016 licensing?

Should you use Server + CAL licensing for SQL Server 2016 or does Per Core licensing offer more bang for your buck? Read our ...

## TheServerSide

• ### How Pokemon Go needed a Kubernetes powered Java cloud

How did Pokemon Go scale so well? A big part of it's scalability was the Java cloud platform that leveraged containers and ...

• ### How microservices patterns made Uber's architecture perform better

How did Uber manage to deal with the inevitable Halloween rush? Applying microservices patterns to their architecture played a ...

• ### Effective DevOps hinges on automating a continuous delivery pipeline

Many organizations struggle to bring development and operations together, only to realize that effective DevOps means ...

## SearchDataCenter

• ### Distributed data centers boost resiliency, but IT hurdles remain

Distributed data center architectures increase IT resiliency compared to traditional single-site models, with networking, data ...

• ### Server firmware security threat, real or not, gets renewed attention

The latest threat to server security could be the firmware. That's the emphasis of at least one server-maker, hoping to help ...

• ### Assess and compare DCIM software options in the market

DCIM software can improve the management and operation of your data center. Choose the right vendor for your company to control ...

## SearchContentManagement

• ### Combining DAM and DX for digital experience management

Digital asset management systems predate the web. Cutting-edge digital experiences, however, depend on keeping DAM in order, with...

• ### Three BI dashboard best practices you need to know

As companies struggle with BI integration, Microsoft has figured out the magic formula with its Power BI dashboard. Here are the ...

Adobe rounds out its cloud suite with Adobe Scan, which allows mobile employees to capture documents and images and turn them ...

## SearchFinancialApplications

• ### Bundled payments healthcare tool finds better deals for employers

Trying to rein in healthcare costs, large self-insured employers are using HR tech tools, such as analytics-based bundled payment...

• ### Ceridian uses employee engagement tool from its own joint venture

After forming a new joint venture, LifeWorks, HCM vendor Ceridian uses LifeWorks' employee recognition, engagement and perks ...

• ### Evaluate HR technology to fit your talent management processes

Choose your talent management system wisely or risk irking your employees. Learn the criteria for evaluating software that ...

Close