Requesting assistance with how approach on building an SQL Query to return desired result

I have a dataset that looks like this

1/4/2018|12:04|User1| Secret1|Checkin
1/4/2018|12:15|User2| Secret1|Checkout
1/4/2018|12:16|User2| Secret1|Checkin
1/4/2018|12:17|User3| Secret1|Checkout
1/4/2018|12:45|User3| Secret1|Checkin

What I need to be able to do is to build a query/queries that will provide actionable data showing when multiple users are checking out a secret within a certain time frame.

So if within a single hour, more than one user has checked out a particular secret  I need to know the time between when user1 checked out Secret 1 and user 2 checked it out.

I will then create a report that shows the percentage to show the contrast between when this scenario is met against the activity we see on a normal basis.

Any help as to an approach would be much appreciated!
J CAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
If you post usable data -- CREATE TABLE with INSERT statement(s) -- I'll try to code something up.

Btw, when you have 3 users overlap, as you did here, what results do you expect to see?
J CAuthor Commented:
Sure thing, attached is the script.

Trying to answer your question...,

The scenario I am trying to capture is when a user is checking out a secret and checking it in immediately, which is most likely to allow additional users to check it out and use the secret simultaneously.

The approach I considered but am not sure if it's the best one is to have both the check in and checkout for a specific user/secret pivoted so that instead of two rows to represent both the check in and check out, there would be a single row.

Then to potentially use a CTE or whatever approach is best to capture the time span between when one user checks out a secret and the next user checks the same secret out in a column.

I'm not sure the best way to approach this and that is why I posted. I need to be able to identify these scenarios and to compare the frequency of this scenario to the normal activity I see.

I hope this helps make things clearer.

Thanks for taking a look
Mark WillsTopic AdvisorCommented:
How about something like :
; with CTE_Checkouts as
  select S.Logindate, s.[secret], s.[identity], s.[action], abs(datediff(minute,s.logindate,a.logindate)) as minutes_between, a.[Identity] as Other_Identity
  from SecretActivity s
  outer apply (select a.logindate, a.[secret], a.[identity] from SecretActivity a where s.[secret] = a.[secret] and s.[identity] <> a.[identity] and abs(datediff(minute,a.logindate,s.logindate)) <61 and a.[action] = 'Checkout') a
  where s.[Action] = 'Checkin'
) select * from CTE_Checkouts   

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J CAuthor Commented:
This is really cool stuff man. Gotta get up to speed on this CTE stuff. Thank you
Mark WillsTopic AdvisorCommented:
Common Table Expressions are pretty cool. Not to different from  derived table, but, you can do a lot more with CTE's (such as recursive queries).

Need to always assess when to use - I see a lot of CTE's being used where sometimes a straight query would work.

In the case of the above CTE, you probably dont need the CTE because the real strength is in the APPLY.

Where it is really beneficial is derived columns (like row_number or aggregates / window functions) so you can use the derived column in the rest of your query. And, you can embed CTE's....

Yep, CTE's are really cool in my opinion :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.