July 21, 2022 at 10:00 pm
Hello.
Some time ago I was looking for help in plotting concurrent usage of PDA devices based on usage and communiication dates. I never got the full solution in SQL but got so far and then managed to use Excel to pivot the data to get what I wanted. Intention was to revisit when I got my head around SQL Pivot.
Unfortunately our vendor has made a change which kind of breaks the code pretty solidly. The code is reliant on the Device Name being in the Session Header and with a New Android Application the Headers in the session do not contain any Device information - in fact a couple of them actually do - but only 2 out of 7 or so.
There is now a separate table (intended for usage for usage/licencing based on a New charging model) that I can get information from that includes the device name and a Userid (the user logged in to the App when it communicated). There will be multiple rows for any device if it communicates multiple times in a geiven day. However this table does not contain any reference to a communication session. What is common is a Userid - but a User can be signed in to multiple devices (in theory) or may pick up different devices throughout the day.
So I can determine that a specific device communicated on a given day and also determine the date a device last communicated - which are 2 things I need to determine - but that table does not give me a breakdown of concurrent usage in a day because that requires analysis of the session detail data and for it to be related to a device..
I have raised this with the vendor - although the logical resolution would be a change to the DB requiring an upgrade if anything is forthcoming which would be problematic.
The only thing I could come up with meanwhile was trying to correlate the Session Detail record timestamps frm 1 table with the Device Usage Communication timestamps from the 2nd table based on Userid and the sequence of the timestamps - i,e, the Usage timestamp is the first one that immediately follows the timestamps in the detail,
So I have a bunch of session detail records in table A with a range of timestamps and I want to associate these with the first device communication session in the other table that occurs immediately after the last session detail timestamp based on the common Userid and the timestamp in the device usage table. If I pick the right device usage session it will tell me the DeviceName - or at least a best guess if you can see the scope for error in this technique.
Table 2 contains 3 columns - Date, DeviceID and Userid - and there can be multiple entries for any given date for any given Userid and any given device and I want to map the correct row to the session detail records that precede that datetime.
I am sure that makes little sense but it anybody follows what I mean and wants to suggest a technique fro me to read up on please do. At the moment I am calling it 'windowing' for want of a better word but that may be incorrect.
July 22, 2022 at 2:24 am
Any chance you could mock up some fake data that represents the situation you're trying to model and query? And an expected query result? It's hard to know how to help without it.
July 29, 2022 at 5:53 pm
Hello and thanks for the response (for some reason I am not getting notifys).
I had another think and tried varous new ways to construct a search in Google and eventually came up with this which describes what I am trying to do - so I am going to look at trying the 2 suggestions.
If links are not permitted please delete but it describes the situation most succinctly.
If I have no joy I will do as suggested and post more info along the lines of the link.
Regards
Steve O.
July 29, 2022 at 7:46 pm
Hello and thanks for the response (for some reason I am not getting notifys).
I had another think and tried varous new ways to construct a search in Google and eventually came up with this which describes what I am trying to do - so I am going to look at trying the 2 suggestions.
If links are not permitted please delete but it describes the situation most succinctly.
If I have no joy I will do as suggested and post more info along the lines of the link.
Regards
Steve O.
Links are permitted but the data isn't "Readily Consumable" on that link (and "Readily Consumable" data would earn you high praise on Stack Overflow, as well). Please see the first link in my signature line below for what I mean by that and one of a couple of ways to gen the code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply