Solved

Measurements derived from a single table

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to print the sql query 18 51
What is the best way to calculate hours worked 5 61
Truncate vs Delete 63 102
Anyway to make these 2 SQL statements into one? 13 25
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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 …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

14 Experts available now in Live!

Get 1:1 Help Now