Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Measurements derived from a single table

Posted on 2008-06-10
5
Medium Priority
?
243 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
[X]
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
  • 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 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

636 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