| This query is giving me a sore head [message #20786] |
Thu, 20 June 2002 06:13  |
John McTeague
Messages: 1
Registered: June 2002
|
Junior Member |
|
|
Heres the problem. I have two tables. The first is called turnaround, and has three fields: ID, startaction and endaction. The second is called histroy, and among others has the following fields - userid, actionid and actiondate.
The system I have is action driven - each action that occurs on each user in the system is recorded in the history table along with the date that action occurred on.
What the powers that be require is a report detailing the average time it has taken between various actions on users.
i.e. The query i have at the moment for getting the average time between all action 1 and action 3's is
select round(avg(enddate - startdate),2) as turnaround_time from
(select history.actiondate startdate from history where history.actionid= 1 and history.userid in (select userid from history where actionid =3)),
(select history.actiondate enddate from history where history.actionid= 3)
(Action 3 will not always occur, it could be 2, or 4 etc, but action 1 is always going to happen. So alternative queries may involve actions 1 and 2, 1 and 4 etc)
WHat I want to do is hook this query into the turnaround table.
If the turnaround table was like this:
ID-startaction-endaction
-------------------------------------------
1-1-2
2-1-3
3-1-4
...
...
How could I create one query to give me each avg turnaround time I require as opposed to my current situtation where I am repeating the same query over and over but for different action combinations.
Asprin will solve the headache - will it solve the SQL as well?
|
|
|
|
| Re: This query is giving me a sore head [message #20795 is a reply to message #20786] |
Thu, 20 June 2002 11:13  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
It may not be the final solution, but maybe I can help you in the right direction.
I started out creating some data (I used events that are a day apart for ease of viewing) - my table1 is your history table:
SQL> select * from table1;
USERID ACTIONID ACTIONDATE
--------- --------- -----------
1 1 01-JAN-2002
1 2 02-JAN-2002
1 1 03-JAN-2002
1 4 04-JAN-2002
1 1 05-JAN-2002
2 1 11-JAN-2002
2 2 12-JAN-2002
2 1 13-JAN-2002
2 3 14-JAN-2002
3 1 15-JAN-2002
4 1 16-JAN-2002
Given the following, I want to create something that looks like
u - id -1 end start
1 - 1 - 2 02-jan-2002 01-jan-2002
1 - 1 - 4 04-jan-2002 03-jan-2002
2 - 1 - 2 12-jan-2002 11-jan-2002
2 - 1 - 3 14-jan-2002 13-jan-2002
I figured if I got that far, I could do the math and the matching up with the turnaround table. I also got from what you said that every initial action is actionid 1; the subsequent action is different. If that is a false assumption, the rest won't work but it might still help you in your thinking.
Then I used the window feature of analytic functions to do the following:
SQL> select userid, actionid, actiondate,
2 first_value(actiondate) over (order by userid, actiondate asc rows 1 preceding)
3 actiondate_prec
4 from table1;
USERID ACTIONID ACTIONDATE ACTIONDATE_
--------- --------- ----------- -----------
1 1 01-JAN-2002 01-JAN-2002
1 2 02-JAN-2002 01-JAN-2002
1 1 03-JAN-2002 02-JAN-2002
1 4 04-JAN-2002 03-JAN-2002
1 1 05-JAN-2002 04-JAN-2002
2 1 11-JAN-2002 05-JAN-2002
2 2 12-JAN-2002 11-JAN-2002
2 1 13-JAN-2002 12-JAN-2002
2 3 14-JAN-2002 13-JAN-2002
3 1 15-JAN-2002 14-JAN-2002
4 1 16-JAN-2002 15-JAN-2002
If I discard all the rows where actionID = 1, I get
1 select * from
2 (select userid, actionid, actiondate,
3 first_value(actiondate) over (order by userid, actiondate asc rows 1 preceding)
4 actiondate_prec
5 from table1)
6* where actionid != 1
SQL> /
USERID ACTIONID ACTIONDATE ACTIONDATE_
--------- --------- ----------- -----------
1 2 02-JAN-2002 01-JAN-2002
1 4 04-JAN-2002 03-JAN-2002
2 2 12-JAN-2002 11-JAN-2002
2 3 14-JAN-2002 13-JAN-2002
Add in the mandatory (I think you implied this) actionid = 1, add some aliases and some subtraction and you get:
1 select userid, 1 startaction, endaction, actiondate enddate,
2 actiondate_prec startdate, actiondate-actiondate_prec days_between from
3 (select userid, actionid endaction, actiondate,
4 first_value(actiondate) over (order by userid, actiondate asc rows 1 preceding)
5 actiondate_prec
6 from table1)
7* where endaction != 1
SQL> /
USERID STARTACTION ENDACTION ENDDATE STARTDATE DAYS_BETWEEN
--------- ----------- --------- ----------- ----------- ------------
1 1 2 02-JAN-2002 01-JAN-2002 1.0013889
1 1 4 04-JAN-2002 03-JAN-2002 1.0083333
2 1 2 12-JAN-2002 11-JAN-2002 1.0013889
2 1 3 14-JAN-2002 13-JAN-2002 1.05
Hope this helps.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
