Solved

Match events of records

Posted on 2013-01-31
5
358 Views
Last Modified: 2013-02-04
Hi all,

I'm struggling with a SQL SELECT statement I'm putting into a SQL RS report.  I'm not even sure I know where to start!!

I have a table called "Task_Event" which will have a series of Events (event_type) such as Assigned, Arrived, On Hold, Off Hold amongst others.  Each event will have a date/time stamp (event_dttm).  

What I want to do is calculate the time time a task is "On Hold" (the difference between On Hold & Off Hold event_dttm).  The problem I have is that there will be multiple On Hold & Off Hold events so I need to match them sequentially.  i.e. the first On Hold will match the first Off Hold, the second On Hold will match the second Off Hold etc.  I'm not going to know exactly how many On Holds & Off Holds will exist and there will be other event types between the On Hold & Off Hold events so there's no guarantee the record immediately after an On Hold will be an Off Hold.

Can anyone point me in the right direction of the SQL SELECT statement needed?

Thanks

Paul
0
Comment
Question by:TTG-Group
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 6

Accepted Solution

by:
liija earned 500 total points
ID: 38838966
You could give the on and off events numbers with SQL  Row_Number().
They should be numbered by salesorder number, for example (smaller risk if some transaction lacks the end stamp).

In this sample you get the OnHolds and OffHolds with two queries and then combine them with salesorder number and the transaction order number.

SELECT 
 DATEDIFF(mi,  OnHolds.Event_time, OffHOlds.Event_time) AS TotalTime,
 OnHolds.OrderNumber,
 OnHolds.TransactionNumber
FROM

(
 SELECT OrderNumber,
  Event_type,
  Event_time,
  Row_Number() OVER (PARTITION BY OrderNumber, Event_type ORDER BY Event_Time DESC) AS TransactionNumber
FROM Tasks
WHERE Event_Type = 'On_Hold'
)OnHolds
LEFT OUTER JOIN
(
SELECT OrderNumber,
  Event_type,
  Event_time,
  Row_Number() OVER (PARTITION BY OrderNumber, Event_type ORDER BY Event_Time DESC) AS TransactionNumber
FROM Tasks
WHERE Event_Type = 'Off_Hold'
)OffHolds

ON 
	OnHolds.TransactionNumber = OffHolds.TransactionNumber
 AND OnHOlds.OrderNumber = OffHolds.OrderNumber

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38839387
Are there any other columns / unique identifiers ?

Is it a big table ?

You could use a corelated inline query to get the next Off Hold but not so good for very large data sets because it tends to become row by row lookups...

e.g.
-- step 1 create some dummy data just for the purposes of the test and as example

create table #Task_Event (event_type varchar(20), event_dttm datetime)

insert #Task_Event values ('Assigned',getdate() - 14)
insert #Task_Event values ('Arrived',getdate() - 12)
insert #Task_Event values ('On Hold',getdate() - 11)
insert #Task_Event values ('Off Hold',getdate() -10.1234)
insert #Task_Event values ('Departed',getdate() - 9)
insert #Task_Event values ('On Hold',getdate() - 8.99)
insert #Task_Event values ('Lunch',getdate() - 7.5)
insert #Task_Event values ('Off Hold',getdate() - 6.2)
 
-- now the real part - the query.
-- this uses a CTE query, but, could have been a simple sub-query

;with cte_holds as
(
 select event_dttm as 'On Hold', 
        (select top 1 event_dttm from #task_Event O where event_type = 'Off Hold' and o.event_dttm > h.event_dttm order by o.event_dttm) as 'Off Hold'
 from #Task_Event H
 where event_type = 'On Hold'
) 
select [on hold],[off hold], datediff(second,[on hold],[off hold]) as time_diff_seconds
from cte_holds

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38843487
so how do you want to deal with a "current" on-hold event ... is that to be included in the
time calculation... what end time should be assigned for it ? (e.g now)

how do you want to deal with the scenario of missing on-hold/off-hold events?

(or consider the situation if you are trying to query between a specified time period...
   you will have events which started on-hold or come off hold after the period ranges)
0
 

Author Closing Comment

by:TTG-Group
ID: 38851002
Did exactly what I needed!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38851518
That's interesting... Dont know where OrderNumber came from, and was easy to break based on the assumption that every on hold must have a corresponding and chronologically correct off hold (ie miss one and it reports erroneously). It sorts into descending sequence so, must "start" with an off-hold. Need to make sure that the off-hold always false within range of your selection criteria.

While I am pleased for you that it did everything you needed, was a little disappointe that we couldnt get you engaged with a couple of the other questions we had for you... Lowfatspread did have some very good questions that you may still need to consider.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question