I have log data and I'm trying to back-fill the data as much as possible to help improve analytics.
The log data contains a SessionId, which is the SessionId created by the browser, the Name of the logged in user (if they are logged in) and a LogTime.
I'm trying to get all the related sessions, sessions that are within 24 hours of each other, and get the first date of that group of sessions, the last date of that group of sessions and populate the first not null and not empty name into all the other name spaces.
For instance, if I had the following data:
--Id SessionId Name LogTime
--1 1 2018-01-01 00:00
--2 1 LargeOne 2018-01-01 12:00
--3 2 Two 2018-01-01 13:00
--4 3 NULL 2018-01-02 00:00
--5 3 2018-01-03 00:00
--6 1 One 2018-01-03 00:00
--7 2 2018-01-03 00:00
--8 2 LargeTwo 2018-01-04 00:00
--9 1 2018-01-04 00:00
I would like to process the data as follows:
--Id SessionId Name LogTime StartTime EndTime
--1 1 LargeOne 2018-01-01 00:00 2018-01-01 00:00 2018-01-01 12:00
--2 1 LargeOne 2018-01-01 12:00 2018-01-01 00:00 2018-01-01 12:00
--3 2 Two 2018-01-01 13:00 2018-01-01 13:00 2018-01-01 13:00
--4 3 NULL 2018-01-02 00:00 2018-01-02 00:00 2018-01-03 00:00
--5 3 NULL 2018-01-03 00:00 2018-01-02 00:00 2018-01-03 00:00
--6 1 One 2018-01-03 00:00 2018-01-03 00:00 2018-01-04 00:00
--7 2 LargeTwo 2018-01-03 00:00 2018-01-03 00:00 2018-01-04 00:00
--8 2 LargeTwo 2018-01-04 00:00 2018-01-03 00:00 2018-01-04 00:00
--9 1 One 2018-01-04 00:00 2018-01-03 00:00 2018-01-04 00:00
Ids 1 and 2 are in the same session and in range (24 hours) of each other so they make one set, notice that the Id 1 doesn't have a name column but Id 2 does and because it's part of the same set, it backfills the name. Ids 6 and 9 are also in session 1 but is not in the 24 hour range of the first set so it makes a new set, Ids 6 and 9 are both in session 1 and even though new sessions appear between them, they are still the same session within range so they make a new set.
I've attached the full question and what I've tried as a txt file.
Hello Sir,
I have not found any attached file but i understand the requirement. I can do this task within a day.
We have 6+ years experience in PHP, MySQL and Website Designing.
Please enable chat for more details.
regards:
Autarkic Infotech
Hi I am a software engineer with a strong expertise in sql. I write queries, stored procedures and reports using SSMS and SSRS everyday at my day job. Let’s talk more about what all you have and what you need to so I can get started for you.