Solved

Match events of records

Posted on 2013-01-31
5
324 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
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now