October 7, 2017 at 5:23 am
Hello.
I am trying to develop the concept of a report of utilization of barcode scanning devices over time, and just looking for pointers and suggestions for SQL techniques.
Assuming that when a Device scans a barcode it generates a record that contains at least 2 fields, the Device Name and ScanDateTime, and these records are uploaded into a SQL database as the source data for the query.
It seems to me that what I want to create is something that looks like a Gantt Chart where I have a Row for each Device and a column for the Time period.
If my timescale was days and I wanted to look at the last 30 days, conceptually, what I want to do is to plot an 'X' (or a 1 or anything really) for each device that got used that day (i.e. indicating that there is at least 1 ScanDateTime record for that device for that day).
Realistically, what I would want to do is also to plot usage by hours of the day (probably limited to the hours of 6AM-6PM).
I have seen references to using SSRS for creating Gantt charts, but don't want to spend the time getting my head around that as well having looked at it briefly in the past.
Any input welcome - thanks.
Steve O.
October 7, 2017 at 6:02 am
Something along these lines I guess
http://www.sqlservercentral.com/scripts/Jobs/66476/
Just need to get my head around it - seems to produce a report for the previous 24 hours.
Steve O.
October 9, 2017 at 6:44 am
But do you understand how that works? I have to assume that if you understood it, you'd have already tried it. I doubt that understanding that query you linked to is any less complex than creating a very simple chart that shows device usage over time. The question is what; exactly; do you want to see? If you've ever used MS Project, then you know what a timeline looks like, and you could easily have one axis of a chart represent time, and the other be some kind of measure of "quantity of use". Typically, that would sum any usage quantity you have over whatever granularity of time you want. Typically, data warehouses create calendar tables that they name DimDate or DimTime or DimDateTime, depending on the time granularity of their measurement needs. Once you select the data at the necessary grouping level, which will be the granularity of your "time dimension", then you can create a pretty useful chart pretty quickly.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 14, 2017 at 7:42 am
sgmunson - Monday, October 9, 2017 6:44 AMBut do you understand how that works? I have to assume that if you understood it, you'd have already tried it. I doubt that understanding that query you linked to is any less complex than creating a very simple chart that shows device usage over time. The question is what; exactly; do you want to see? If you've ever used MS Project, then you know what a timeline looks like, and you could easily have one axis of a chart represent time, and the other be some kind of measure of "quantity of use". Typically, that would sum any usage quantity you have over whatever granularity of time you want. Typically, data warehouses create calendar tables that they name DimDate or DimTime or DimDateTime, depending on the time granularity of their measurement needs. Once you select the data at the necessary grouping level, which will be the granularity of your "time dimension", then you can create a pretty useful chart pretty quickly.
Hello Steve.
Thanks for the reply - the Gantt chart simile was based on use of MS Project (and PMW before that), so yes, I am looking at charting usage over time.
I guess what I am asking for are any pointers to sample code that do this sort of thing as that is how I learn, never having been a Developer/Programmer and the link I posted was what I came across in a brief search.
The reality is that I am trying to demonstrate that expensive resources (Mobile devices) are poorly distributed and underutilized.
I have to date been able to write queries to locate where a device was last allocated (by determining who was logged on to it when it last communicated), and then extrapolated this information to determine how long ago it was last used (using the MAX function on date) .
There is one issue with this, but since it has no solution I am not pursuing that anomaly and the accuracy of the data is within acceptable limits.
This code is encapsulated into an Excel spreadsheet and updates in real time by pressing the Data Refresh button.
These were Phases 1 and 2 and now I am moving on to Phase 3.
I am aware that even where a device is locatable and has communicated recently, these devices are over-allocated or mis-allocated in regards to concurrent usage requirements.
So what I want to demonstrate (and a graph is the simplest way to communicate this) is where usage of devices does not overlap, and that they are used in a round Robin fashion and taken randomly from a pool and used only briefly.
I am pretty sure that I have all of data to do this (by joining tables correctly) I am just struggling with my SQL as I have not done something like this before.
Regards
Steve O.
October 14, 2017 at 10:29 am
SteveOC - Saturday, October 7, 2017 5:23 AMHello.I am trying to develop the concept of a report of utilization of barcode scanning devices over time, and just looking for pointers and suggestions for SQL techniques.
Assuming that when a Device scans a barcode it generates a record that contains at least 2 fields, the Device Name and ScanDateTime, and these records are uploaded into a SQL database as the source data for the query.
It seems to me that what I want to create is something that looks like a Gantt Chart where I have a Row for each Device and a column for the Time period.
If my timescale was days and I wanted to look at the last 30 days, conceptually, what I want to do is to plot an 'X' (or a 1 or anything really) for each device that got used that day (i.e. indicating that there is at least 1 ScanDateTime record for that device for that day).
Realistically, what I would want to do is also to plot usage by hours of the day (probably limited to the hours of 6AM-6PM).
I have seen references to using SSRS for creating Gantt charts, but don't want to spend the time getting my head around that as well having looked at it briefly in the past.
Any input welcome - thanks.
Steve O.
This is actually pretty easy to do. If you'd post some readily consumable data (see the first link in my signature line below for how to do that), I believe I could help here.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 15, 2017 at 8:43 am
Jeff Moden - Saturday, October 14, 2017 10:29 AMSteveOC - Saturday, October 7, 2017 5:23 AMHello.I am trying to develop the concept of a report of utilization of barcode scanning devices over time, and just looking for pointers and suggestions for SQL techniques.
Assuming that when a Device scans a barcode it generates a record that contains at least 2 fields, the Device Name and ScanDateTime, and these records are uploaded into a SQL database as the source data for the query.
It seems to me that what I want to create is something that looks like a Gantt Chart where I have a Row for each Device and a column for the Time period.
If my timescale was days and I wanted to look at the last 30 days, conceptually, what I want to do is to plot an 'X' (or a 1 or anything really) for each device that got used that day (i.e. indicating that there is at least 1 ScanDateTime record for that device for that day).
Realistically, what I would want to do is also to plot usage by hours of the day (probably limited to the hours of 6AM-6PM).
I have seen references to using SSRS for creating Gantt charts, but don't want to spend the time getting my head around that as well having looked at it briefly in the past.
Any input welcome - thanks.
Steve O.
This is actually pretty easy to do. If you'd post some readily consumable data (see the first link in my signature line below for how to do that), I believe I could help here.
Thanks for the reply Jeff.
In trying to formulate a response based on the Link in your sig, and working out the table Creates, I realise that this is more complicated than I thought, over and above normalisation.
In reality there are multiple tables holding the Detail records that contain a Scan Date and Time, dependent on the function that the user was in on the device when scanning.
In each case I can link back to a Session header record in another table, and from there link to a device name in yet another table based on device id.
I think that I am going to have to work out how to pull records from them all into a Temp table based on a time window (6AM-6PM), complete with the session and device information, and
I believe that I can do this myself based on previous learning.
I guess I just need help with understanding how to group or window within that result set based on device ID and Scan Date and Time, and for simplicity I guess my Temp table would only need to consist of the following 2 columns :
DeviceName NVARCHAR(15)
ScanDTime DATETIME
So my Temp table data would look like this I think
DeviceName ScanDTime
Scanner1 2017-10-01 00:06:01.000
Scanner1 2017-10-01 00:06:09.000
Scanner2 2017-10-01 00:07:32.000
Scanner1 2017-10-01 00:07:35.000
Scanner3 2017-10-01 00:07:35.000
Scanner2 2017-10-01 00:07:36.000
and I would want to output something to show that only 1 scanner was used between 6-7 AM , whereas 3 scanners were in concurrent use between 7-8 AM, but rather than a count of concurrent use I would like to plot usage of specific device against time.
I will proceed with the notion of generating a Temp table and can then perhaps provide meaningful data if the above is insufficient.
Thanks
Steve O.
October 15, 2017 at 1:01 pm
SteveOC - Sunday, October 15, 2017 8:43 AMJeff Moden - Saturday, October 14, 2017 10:29 AMSteveOC - Saturday, October 7, 2017 5:23 AMHello.I am trying to develop the concept of a report of utilization of barcode scanning devices over time, and just looking for pointers and suggestions for SQL techniques.
Assuming that when a Device scans a barcode it generates a record that contains at least 2 fields, the Device Name and ScanDateTime, and these records are uploaded into a SQL database as the source data for the query.
It seems to me that what I want to create is something that looks like a Gantt Chart where I have a Row for each Device and a column for the Time period.
If my timescale was days and I wanted to look at the last 30 days, conceptually, what I want to do is to plot an 'X' (or a 1 or anything really) for each device that got used that day (i.e. indicating that there is at least 1 ScanDateTime record for that device for that day).
Realistically, what I would want to do is also to plot usage by hours of the day (probably limited to the hours of 6AM-6PM).
I have seen references to using SSRS for creating Gantt charts, but don't want to spend the time getting my head around that as well having looked at it briefly in the past.
Any input welcome - thanks.
Steve O.
This is actually pretty easy to do. If you'd post some readily consumable data (see the first link in my signature line below for how to do that), I believe I could help here.
Thanks for the reply Jeff.
In trying to formulate a response based on the Link in your sig, and working out the table Creates, I realise that this is more complicated than I thought, over and above normalisation.
In reality there are multiple tables holding the Detail records that contain a Scan Date and Time, dependent on the function that the user was in on the device when scanning.
In each case I can link back to a Session header record in another table, and from there link to a device name in yet another table based on device id.I think that I am going to have to work out how to pull records from them all into a Temp table based on a time window (6AM-6PM), complete with the session and device information, and
I believe that I can do this myself based on previous learning.I guess I just need help with understanding how to group or window within that result set based on device ID and Scan Date and Time, and for simplicity I guess my Temp table would only need to consist of the following 2 columns :
DeviceName NVARCHAR(15)
ScanDTime DATETIMESo my Temp table data would look like this I think
DeviceName ScanDTime
Scanner1 2017-10-01 00:06:01.000
Scanner1 2017-10-01 00:06:09.000
Scanner2 2017-10-01 00:07:32.000
Scanner1 2017-10-01 00:07:35.000
Scanner3 2017-10-01 00:07:35.000
Scanner2 2017-10-01 00:07:36.000and I would want to output something to show that only 1 scanner was used between 6-7 AM , whereas 3 scanners were in concurrent use between 7-8 AM, but rather than a count of concurrent use I would like to plot usage of specific device against time.
I will proceed with the notion of generating a Temp table and can then perhaps provide meaningful data if the above is insufficient.
Thanks
Steve O.
Even if you generate artificial data for demo purposes, it would be real handy if you'd write it so that it actually dumps to a table instead of me having to do it. You're one person... I have to help many. Help me help you by providing the data in a readily consumable format. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply