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
Solved

Measurements derived from a single table

Posted on 2008-06-10
5
225 Views
Last Modified: 2013-11-16
I have a history table that looks like this:  

hx_id | Visit_id | user_id | Action |  Action_dttm
1000 |  1523    |  1007    |   1       |   2008-06-02 08:00:52
1001 |  1523    |  1007    |   2       |   2008-06-02 08:10:52
1002 |  1523    |  1007    |   3       |   2008-06-02 08:15:52
1003 |  1523    |  1007    |   4       |   2008-06-02 08:30:52
1004 |  1523    |  1007    |   5       |   2008-06-02 08:40:52
1005 |  1523    |  1007    |   6       |   2008-06-02 08:45:52
1006 |  1523    |  1007    |   7       |   2008-06-02 08:50:52
1007 |  1523    |  1007    |   7       |   2008-06-02 08:59:52
1008 |  1523    |  1007    |   8       |   2008-06-02 09:25:52
1009 |  1527    |  1007    |   1       |   2008-06-06 07:30:52
1010 |  1527    |  1007    |   2       |   2008-06-06 07:33:52
1011 |  1527    |  1007    |   3       |   2008-06-06 07:38:52
1012 |  1527    |  1007    |   4       |   2008-06-06 07:42:52
1013 |  1527    |  1007    |   5       |   2008-06-06 08:01:52
1014 |  1527    |  1007    |   6       |   2008-06-06 08:15:52
1015 |  1527    |  1007    |   7       |   2008-06-06 08:50:52
1016 |  1527    |  1007    |   8       |   2008-06-06 08:55:52
1017 |  1541    |  1007    |   1       |   2008-06-09 06:50:52
1018 |  1541    |  1007    |   2       |   2008-06-09 07:03:52
1019 |  1541    |  1007    |   3       |   2008-06-09 07:40:52
1020 |  1541    |  1007    |   4       |   2008-06-09 08:00:52





so for a given visit, I will get a list of actions and the dattime of the actions...

for a givien user I need to pull metrics for each visit  into a table like so:

Visit_id | datetime for action where action = 1| dttm for when action = 2 | dttm for when action = 3 | dttm for when action = 4 |  and so on


 
0
Comment
Question by:the_b1ackfox
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749036
the problem is the "and so on".
unless you have a fixed maximum number of actions, OR want to use 1 field for that output, you cannot do it.
if the number of actions is know and fixed max, check out the PIVOT syntax.
if you want the output in 1 field, you can do so with a user-defined function.
otherwise, you have to do this in the client application code.
0
 
LVL 9

Author Comment

by:the_b1ackfox
ID: 21749089
there are a fixed number of 'and so ons'   ...  I am checking out the pivot syntax now
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21749167
here is the tech reference:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
tell me if you need help with the syntax.
0
 
LVL 9

Author Comment

by:the_b1ackfox
ID: 21753862
Angellll:  Having been cursed with a puzzle solvers complex, I went to bed last night with this issue in my head.  After having all sorts of weird dreams about putting SQL queries together, I woke up with an idea on how to solve my issue...   I tested it this morning, and it seems to work, although I suspect this query is going to run long and slow.  I am certain that there may be a better way to do this, but let me know what you think:  (and yes, I still have some issues to iron out)

select  distinct pvh.user_id, pvh.visit_id, pvh.action_dttm as [measure 1], q2.action_dttm as [Measure 2] ,
q3.action_dttm as [measure 3], q4.action_dttm as [measure 4], q5.action_dttm as [Measure 5],
q6.action_dttm as [measure 6], q7.action_dttm as [measure 7], q8.action_dttm as [measure 8]
from w_history pvh
inner join (select visit_id, action_dttm from w_history where action_typ_cd=2) Q2 on q2.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=4) Q3 on q3.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=11) Q4 on q4.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=7) Q5 on q5.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=1) Q6 on q6.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=14) Q7 on q7.visit_id=pvh.visit_id
inner join (select visit_id, action_dttm from w_history where action_typ_cd=15) Q8 on q8.visit_id=pvh.visit_id

where pvh.user_id='456.17368'
and (pvh.action_typ_cd='12')
and pvh.action_dttm=(select max(action_dttm) from  w_history where visit_id=pvh.visit_id and action_typ_cd='12')

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21754112
>although I suspect this query is going to run long and slow
yes, indeed.

with "pure" SQL (ie no PIVOT):
select  pvh.user_id
, pvh.visit_id
, MAX(CASE WHEN action_typ_cd=12 THEN pvh.action_dttm END) as [measure 1]
, MAX(CASE WHEN action_typ_cd=2 THEN pvh.action_dttm END) as [measure 2]
, MAX(CASE WHEN action_typ_cd=4 THEN pvh.action_dttm END) as [measure 3]
, MAX(CASE WHEN action_typ_cd=11 THEN pvh.action_dttm END) as [measure 4]
, MAX(CASE WHEN action_typ_cd=7 THEN pvh.action_dttm END) as [measure 5]
, MAX(CASE WHEN action_typ_cd=1 THEN pvh.action_dttm END) as [measure 6]
, MAX(CASE WHEN action_typ_cd=14 THEN pvh.action_dttm END) as [measure 7]
, MAX(CASE WHEN action_typ_cd=15 THEN pvh.action_dttm END) as [measure 8]
from w_history pvh
where pvh.user_id='456.17368'
and pvh.action_typ_cd in (1,2,4,7,11,12,14,15)
GROUP BY pvh.user_id ,  pvh.visit_id

Open in new window

0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

How to increase the row limit in Jasper Server.
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

856 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