Solved

Measurements derived from a single table

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
In this article I will describe the Copy Database Wizard 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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 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