• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 47
  • Last Modified:

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

I have a dataset that looks like this

LoginDate|LoginTime|Secret|IdentityName|Action
1/4/2018|12:01|User1|Secret1|Checkout
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!
0
J C
Asked:
J C
  • 2
  • 2
1 Solution
 
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?
0
 
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
SecretActivity.sql
0
 
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

0
 
J CAuthor Commented:
This is really cool stuff man. Gotta get up to speed on this CTE stuff. Thank you
0
 
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 :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now