Query Help

Question

Query Help

Alex Chavez

SSChasing Mays

Points: 621
More actions
February 1, 2010 at 11:42 am

#136998

Hello everyone,
I'm having a issue comparing 1 row to another specific row. What I'm trying to accomplish is this, I need to see if a specific owner has completed a View/Final on a specific document ID before viewing another. And get a percentage of how many times they actually completed a view/final before viewing/finaling another document. For example the first 2 rows are correct since there was a viewed/final before the owner viwed another document at 9:01, but then at 9:26 he viewed two and didn't complete the process in the correct order.
Sorry if my explanation is just horrible.
My columns are
Event_Order DT EntryIDEncounter Person File Action Owner EventDTTM
1200908122183270191682 9869272Viewed1818/12/09 8:27
2200908121183270191682 9869272Final 1818/12/09 8:43
3200908122199252071726539869303Viewed2558/12/09 8:47
4200908121199252071726539869303Final 2558/12/09 9:00
5200908122199426063909049869667Viewed1818/12/09 9:01
6200908121199426063909049869667Final 1818/12/09 9:12
7200908122199447613800799870563Viewed1818/12/09 9:18
82009081222032408111107839870797Viewed1818/12/09 9:26
9200908121199447613800799870563Final 1818/12/09 9:42
10200908122199500382887989872043Viewed1818/12/09 10:22

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

ugg; i didn't even look at the question, just tried to create the data in a ready to consume format;

help us help you! in the future, try to give everyone the data like this so we can test some solution with you; it makes it easier on everyone who is volunteering their tiem to help.

here you go!

DECLARE @MyExample TABLE(

Event_Order int,

DT datetime,

EntryID int,

Encounter int,

Person int,

[File] int,

Action varchar(30),

Owner int,

EventDTTM datetime

)

INSERT INTO @MyExample

SELECT 1, '20090812','2','18327019', '1682','9869272','Viewed','181','8/12/09 8:27' UNION ALL

SELECT 2, '20090812','1','18327019', '1682','9869272', 'Final','181','8/12/09 8:43' UNION ALL

SELECT 3, '20090812','2','19925207', '172653','9869303','Viewed','255','8/12/09 8:47' UNION ALL

SELECT 4, '20090812','1','19925207', '172653','9869303', 'Final','255','8/12/09 9:00' UNION ALL

SELECT 5, '20090812','2','19942606', '390904','9869667','Viewed','181','8/12/09 9:01' UNION ALL

SELECT 6, '20090812','1','19942606', '390904','9869667', 'Final','181','8/12/09 9:12' UNION ALL

SELECT 7, '20090812','2','19944761', '380079','9870563','Viewed','181','8/12/09 9:18' UNION ALL

SELECT 8, '20090812','2','20324081','1110783','9870797','Viewed','181','8/12/09 9:26' UNION ALL

SELECT 9, '20090812','1','19944761', '380079','9870563', 'Final','181','8/12/09 9:42' UNION ALL

SELECT 10,'20090812','2','19950038', '288798','9872043','Viewed','181','8/12/09 10:22'

SELECT * FROM @MyExample

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alex Chavez SSChasing Mays Points: 621 More actions · Answer 2

Thank you. I never thought of presenting it that way. I'll use that in the future whenever I post.

Revenant SSC-Forever Points: 42467 More actions · Answer 3

Using Lowell's code, I think that the query you are looking for is

[font="Courier New"]SELECT * FROM @MyExample a

WHERE [Action] = 'Viewed'

AND ( SELECT [Action] FROM @MyExample b WHERE b.Event_Order = a.Event_Order + 1 )

<> 'Final'[/font]

Alex Chavez SSChasing Mays Points: 621 More actions · Answer 4

Doesn't quite work. a Owner may View document 100 and than final document 102 and that throws off the query or if he views 100, 101, finals 100, views 102, finals 101 that throws it off as well.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 5

We also need to see the expected results - but, you can start with this and see if I am close.

SELECT *

FROM @MyExample r1

LEFT JOIN @MyExample r2 ON r2.Event_Order = r1.Event_Order + 1

AND r2.Encounter = r1.Encounter

WHERE r1.EntryID = 2;

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs