Solved

Measurements derived from a single table

Posted on 2008-06-10
5
194 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 142

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 142

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 142

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

11 Experts available now in Live!

Get 1:1 Help Now