Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Logical SELECT problem - do I need cursors?

Author  Topic 

ikantspel
Starting Member

1 Post

Posted - 2013-03-15 : 17:00:19
What follows is a reduced version of a more complex real-world problem I am trying to solve.

There is a light bulb with an on button, and an off button. Over a period of time, we have kept track of each time either button was pressed. The table looks like this:

Time|Button
9:00|Off
10:00|On
11:00|On
12:00|On
13:00|Off
14:00|On
15:00|On

Special note here - as 15:00 is the final entry, we will assume that we stopped tracking here, and also assume that the lightbulb turned off immediately after 15:00.

The goal is to make a list of times that the light bulb was on, and report the time it turned on, and the time it turned off. The logic should be straightforward, other than the rule that the bulb turns off immediately after the final entry, regardless of when or what button the final entry was.

So for this sample table, the list contains two entries like this:

On|Off
10:00|13:00
14:00|15:00

What's the best way to go about doing this? I am dimly aware of what cursors are, but have never used them before. Performance isn't as big an issue as maintainable code is.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-03-15 : 18:31:15
There is a sophisticated "Gaps and Islands" technique from Itzik Ben_Gan to do this.

But here is a more step-by-step way.

Most people like/understand CTEs better so I used that method.

Hopefully at the very least it will help you get going.


DECLARE @table1 TABLE (
time time,
button char(3)
)

insert into @table1
select '9:00','Off' union all
select '10:00','On' union all
select '11:00','On' union all
select '12:00','On' union all
select '13:00','Off' union all
select '14:00','On' union all
select '15:00','On'
SELECT * FROM @table1

;WITH
buttons_all AS (
SELECT
time, button, ROW_NUMBER() OVER(ORDER BY time) AS row_num
FROM @table1 ons
),
buttons_first_ons AS (
SELECT
time, row_num
FROM buttons_all b_ons
WHERE
button = 'on' AND
EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_ons.row_num - 1 AND b_all.button = 'off')
),
buttons_first_offs AS (
SELECT
time, row_num
FROM buttons_all b_offs
WHERE
button = 'off' AND
EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_offs.row_num - 1 AND b_all.button = 'on')
)
SELECT
b_ons.time AS [On],
ISNULL(b_offs.time, (SELECT MAX(time) FROM buttons_all)) AS [Off]
FROM buttons_first_ons b_ons
LEFT OUTER JOIN buttons_first_offs b_offs ON
b_offs.row_num = (SELECT MIN(row_num) FROM buttons_first_offs b_offs2 WHERE b_offs2.row_num > b_ons.row_num)
Go to Top of Page
   

- Advertisement -