J C
asked on
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 |IdentityN ame|Action
1/4/2018|12:01|User1|Secre t1|Checkou t
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!
LoginDate|LoginTime|Secret
1/4/2018|12:01|User1|Secre
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!
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is really cool stuff man. Gotta get up to speed on this CTE stuff. Thank you
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 :)
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 :)
Btw, when you have 3 users overlap, as you did here, what results do you expect to see?