Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Match events of records

Posted on 2013-01-31
5
Medium Priority
?
374 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

971 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