August 11, 2016 at 11:57 am
How can i get the total time a user spent on the system from a table as shown below, i tried the DML below and got an error, any ideas
Table Structure
TransactionID int PK
OrderID int not null
UserID nvarchar(30) null
ActionType nvarchar(90) null
LogDateTime datetime null
DATA structure
136698931XXX Display2015-02-26 11:22:20.000
136699931XXX Display2015-02-26 11:23:20.000
136700931XXX Display2015-02-26 12:24:20.000
136701931XXX Save 2015-02-26 12:25:20.000
SELECT UserID, Hours = SUM(DATEDIFF(MINUTE, '0:00:00', LogDatetime))
FROM dbo.TLog
where OrderId = '1' and USERID = 'xxx'
GROUP BY UserId;
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
August 11, 2016 at 12:02 pm
You're adding too many minutes. If that's correct, then cast as bigint before aggregating. If not, correct the formula.
SELECT UserID, Hours = SUM(CAST( DATEDIFF(MINUTE, '0:00:00', LogDatetime) AS bigint))
FROM dbo.TLog
where OrderId = 931 and USERID = 'xxx'
GROUP BY UserId;
August 11, 2016 at 12:29 pm
Data
137256931DisplayXXX2015-03-05 12:24:54.000
137257931DisplayXXX2015-03-05 12:43:04.000
137258931DisplayXXX2015-03-05 12:46:13.000
137358931DisplayXXX2015-03-05 15:03:18.000
137435931DisplayXXX2015-03-06 11:28:01.000
137437931DisplayXXX2015-03-06 11:40:48.000
137438931DisplayXXX2015-03-06 11:58:33.000
137441931DisplayXXX2015-03-06 12:01:17.000
137445931DisplayXXX2015-03-06 12:12:24.000
137448931DisplayXXX2015-03-06 12:15:57.000
137454931DisplayXXX2015-03-06 12:17:25.000
155638931DisplayXXX2016-02-17 12:48:40.000
155639931DisplayXXX2016-02-17 12:52:01.000
155640931Save XXX2016-02-17 12:52:50.000
155641931Save XXX2016-02-17 12:52:50.000
155642931DisplayXXX2016-02-17 12:52:50.000
155643931DisplayXXX2016-02-17 12:53:13.000
155644931DisplayXXX2016-02-17 12:55:27.000
155645931DisplayXXX2016-02-17 12:55:29.000
155646931Save XXX2016-02-17 12:56:15.000
155647931Save XXX2016-02-17 12:56:15.000
155648931DisplayXXX2016-02-17 12:56:15.000
155649931DisplayXXX2016-02-17 12:56:43.000
155650931DisplayXXX2016-02-17 12:57:52.000
155651931DisplayXXX2016-02-17 12:58:56.000
155652931DisplayXXX2016-02-17 12:59:10.000
155653931DisplayXXX2016-02-17 12:59:15.000
155654931DisplayXXX2016-02-17 12:59:40.000
155655931DisplayXXX2016-02-17 13:00:21.000
155656931DisplayXXX2016-02-17 13:00:49.000
155664931DisplayXXX2016-02-17 13:24:42.000
The query below
SELECT UserID, Hours = SUM(DATEDIFF(MINUTE, '0:00:00', LogDatetime))
FROM dbo.TLog
where OrderId = '1' and USERID = 'xxx'
GROUP BY UserId;
Result For Minute
XXX2311979115
Result For Hour
XXX38532962 which makes no sense as i just want to figure out how much time they spent, i.e. total time on the system in a way that is readable and presentable
perhaps the table structure is the problem
August 11, 2016 at 12:37 pm
SQLTestUser (8/11/2016)
Result For Hour
XXX38532962 which makes no sense as i just want to figure out how much time they spent, i.e. total time on the system in a way that is readable and presentable
perhaps the table structure is the problem
Take a step back. How do you calculate the time spent? When did they got in and when did they got out?
Right now, you're calculating the minutes from 1900-01-01 00:00:00 to the LogDateTime.
Once you can calculate that, we can get to presentation options.
August 11, 2016 at 12:40 pm
SQLTestUser (8/11/2016)
SELECT UserID, Hours = SUM(DATEDIFF(MINUTE, '0:00:00', LogDatetime))
It's because of the '0:00:00' you have in the datediff. With no date specified, that becomes 1900-01-01 00:00:00.000, which is probably not what you wanted.
What should the logdatetime be subtracted from?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2016 at 11:21 am
From the two time periods on Logdatetime itself
August 15, 2016 at 2:28 pm
SQLTestUser (8/15/2016)
From the two time periods on Logdatetime itself
What two time periods? There's only one datetime per row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 16, 2016 at 9:42 am
SQLTestUser (8/15/2016)
From the two time periods on Logdatetime itself
Sorry, but this doesn't make much sense.
I will have to take a wild guess.
For your purposes it would have been best if there was an action for LogOn and an action for LogOff. Then you could simply say, that the time used on your system was the time between those two log entries.
But no such actions exist in your example data.
So I'm assuming that you want to calculate the time betwen the first and last log entries per order and sum up that per user:
WITH
-- Find the first and last log entry per user/date/order...
UserOrderDayLog AS (
SELECT UserID, OrderId, CAST(DATEDIFF(d,0,LogDateTime) AS DATETIME) AS LogDate, MIN(LogDatetime) AS FirstLogDateTime, MAX(LogDatetime) AS LastLogDateTime
FROM dbo.Tlog
GROUP BY UserId, OrderId, CAST(DATEDIFF(d,0,LogDateTime) AS DATETIME)),
-- Subtract the first log time from the last one...
UserOrderDaylogSeconds AS (
SELECT UserID, OrderId, LogDate, DATEDIFF(s,FirstLogDateTime,LastLogDateTime) AS EstimatedSystemTimeInSeconds
FROM UserOrderDayLog),
-- Sum the time intervals per user
UserSystemTime AS (
SELECT UserId, SUM(EstimatedSystemTimeInSeconds) AS EstimatedTotalSystemTimeInSeconds
FROM UserOrderDayLogSeconds
GROUP BY UserId)
-- Show the user results as seconds and as minutes, listed in descending order based on used system time..
SELECT UserId, EstimatedTotalSystemTimeInSeconds, FLOOR(EstimatedTotalSystemTimeInSeconds / 60) AS EstimatedTotalSystemTimeInMinutes
FROM UserSystemTime
ORDER BY EstimatedTotalSystemTimeInSeconds DESC
There are of course the built-in assumption that a user never starts processing an order on one date and ends the processing on a different date (past midnight).
Your example data show that the same orderid is used over several (all) of the logged dates. Because of that I included the date as a part of the grouping.
I have not actually tested the above query (at all), so no guaranties that it will work as intended. But hopefully it will give you some ideas so that you can move forward yourself.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply