Calculate time elapsed between Dates
-
Wednesday, February 13, 2013 9:56 PM
I'm working on a query to calculate the time elapsed between dates stored in a table. I would like to write the query without using a cursor(pretty big tables with many columns). I have a table that stores employees and a table that stores the time an employee clicks a button in a certain section of a web application. The employee table is linked to the process table by employee id. There are multiple process rows for each employee. I will have to use the process id value in the process table to determine what section the button was clicked in. The process date values will be used for the elapsed time calculation. Once I have a date for each section, I will need to calculate the elapsed time(in days, hours, minutes) between certain sections. This query will be used in a report that runs weekly.
ex. processdate(Section 1) processdate(Section 2) Time elapsed 1-2
2009-09-28 13:09:49.360 2010-01-22 14:42:09.510 time elapsed in days, hours, minutes?
I have a few questions?
1. What is the best approach? Do I need cursor to loop thru all of the dates for each employee. I'm worried about the impact on performance?
2. How to caculate time elapsed in days, hours, minutes?
3. Should I use temp tables to get all of the sections in one row like the example above before doing the time calculation?
I've listed the relevant table columns below:
CREATE TABLE [dbo].[Employee]( [Empid] [int] NOT NULL, [First_Name] [varchar](50) NULL, [Last_Name] [varchar](50) NULL, [Hire_Date] [datetime] NULL, .... ) CREATE TABLE [dbo].[Process]( [Empid] [int] NULL, [processid] [int] null, [processDate] [datetime] NULL, ...... )
- Edited by James_P Wednesday, February 13, 2013 9:57 PM
All Replies
-
Wednesday, February 13, 2013 10:20 PM
Hi James_P
I previously worked on this thread let me know if it doesn't help you. [THREAD]
Pérez
Update: 02/19/2013 I can't believe how badly I messed up in this thread. We only need to get the difference once between dates. The seconds is the best choice. It will not only do the difference for the seconds but it gives you the difference in seconds. So we didn't have to do it for each cycle aka year, month, day, hour, minute, second... :( my bad! -
Wednesday, February 13, 2013 11:20 PMModerator
The following blog is on the same topic:
http://www.sqlusa.com/bestpractices2005/employment/
Do you really need minutes?
Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012 -
Thursday, February 14, 2013 6:15 AM
you can try this script -
--CREATE TABLE [dbo].[Employee]( -- [Empid] [int] NOT NULL, -- [First_Name] [varchar](50) NULL, -- [Last_Name] [varchar](50) NULL, -- [Hire_Date] [datetime] NULL -- ) --CREATE TABLE [dbo].[Process]( -- [Empid] [int] NULL, -- [Processid] [int] null, -- [ProcessDate] [datetime] NULL -- ) --INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (1,'a','b', GETDATE()-200) --INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (2,'c','d', GETDATE()-150) --INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (3,'e','f' ,GETDATE()-170) --INSERT Employee (EmpID,First_Name,Last_Name,Hire_Date) VALUES (4,'g','h', GETDATE()-100) --INSERT Process(Empid,Processid,ProcessDate) VALUES (1,1,GETDATE()-200) --INSERT Process(Empid,Processid,ProcessDate) VALUES (1,2,GETDATE()-190) --INSERT Process(Empid,Processid,ProcessDate) VALUES (1,3,GETDATE()-183) --INSERT Process(Empid,Processid,ProcessDate) VALUES (1,4,GETDATE()-150) --INSERT Process(Empid,Processid,ProcessDate) VALUES (2,1,GETDATE()-150) --INSERT Process(Empid,Processid,ProcessDate) VALUES (2,5,GETDATE()-130) --INSERT Process(Empid,Processid,ProcessDate) VALUES (2,3,GETDATE()-120) --INSERT Process(Empid,Processid,ProcessDate) VALUES (3,1,GETDATE()-170) --INSERT Process(Empid,Processid,ProcessDate) VALUES (3,4,GETDATE()-100) --INSERT Process(Empid,Processid,ProcessDate) VALUES (3,7,GETDATE()-50) --INSERT Process(Empid,Processid,ProcessDate) VALUES (4,1,GETDATE()-100) WITH CTE (RN, EmpID, First_Name, Last_Name, Hire_Date,ProcessID, ProcessDate) AS (SELECT ROW_NUMBER() OVER (PARTITION BY E.empID ORDER BY E.EmpID, P.ProcessDate) AS RN ,E.EmpID ,E.First_Name ,E.Last_Name ,E.Hire_Date ,P.ProcessID ,P.ProcessDate FROM Employee E JOIN Process P ON E.Empid = P.Empid ) SELECT CASE B.ProcessDate WHEN NULL THEN 0 ELSE DATEDIFF(DAY,A.ProcessDate,B.ProcessDate) END AS [Days] ,CASE B.ProcessDate WHEN NULL THEN 0 ELSE DATEDIFF(HOUR,A.ProcessDate,B.ProcessDate)%24 END AS [Hours] ,CASE B.ProcessDate WHEN NULL THEN 0 ELSE DATEDIFF(MINUTE,A.ProcessDate,B.ProcessDate)%60 END AS [Minutes] ,A.* ,B.* FROM CTE A LEFT JOIN CTE B ON A.RN+1 = B.RN AND A.EmpID = B.EmpID ORDER BY A.EmpID,A.RN
-
Tuesday, February 19, 2013 7:30 PM
Hi Perez,
I'm trying to incorporate the code that was used in your thread referenced above. I have around 50 process date columns in one table that I need to calculate the elapsed time on. If I understand your script correctly, I would have to repeat the script each time I needed to calculate the elapsed time between two dates. Is there anyway I can do this in one pass thru? Should I create a function to do this?
I've added the first two columns to your script below. Each date column has a unique name.
DECLARE @TableWithStuff table (Client int,processdate1Dt datetime,processdate2Dt datetime) INSERT INTO @TableWithStuff (Client,processdate1Dt,processdate2Dt) VALUES (1,'2012-04-03','2012-04-06 11:58') --,(1,CURRENT_TIMESTAMP,DATEADD(DAY,10,'2012-10-07 10:10:40.130')) --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ -- Script --+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Select * from @TableWithStuff ;with AbstractingTheBaseDataSet as ( SELECT Client /* Removed so that group by will work ;) ,AuditProcessedDt ,ReceivedDt */ ,[Years] = SUM(ABS(DATEPART(YEAR,processdate1Dt)-DATEPART(YEAR,processdate2Dt))) ,[Months] = SUM(ABS(DATEPART(MONTH,processdate1Dt)-DATEPART(MONTH,processdate2Dt))) ,[Days] = SUM(ABS(DATEPART(DAY,processdate1Dt)-DATEPART(DAY,processdate2Dt))) ,[HOURS] = SUM(ABS(DATEPART(HH,processdate1Dt)-DATEPART(HH,processdate2Dt))) ,[Minutes] = SUM(ABS(DATEPART(MM,processdate1Dt)-DATEPART(MM,processdate2Dt))) ,[Seconds] = SUM(ABS(DATEPART(SS,processdate1Dt)-DATEPART(SS,processdate2Dt))) ,[CalculationDate] = DATEADD(SS,SUM(DATEDIFF(SS,processdate1Dt,processdate2Dt)),'19000101') FROM @TableWithStuff GROUP BY Client) SELECT --* FROM AbstractingTheBaseDataSet CONVERT(VARCHAR(255),(DATEPART(YEAR,CalculationDate)-DATEPART(YEAR,'19000101'))) + 'y' + CONVERT(VARCHAR(255),(DATEPART(MONTH,CalculationDate)-DATEPART(MONTH,'19000101'))) + 'm' + CONVERT(VARCHAR(255),(DATEPART(DAY,CalculationDate)-DATEPART(DAY,'19000101'))) + 'd ' + CONVERT(VARCHAR(255),(DATEPART(HH,CalculationDate)-DATEPART(HH,'19000101'))) + ':' + CONVERT(VARCHAR(255),(DATEPART(MM,CalculationDate)-DATEPART(MM,'19000101'))) + ':' + CONVERT(VARCHAR(255),(DATEPART(SS,CalculationDate)-DATEPART(SS,'19000101'))) FROM AbstractingTheBaseDataSet
- Edited by James_P Tuesday, February 19, 2013 7:31 PM
-
Tuesday, February 19, 2013 9:57 PM
Hi James_P
Short answer: Yes. ;)
Long Answer: Try this. Warning Not tested thoroughly!
---- table with many date columns --IF OBJECT_ID(N'dbo.mytime',N'U') IS NOT NULL DROP TABLE dbo.mytime --CREATE TABLE dbo.mytime ( --LogID BIGINT IDENTITY(1,1), --ProcessDate1 DATETIME, --ProcessDate2 DATETIME, --ProcessDate3 DATETIME, --ProcessDate4 DATETIME, --ProcessDate5 DATETIME, --ProcessDate6 DATETIME, --ProcessDate7 DATETIME, --ProcessDate8 DATETIME, --ProcessDate9 DATETIME, --ProcessDate10 DATETIME, --CONSTRAINT pk_LogID PRIMARY KEY (LogID) --) --INSERT INTO dbo.mytime ( --ProcessDate1, --ProcessDate2, --ProcessDate3, --ProcessDate4, --ProcessDate5, --ProcessDate6, --ProcessDate7, --ProcessDate8, --ProcessDate9, --ProcessDate10 --)-- Control Group 1 Day apart --VALUES (DATEADD(DD,-1,CURRENT_TIMESTAMP), --DATEADD(DD,-2,CURRENT_TIMESTAMP), --DATEADD(DD,-3,CURRENT_TIMESTAMP), --DATEADD(DD,-4,CURRENT_TIMESTAMP), --DATEADD(DD,-5,CURRENT_TIMESTAMP), --DATEADD(DD,-6,CURRENT_TIMESTAMP), --DATEADD(DD,-7,CURRENT_TIMESTAMP), --DATEADD(DD,-8,CURRENT_TIMESTAMP), --DATEADD(DD,-9,CURRENT_TIMESTAMP), --DATEADD(DD,-10,CURRENT_TIMESTAMP) --),-- Test Group Randomly set apart --(DATEADD(SS,-12345,CURRENT_TIMESTAMP), --DATEADD(SS,-23456,CURRENT_TIMESTAMP), --DATEADD(SS,-34567,CURRENT_TIMESTAMP), --DATEADD(SS,-45678,CURRENT_TIMESTAMP), --DATEADD(SS,-56789,CURRENT_TIMESTAMP), --DATEADD(SS,-67890,CURRENT_TIMESTAMP), --DATEADD(SS,-78901,CURRENT_TIMESTAMP), --DATEADD(SS,-89012,CURRENT_TIMESTAMP), --DATEADD(SS,-90123,CURRENT_TIMESTAMP), --DATEADD(SS,-01234,CURRENT_TIMESTAMP) --) --GO --ALTER FUNCTION dbo.fn_TimeDifference (@date1 DATETIME, @date2 DATETIME) --CREATE FUNCTION dbo.fn_TimeDifference (@date1 DATETIME, @date2 DATETIME) --RETURNS VARCHAR(30) --WITH EXECUTE AS CALLER --AS --BEGIN -- DECLARE @DtDiffString VARCHAR(30) -- ;WITH cte_DateMath AS ( -- SELECT DATEADD(SS,ABS(DATEDIFF(SS,@date1,@date2)),'1900/01/01') AS DtDiff -- ) -- SELECT @DtDiffString = -- CONVERT(VARCHAR(3),(DATEPART(YEAR,DtDiff)-DATEPART(YEAR,'19000101'))) + 'y' -- + CONVERT(VARCHAR(3),(DATEPART(MONTH,DtDiff)-DATEPART(MONTH,'19000101'))) + 'm' -- + CONVERT(VARCHAR(3),(DATEPART(DAY,DtDiff)-DATEPART(DAY,'19000101'))) + 'd ' -- + CONVERT(VARCHAR(3),(DATEPART(HH,DtDiff)-DATEPART(HH,'19000101'))) + ':' -- + CONVERT(VARCHAR(3),(DATEPART(MM,DtDiff)-DATEPART(MM,'19000101'))) + ':' -- + CONVERT(VARCHAR(3),(DATEPART(SS,DtDiff)-DATEPART(SS,'19000101'))) -- FROM cte_DateMath -- RETURN @DtDiffString --END --GO SELECT dbo.fn_TimeDifference(ProcessDate1, ProcessDate2) ,dbo.fn_TimeDifference(ProcessDate3, ProcessDate4) ,dbo.fn_TimeDifference(ProcessDate5, ProcessDate6) ,dbo.fn_TimeDifference(ProcessDate7, ProcessDate8) ,dbo.fn_TimeDifference(ProcessDate9, ProcessDate10) FROM dbo.mytime
Pérez
- Marked As Answer by Iric WenModerator Thursday, February 21, 2013 9:39 AM
-
Wednesday, February 27, 2013 1:50 PM
I understand that the ABS function returns the absolute value of a number. This would prevent negative numbers from being displayed. Would the function work if I removed ABS? I will need to identify and report to the users when the numbers are negative. This would mean that the input dates are invalid. Is there any way to change the function logic to return "Invalid" if the ABS value is negative?
.png)
