Jim Posted January 2 Share Posted January 2 Hi, I have a report that links the requests, activities, and page visits together in a fashion, I have ended up with some duplicate results on activities against each page visit which I would ideally like to remove, I understand it happens as the page as visited multiple times and the same activities match the join criteria over and over, it's not awful but not ideal and I was wondering if anyone with a better knowledge on sql could help me with this? I was thinking about only joining based on an update being done within 1 hour of the request being viewed, however this is not ideal as many analysts leave tabs open and go back to them after this time so I would miss activities with that criteria Link to comment Share on other sites More sharing options...
Jim Posted January 2 Author Share Posted January 2 Ideally I would like to join the activity to the nearest time stamp of the page being visited, and not all the page visits that came prior also testing-footprints.report.txt Link to comment Share on other sites More sharing options...
Jim Posted January 4 Author Share Posted January 4 I have managed to resole this myself with some sub query magic 1 Link to comment Share on other sites More sharing options...
Jim Posted January 4 Author Share Posted January 4 When I run this for a month period it returns no results, I have a hunch it is timing out, is anyone able to assist with making the query more efficient? testing-footprints---final---validate.report (2).txt Link to comment Share on other sites More sharing options...
Emily Patrick Posted January 29 Share Posted January 29 On 04/01/2024 at 11:27, Jim said: I have managed to resole this myself with some sub query magic @Jim How did you manage to remove the duplicate entries? I have a similar issue, but with FAQ's. It's listing each entry twice, and looking to get the duplicate removed as it will be emailed as a scheduled report and don't want to have to explain to the recipients that they need to remove a line every other entry. Link to comment Share on other sites More sharing options...
Jim Posted January 29 Author Share Posted January 29 testing-footprints---final---validate.report (3).txtI managed to remove duplicates by using a sub query - this is very inefficient though and so far I have only been able to use it on a day by day basis per user unfortunately so not all I hoped it to be Link to comment Share on other sites More sharing options...
Jim Posted January 29 Author Share Posted January 29 With your's being FAQ's you would have to rework the query to look at the right table but as I say, it's very inefficient, What is your actual use case? maybe there is a simpler way with your problem? Link to comment Share on other sites More sharing options...
Emily Patrick Posted January 29 Share Posted January 29 Thanks Jim, I'll take a look at what you posted. Trying to populate a list of the FAQ's we have that are published to try and make it easier for a support team to use our 'knowledge base' for the time being. I can get them to show, but it likes to add it in twice for some reason! Just looking for a single list of FAQ's. Link to comment Share on other sites More sharing options...
Jim Posted January 29 Author Share Posted January 29 Could you attach the report definition and I will have a look for you Link to comment Share on other sites More sharing options...
Emily Patrick Posted January 29 Share Posted January 29 I've just sent it as a message to you Jim. Thanks for taking a look Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now