Calculating Duration

Question

Post reply

Calculating Duration

whymaravele

SSC Enthusiast

Points: 129
More actions
June 20, 2016 at 1:52 am

#327188

Hi
I have a structure below:
/****** Object: Table [dbo].[OrderDetails] Script Date: 20/06/2016 09:03:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO --drop table [dbo].[CONSULTATION]
CREATE TABLE [dbo].[CONSULTATION](
[TaskID] [int] IDENTITY(1,1) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[TakenDate] [datetime] NULL,
[CompletedDate] [datetime] NULL,
CONSTRAINT [PK_HD_TASK] PRIMARY KEY CLUSTERED
(
[TaskID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT INTO [dbo].[CONSULTATION]
VALUES('2016-05-01 11:49:28.763','2016-05-01 13:46:05.660','2016-05-01 13:54:30.347'),
('2016-05-01 23:55:07.417','2016-05-02 00:33:54.367','2016-05-02 00:35:36.837'),
('2016-05-02 12:48:48.640','2016-05-02 13:36:24.420','2016-05-02 13:41:52.800'),
('2016-05-04 05:55:52.290','2016-05-04 06:20:15.590','2016-05-04 06:26:57.087')
SELECT
TaskId
,CONVERT(DATE ,c.CreatedDate)AS DateSubmitted
,CONVERT(TIME ,c.CreatedDate)AS TimeSubmitted
,CONVERT(DATE ,c.TakenDate)AS DateTaken
,CONVERT(TIME ,c.TakenDate)AS TimeTaken
,CONVERT(DATE ,c.CompletedDate)AS DateCompleted
,CONVERT(TIME ,c.CompletedDate)AS TimeCompleted
FROM [dbo].[CONSULTATION] c
I want to calculate duration of the call by Duration = (Time Completed - Time Taken)
And Response Time = (Time Taken - Time Submitted)
But also put into account the call can go over night.

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

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

durga.palepu Hall of Fame Points: 3530 More actions · Answer 1

durga.palepu

Hall of Fame

Points: 3530

June 20, 2016 at 2:22 am

#1885791

What's the problem you are facing here?

whymaravele SSC Enthusiast Points: 129 More actions · Answer 2

I was trying

WITH cte AS (
SELECT
TaskId
,CONVERT(DATE ,c.CreatedDate)AS DateSubmitted
,CONVERT(TIME ,c.CreatedDate)AS TimeSubmitted
,CONVERT(DATE ,c.TakenDate)AS DateTaken
,CONVERT(TIME ,c.TakenDate)AS TimeTaken
,CONVERT(DATE ,c.CompletedDate)AS DateCompleted
,CONVERT(TIME ,c.CompletedDate)AS TimeCompleted
FROM [dbo].[CONSULTATION] c
)
SELECT
TaskId
,DateSubmitted
,TimeSubmitted
,DateTaken
,TimeTaken
,DateCompleted
,TimeCompleted
,(TimeCompleted - TimeTaken) AS Duration
FROM cte

But can't get it right

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 3

Check out the DATEDIFF function: https://msdn.microsoft.com/en-GB/library/ms189794.aspx

E.g this returns the duration in minutes

DATEDIFF(MINUTE,TimeTaken,TimeCompleted) AS Duration

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT *,

[Duration] = DATEDIFF(SECOND, c.TakenDate, c.CompletedDate),

[Response Time] = DATEDIFF(SECOND, c.CreatedDate, c.TakenDate)

FROM #CONSULTATION c

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sergiy SSC Guru Points: 110208 More actions · Answer 5

whymaravele (6/20/2016)
I was trying
WITH cte AS (
SELECT
TaskId
,CONVERT(DATE ,c.CreatedDate)AS DateSubmitted
,CONVERT(TIME ,c.CreatedDate)AS TimeSubmitted
,CONVERT(DATE ,c.TakenDate)AS DateTaken
,CONVERT(TIME ,c.TakenDate)AS TimeTaken
,CONVERT(DATE ,c.CompletedDate)AS DateCompleted
,CONVERT(TIME ,c.CompletedDate)AS TimeCompleted
FROM [dbo].[CONSULTATION] c
)
SELECT
TaskId
,DateSubmitted
,TimeSubmitted
,DateTaken
,TimeTaken
,DateCompleted
,TimeCompleted
,(TimeCompleted - TimeTaken) AS Duration
FROM cte
But can't get it right

You're making a mistake of using DATE and TIME data types inside of the query.

They are for presentation only.

SELECT

TaskId

,CONVERT(DATE ,c.CreatedDate)AS DateSubmitted

,CONVERT(TIME ,c.CreatedDate)AS TimeSubmitted

,CONVERT(DATE ,c.TakenDate)AS DateTaken

,CONVERT(TIME ,c.TakenDate)AS TimeTaken

,CONVERT(DATE ,c.CompletedDate)AS DateCompleted

,CONVERT(TIME ,c.CompletedDate)AS TimeCompleted

,FLOOR (CONVERT(FLOAT, [CompletedDate] - [TakenDate])) AS DurationDays

,convert(time, [CompletedDate] - [TakenDate]) AS DurationTime

FROM [dbo].[CONSULTATION] c

_____________
Code for TallyGenerator