date time diff calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

date time diff calculation

Author

Topic

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-28 : 09:16:12

hi folks,
I have the following table,
callid, assigneddate(datetime),resolveddate(datetime), startstop, STARTSTOPDATE (datetime)

100, 21/05/2009 10:00:00, 21/05/2009 15:30:00, STOP, 21/05/2009 13:00:00
100, 21/05/2009 10:00:00, 21/05/2009 15:30:00, START, 21/05/2009 15:00:00

I want to be able to work out the time difference between resolveddate and assigneddate, taking into account any stops and starts in the data..

ie. RESOLVEDDATE - STARTDATE - 5hr 30mins
START - STOP = 2hr

therefore time differnce is 3hr 30mins (including stops)

how can I work this out in SQL?
thanks.
Craig.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 09:18:41

will have more than 1 START / STOP per callid ?

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-28 : 09:25:53

it could have and also it may have a stop without a start

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:30:21

khtan, "stop1 - start1" + "stop2 - start" equals to SUM(Stop) - SUM(Start).

DECLARE	@Sample TABLE
	(
		CallID INT,
		AssignedDate DATETIME,
		ResolvedDate DATETIME,
		StartStop VARCHAR(5),
		StartStopDate DATETIME
	)

SET DATEFORMAT DMY

INSERT	@Sample
SELECT	100, '21/05/2009 10:00', '21/05/2009 15:30', 'STOP' , '21/05/2009 11:00' UNION ALL
SELECT	100, '21/05/2009 10:00', '21/05/2009 15:30', 'START', '21/05/2009 11:15' UNION ALL
SELECT	100, '21/05/2009 10:00', '21/05/2009 15:30', 'STOP' , '21/05/2009 13:00' UNION ALL
SELECT	100, '21/05/2009 10:00', '21/05/2009 15:30', 'START', '21/05/2009 15:00'

SELECT		CallID,
		Duration - Rest AS MinutesTaken,
		Duration AS TotalDuration,
		Rest
FROM		(
			SELECT		CallID,
					SUM(CASE StartStop
						WHEN 'Start' THEN DATEDIFF(MINUTE, '20000101', StartStopDate)
						WHEN 'Stop' THEN DATEDIFF(MINUTE, StartStopDate, '20000101')
						ELSE 0
					END) AS Rest,
					SUM(DATEDIFF(MINUTE, AssignedDate, ResolvedDate)) AS Duration
			FROM		@Sample
			GROUP BY	CallID
		) AS d
ORDER BY	CallID

cjonline, what is the business rules for handling such cases? If only stop (or start) how should that be treated?

E 12°55'05.63"
N 56°04'39.26"

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-28 : 09:38:24

Hi,
Thanks, I need to work out business hours for these differences (8am - 6pm)

If there is is NO start after a stop then the last STOP becomes the ResolvedDate.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 09:45:50

How about any cross day start - stop ? Do you need to count only business hours ?

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-28 : 09:49:37

Need cross day start stop times in business hours.. ie STOP = 1/1/2009 16:00, START = 2/1/2009 9:00
then the buinesshourstime would be 3hrs (as business hrs are 08:00 - 18:00)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:52:15

Can you please please please provide accurate sample data, demonstration every possible combination of events?
And also post your expected output based on all these combniations?

You can use the suggestion above as a template how to populate a sample table with data.

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-28 : 09:52:18

so there should not be 2 consecutive START right ?

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-28 : 10:16:18

no, the process should either be
-> ASSIGNED_DATE -> STOP_DATE -> START_DATE -> RESOLVED_DATE
OR
-> ASSIGNED_DATE -> STOP_DATE -> START_DATE -> STOP_DATE -> START_DATE -> (repeated as necessary) -> RESOLVED_DATE
OR
-> ASSIGNED_DATE -> STOP_DATE ->
OR
-> ASSIGNED_DATE -> STOP_DATE -> START_DATE -> (repeated as neccessary) -> STOP_DATE

(the assigned_date is always mandatory. Either a STOP_DATE or a RESOLVED_DATE must exist to close the call)

thats the only conditions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 14:21:25

try this (not tested)


SELECT callid, assigneddate,resolveddate,  STARTSTOPDATE AS StopTime,(SELECT TOP 1 STARTSTOPDATE FROM YourTable WHERE callid=t.callid AND startstop='START' AND STARTSTOPDATE >t.STARTSTOPDATE ORDER BY STARTSTOPDATE) AS StartTime INTO #Temp
FROM YourTable t
WHERE startstop='STOP'

SELECT callid,CONVERT(varchar(8),DATEADD(mi,Diff,0),108)
FROM
(
SELECT callid,MIN(CASE WHEN DATEDIFF(dd, assigneddate, resolveddate)-2 > 0 THEN (DATEDIFF(dd, assigneddate, resolveddate)-2)* 10*60 ELSE 0 END + DATEDIFF(mi,DATEADD(dd,-1 * DATEDIFF(dd,0, assigneddate), assigneddate),CASE WHEN resolveddate IS NULL THEN DATEADD(dd,-1 * DATEDIFF(dd,0, StopTime), StopTime) ELSE '18:00' END) +  COALESCE(DATEDIFF(mi,'08:00',DATEADD(dd,-1 * DATEDIFF(dd,0, resolveddate), resolveddate)),0))- SUM(CASE WHEN DATEDIFF(dd,StopTime,StartTime)-2>0 THEN (DATEDIFF(dd,StopTime,StartTime)-2)*10*60 ELSE 0 END+  DATEDIFF(mi,DATEADD(dd,-1 * DATEDIFF(dd,0, StopTime), StopTime),'18:00') +  COALESCE(DATEDIFF(mi,'08:00',DATEADD(dd,-1 * DATEDIFF(dd,0, StartTime), StartTime)),0)) AS Diff
FROM #Temp
GROUP BY callid
)t

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 04:18:58

visakh16, when I use your query with the data in the 2nd post, it returns 17:15:00 when it should only return 3:30:00 ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 05:15:41

[code]
DECLARE @Sample TABLE
(
CallID int,
AssignedDate datetime,
ResolvedDate datetime,
StartStop varchar(5),
StartStopDate datetime
)

SET DATEFORMAT DMY

INSERT @Sample
SELECT 100, '21/05/2009 10:00', '21/05/2009 15:30', 'STOP' , '21/05/2009 11:00' UNION ALL -- 1.0 (10:00 - 11:00)
SELECT 100, '21/05/2009 10:00', '21/05/2009 15:30', 'START', '21/05/2009 11:30' UNION ALL
SELECT 100, '21/05/2009 10:00', '21/05/2009 15:30', 'STOP' , '21/05/2009 13:00' UNION ALL -- 1.5 (11:30 - 13:00)
SELECT 100, '21/05/2009 10:00', '21/05/2009 15:30', 'START', '21/05/2009 15:00' UNION ALL -- 0.5 (15:00 - 15:30)

SELECT 101, '01/01/2009 10:00', '03/01/2009 10:30', 'STOP' , '02/01/2009 09:00' UNION ALL -- 8.0 + 1.0 (10:00 - 18:00, 08:00 - 09:00)
SELECT 101, '01/01/2009 10:00', '03/01/2009 10:30', 'START', '02/01/2009 10:00' UNION ALL
SELECT 101, '01/01/2009 10:00', '03/01/2009 10:30', 'STOP', '03/01/2009 09:30' UNION ALL -- 8.0 + 1.5 (10:00 - 18:00, 08:00 - 09:30)
SELECT 101, '01/01/2009 10:00', '03/01/2009 10:30', 'START', '03/01/2009 10:00' -- 0.5 (10:00 - 10:30)

DECLARE @work TABLE
(
row_id int identity(1,1),
CallID int,
Status varchar(10),
StatusDate datetime
)

INSERT INTO @work (CallID, Status, StatusDate)
SELECT CallID, Status, StatusDate
FROM
(
SELECT CallID, Status = 'ASSIGN', StatusDate = MIN(AssignedDate)
FROM @Sample
GROUP BY CallID

UNION ALL

SELECT CallID, Status = StartStop, StatusDate = StartStopDate
FROM @Sample

UNION ALL

SELECT CallID, Status = 'RESOLVE', StatusDate = MAX(ResolvedDate)
FROM @Sample
GROUP BY CallID
) s
ORDER BY CallID, StatusDate

SELECT CallID,
status = MAX(CASE WHEN row_id % 2 = 1 THEN Status END) + ' - ' + MAX(CASE WHEN row_id % 2 = 0 THEN Status END),
start = MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
stop = MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END),
tm = CASE WHEN DATEDIFF(DAY,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END)) = 0
THEN DATEDIFF(minute,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
ELSE
DATEDIFF(minute,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
-
(
DATEDIFF(DAY,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
* 14 * 60
)
END
FROM @work
GROUP BY CallID, (row_id - 1) / 2

/*
CallID status start stop tm
-------- ------------------ ------------------------- ------------------------- -----------
100 ASSIGN - STOP 2009-05-21 10:00:00.000 2009-05-21 11:00:00.000 60
100 START - STOP 2009-05-21 11:30:00.000 2009-05-21 13:00:00.000 90
100 START - RESOLVE 2009-05-21 15:00:00.000 2009-05-21 15:30:00.000 30
101 ASSIGN - STOP 2009-01-01 10:00:00.000 2009-01-02 09:00:00.000 540
101 START - STOP 2009-01-02 10:00:00.000 2009-01-03 09:30:00.000 570
101 START - RESOLVE 2009-01-03 10:00:00.000 2009-01-03 10:30:00.000 30

(6 row(s) affected)
*/

SELECT CallID,
tm = SUM(tm),
tm_hr = SUM(tm) / 60,
tm_mn = SUM(tm) % 60
FROM
(
SELECT CallID,
status = MAX(CASE WHEN row_id % 2 = 1 THEN Status END) + ' - ' + MAX(CASE WHEN row_id % 2 = 0 THEN Status END),
start = MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
stop = MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END),
tm = CASE WHEN DATEDIFF(DAY,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END)) = 0
THEN DATEDIFF(minute,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
ELSE
DATEDIFF(minute,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
-
(
DATEDIFF(DAY,
MAX(CASE WHEN row_id % 2 = 1 THEN StatusDate END),
MAX(CASE WHEN row_id % 2 = 0 THEN StatusDate END))
* 14 * 60
)
END

FROM @work
GROUP BY CallID, (row_id - 1) / 2
) d
GROUP BY CallID

/*
CallID tm tm_hr tm_mn
--------- ------ ------- --------
100 180 3 0
101 1140 19 0

(2 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 06:58:30

Hi,
Thanks for this.. its almost working... it seems to work ok when theres a
ASSIGNED and RESOLVED datetimes only, doesnt work when there's an
ASSIGNED, STOP,START,RESOLVED.. it seems to return the Stoppage (STOP->START) time,
but does not seem to return the assigned -> resolved time

thanks

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 07:07:40

I can post data if it helps

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 08:24:56

quote:
Originally posted by cjonline

I can post data if it helps

please post the sample data

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 09:04:41

CALLID STARTSTOP STARTSTOPDATETIME ASSIGNED RESOLVED
255833 SAIC_STOP 28/05/2009 12:21 28/05/2009 12:19 28/05/2009 12:32
255833 SAIC_START 28/05/2009 12:26 28/05/2009 12:19 28/05/2009 12:32
255833 SAIC_STOP 28/05/2009 12:28 28/05/2009 12:19 28/05/2009 12:32
255833 SAIC_START 28/05/2009 12:30 28/05/2009 12:19 28/05/2009 12:32
255832 SAIC_STOP 28/05/2009 12:04 28/05/2009 12:01 28/05/2009 12:12
255832 SAIC_START 28/05/2009 12:09 28/05/2009 12:01 28/05/2009 12:12
255825 SAIC_STOP 28/05/2009 10:02 26/05/2009 15:38 28/05/2009 10:08
255825 SAIC_START 28/05/2009 10:06 26/05/2009 15:38 28/05/2009 10:08
255824 RESOLVED_BY_SAIC 28/05/2009 09:39 26/05/2009 14:40 28/05/2009 09:39
255822 RESOLVED_BY_SAIC 28/05/2009 09:39 26/05/2009 10:49 28/05/2009 09:39

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 09:25:21

You never mention there is a 3rd status in the STARTSTOP ?

add the line in red


insert into @work (CallID, Status, StatusDate)
select	CallID, Status, StatusDate
from
(
	select	CallID, Status = 'ASSIGN', StatusDate = min(AssignedDate)
	from	@Sample
	group by CallID
	
	union all
	
	select	CallID, Status = StartStop, StatusDate = StartStopDate
	from	@Sample
	where	StartStop	in ('START', 'STOP')
	union all 
	
	select	CallID, Status = 'RESOLVE', StatusDate = max(ResolvedDate)
	from	@Sample
	group by CallID
) s
order by CallID, StatusDate

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 10:00:03

Thanks, is there any way you could create a function for Business Hours where I could just send a startdatetime and an enddatetime and it returns the business MINS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-29 : 10:03:36

is already in the code that i posted.


tm	= CASE 	WHEN 	DATEDIFF(DAY, @start_date, @end_date) = 0
		THEN 	DATEDIFF(minute, @start_date, @end_date)
		ELSE
			DATEDIFF(minute, @start_date, @end_date)
			-
			(
				DATEDIFF(DAY, @start_date, @end_date) * 14 * 60
			)

KH
[spoiler]Time is always against us[/spoiler]

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-05-29 : 10:21:56

does that remove weekends?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources