asked
Need help with a unit utilization query
Our firm deploys tablets to our customers to run an embedded application. I have a table that contains a date/time on a unit, date/time off a unit, & unit number. The goal is to get the peek unit utilization (simultaneous units being used), average unit utilization, and produce a SSRS report with a chart of unit utilization over time. This data will help to see if we need to deploy more tablets to a customer. I am kind of a SQL newbie, but I don't need code help, just concept / method help Thanks
I would group the data into periods for analysis. Intervals of 15 minutes perhaps. Build a TimeOfDay table that has a row for every interval in a 24 hour day with a start datetime, an end datetime, and a string description. Then you could join your table to the TimeOfDay table by comparing the time parts of the on and off datetimes with the start and end times in the TimeOfDay table. You'd end up with date, time of day interval description, unit, unit number. You could filter and slice that whichever way you like.
18 People are following this question.
