Solved

Match events of records

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 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