Rounding problem (?) using DATEDIFF (minutes)

Question

Rounding problem (?) using DATEDIFF (minutes)

SSCarpal Tunnel

Points: 4298

August 31, 2021 at 1:16 pm

I have a query that is using DATEDIFF to calculate the number of minutes, of course as an integer, but I may need the extra precision of showing this as a decimal (?). Also I'm looking for a way of summing the time based on "EmpID" (using CTE?):

SELECT T1.[EmpID],
       Convert(Datetime,T1.[Time]) AS [Time], 
       MIN(Convert(Datetime,T2.[Time])) AS [Time2], 
       DATEDIFF  (MINUTE, (Convert(Datetime,T1.[Time])), MIN(Convert(Datetime,T2.[Time])))   AS [DurationMins]

FROM   [Dev].[dbo].[ConvoProductivityLog] T1
       LEFT JOIN 
   [Dev].[dbo].[ConvoProductivityLog] T2
       ON T1.[EmpID] = T2.[EmpID]
       AND Convert(Datetime,T2.[Time]) > Convert(Datetime,T1.[Time])
       AND T2.[State] = 'Logout'

WHERE T1.[State] = 'Login'

GROUP BY T1.[EmpID], T1.[User], Convert(Datetime, T1.[Time])
ORDER BY T1.[EmpID], T1.[User], Convert(Datetime, T1.[Time])

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

Multiply the result of your sum by 1.000 (adjust the number of zeroes as needed), and it will be converted to a decimal.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

without any data or table set up scripts, tis a tad hard to guess what you want....so, just for fun...

if this is anywhere close to your basic results, then SUM can easily be added and as for decimal, I believe that you were already given an answer here https://www.sqlservercentral.com/forums/topic/function-for-converting-hhmmss-time-to-decimal-equivalent

CREATE TABLE example(
EmpId INT NOT NULL
,T1 DATETIME NOT NULL
,T2 DATETIME NOT NULL
);
INSERT INTO example(EmpId,T1,T2) VALUES
(1,'2021-08-31 15:58:23.090','2021-08-31 15:52:20.070')
,(2,'2021-08-31 15:59:53.223','2021-08-31 15:51:39.727')
,(3,'2021-08-31 16:05:50.790','2021-08-31 16:00:15.407')
,(1,'2021-08-31 12:15:43.470','2021-08-31 12:01:26.180')
,(2,'2021-08-31 11:09:43.140','2021-08-31 11:02:18.153')
,(1,'2021-08-31 15:53:28.067','2021-08-31 15:49:27.112')
,(2,'2021-08-31 16:49:27.115','2021-08-31 10:49:27.514');

--SELECT * FROM example;

WITH cte
AS (SELECT
EmpID,
MIN(CAST(T2 AS time)) min_t2
FROM example
GROUP BY EmpId)

SELECT
e.EmpId,
e.T1,
cte.min_t2,
DATEDIFF(MINUTE, cte.min_t2, CAST(e.T1 AS time)) AS DurationMins
FROM example e
INNER JOIN cte
ON e.EmpId = cte.EmpID
ORDER BY e.EmpId ASC, e.T1 DESC

This reply was modified 3 years, 2 months ago by J Livingston SQL.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sergiy SSC Guru Points: 110208 More actions · Answer 3

First of all, triagonal joins like you use in the query, are very ineffective and even quite dangerous, because they can produce huge result sets during execution and overwhelm the server.

You need to rewrite the query, here is one if the ways:

SELECT T1.[EmpID],
       Convert(Datetime,T1.[Time]) AS [Time], 
       LogoutTime AS [Time2], 
       DATEDIFF  (MINUTE, (Convert(Datetime,T1.[Time])), LogoutTime)   AS [DurationMins]

FROM   [Dev].[dbo].[ConvoProductivityLog] T1
       OUTER APPLY (select top 1 Convert(datetime, T2.[Time] LogoutTime
        From [Dev].[dbo].[ConvoProductivityLog] T2
        Where T1.[EmpID] = T2.[EmpID]
       AND Convert(Datetime,T2.[Time]) > Convert(Datetime,T1.[Time])
       AND T2.[State] = 'Logout'
       order by CONVERT(datetime, T2.[Time]
       ) O
WHERE T1.[State] = 'Login'
ORDER BY T1.[EmpID], T1.[User], Convert(Datetime, T1.[Time])

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Second, you might use DATEDIFF (ss, ..) to find duration in seconds and then divide it by 60.0 to get duration in minutes with better precision.

or - you may use the function AgeCalculation from my article published on this web site to find out duration in minutes + seconds, and then present it in any way you like.

And third - why do you need to convert Time values to DATETIME? How it is stored in the table? This conversion is possibly a terrible performance killer.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

DaveBriCam wrote:

I have a query that is using DATEDIFF to calculate the number of minutes, of course as an integer, but I may need the extra precision of showing this as a decimal (?). Also I'm looking for a way of summing the time based on "EmpID" (using CTE?):

SELECT T1.[EmpID],
       Convert(Datetime,T1.[Time]) AS [Time], 
       MIN(Convert(Datetime,T2.[Time])) AS [Time2], 
       DATEDIFF  (MINUTE, (Convert(Datetime,T1.[Time])), MIN(Convert(Datetime,T2.[Time])))   AS [DurationMins]

FROM   [Dev].[dbo].[ConvoProductivityLog] T1
       LEFT JOIN 
   [Dev].[dbo].[ConvoProductivityLog] T2
       ON T1.[EmpID] = T2.[EmpID]
       AND Convert(Datetime,T2.[Time]) > Convert(Datetime,T1.[Time])
       AND T2.[State] = 'Logout'

WHERE T1.[State] = 'Login'

GROUP BY T1.[EmpID], T1.[User], Convert(Datetime, T1.[Time])
ORDER BY T1.[EmpID], T1.[User], Convert(Datetime, T1.[Time])

@dave... what datatype is the "Time" column of the ConvoProductivityLog table? If it's Varchar or Char, can you provide a couple examples of what is actually stored in that column?

Is there a DATE column in the table? If so, what datatype and could you provide a couple of examples of what is actually stored in the column if it's NOT a DATETIME or DATE or DATETIME2() datatype?

Also, is the table guaranteed to ALWAYS have 1 Logout for every Login?

And, last but not least... do you have any logins that occur one day and the logout does happen until after midnight the next day?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)