asked
Calculate computer usage during time window
I need to calculate the number of minutes that customers have utilized a computer during the 2PM to 4PM time slot. Some of the customers have logged on outside of the window. How do I subtract those times from both the LogInDate and LogOutDate columns the Deleted Bookings Table? I figured out how to filter the window of time using the DATEPART [link text][1]function. [1]: /storage/temp/1560-library+login+logout+calculation.txt
This site works from voting. You should indicate helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution you should indicate this by clicking on the check box next to that answer.
If I understand you correctly, in the case when a customer logged on from 1pm until 5pm, you would want to return 2 hours for that customer as they were logged on for two hours between 2 and 4 (although 4 hours in total). I would use a case statement to determine the latest of (the time portion of LogInDate and 2pm ), and another to determine the earliest of (the time portion of LogOutDate and 4pm), then work out the datediff in mins between them. Note - be careful if customers can be logged in for >1 day, as you need to ensure you account for the date difference.
Thanks Dave_Green. Perhaps a little more background information: Customers can log into a 30 minute, 60 minute or 120 minute computer and not use the full time, i.e. use 12 minutes on either of the 3 types of computers. If the customer logs in at 1202 and logs off at 1402, they would have used 2 minutes during the 2-4 time period window. They could use a 30 minute computer from 1549 to 1619 and use 11 minutes during that time period. I am relatively new at SQL. Would the Case statement look something like: CASE WHEN logindate <2 and logoutdate >4 THEN subtract logindate from logoutdate I could insert the results into a temporary table/table variable for later computation by month/year? Thanks for your help.
Following my previous answer, here's a working version of the model - this only calculates correctly assuming the activity doesn't cross midnight. --Set up test scenario DECLARE @t TABLE (LogInDate DATETIME, LogOutDate DATETIME) INSERT @t VALUES ('2014-05-09T09:40:00','2014-05-09T13:59:00') INSERT @t VALUES ('2014-05-09T09:40:00','2014-05-09T17:59:00') INSERT @t VALUES ('2014-05-09T15:01:00','2014-05-09T17:59:00') INSERT @t VALUES ('2014-05-09T09:40:00','2014-05-09T15:01:00') INSERT @t VALUES ('2014-05-09T16:40:00','2014-05-09T19:01:00') --Set start of window to consider: DECLARE @WindowStart TIME(0) = '14:00', @WindowEnd TIME(0) = '16:00' SELECT YEAR(Logindate) [Year], MONTH(logindate) [Month], CASE --Get rid of periods we don't care about - you may want to do this in the WHERE clause, --but I've included them for clarity. WHEN CONVERT(TIME(0),logindate) > @WindowEnd THEN 0 -- after the period WHEN CONVERT(TIME(0),logoutdate) < @WindowStart THEN 0 -- before the period else --Ones we want: DATEDIFF(mi, CASE WHEN CONVERT(TIME(0),LogInDate) <@WindowStart THEN @windowStart ELSE CONVERT(TIME(0),LogInDate) END ,CASE WHEN CONVERT(TIME(0),LogOutDate) >@WindowEnd THEN @windowEnd ELSE CONVERT(TIME(0),LogOutDate) END ) END AS DurationInWindow ,CASE WHEN CONVERT(TIME(0),LogInDate) <@WindowStart THEN @windowStart ELSE CONVERT(TIME(0),LogInDate) END [EffectiveStartTime] -- selected separately for clarity ,CASE WHEN CONVERT(TIME(0),LogOutDate) >@WindowEnd THEN @windowEnd ELSE CONVERT(TIME(0),LogOutDate) END [EffectiveEndTime] -- selected separately for clarity ,LogInDate,LogOutDate,CONVERT(TIME(0),LogOutDate) -- Note the time conversion as example FROM @t Hope that helps to illustrate.
And you can group by the year and month should you wish to sum the DurationInWindow column, to get total activity in the period...
You can open the Task Manager by right-clicking your computer clock and selecting "Start Task Manager.” When you click the “Performance” tab, you'll see a list of values in the window's System section. The “Up Time” value tells you how many days, hours, minutes and seconds the computer has been running.
Regards,
Will
17 People are following this question.
