Datediff between dates vertically

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 2008 Forums

Transact-SQL (2008)

Datediff between dates vertically

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-15 : 02:43:27

Hi,

I have a requirements to get the date difference between the workid
using routenumber. I need the date difference on how many TAT were the workID transfer from one wotk id to another using the Date1. thanks.

sample
------------

ID---Workid--------date1-------------datediff
-----------------------------------------------
1------A---2013-12-04 04:20:41.000------
1------B---2013-12-05 14:26:10.000------result of A&B
1------C---2013-12-05 14:26:34.000------result of B&C until the last workid

Create table #sample (ID int, workid nvarchar(2), date1 datetime, date2 datetime, Routenumber int)
	
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'A','2013-12-04 04:20:41.000','2013-12-05 04:20:40.000',5)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'B','2013-12-05 14:26:10.000','2013-12-05 04:20:40.000',10)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'C','2013-12-05 14:26:34.000','2013-12-05 04:20:40.000',15)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'D','2013-12-05 14:27:23.000','2013-12-05 04:20:40.000',20)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'E','2013-12-05 14:53:13.000','2013-12-05 04:20:40.000',25)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'A','2013-12-15 00:38:25.000','2013-12-15 00:38:24.000',5)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'B','2013-12-15 00:38:34.000','2013-12-15 00:38:24.000',10)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'C','2013-12-15 00:38:57.000','2013-12-15 00:38:24.000',15)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'D','2013-12-15 00:38:57.000','2013-12-15 00:38:24.000',20)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'E','2013-12-15 02:12:27.000','2013-12-15 00:38:24.000',25)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'F','2013-12-15 02:12:37.000','2013-12-15 00:38:24.000',30)

Select ID, workid, date1, date2, Routenumber
From #sample

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-15 : 03:31:24

[code]
IF Object_ID('tempDB..#sample') IS NOT NULL
drop table tempDB..#sample

Create table #sample (ID int, workid nvarchar(2), date1 datetime, date2 datetime, Routenumber int)

Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'A','2013-12-04 04:20:41.000','2013-12-05 04:20:40.000',5)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'B','2013-12-05 14:26:10.000','2013-12-05 04:20:40.000',10)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'C','2013-12-05 14:26:34.000','2013-12-05 04:20:40.000',15)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'D','2013-12-05 14:27:23.000','2013-12-05 04:20:40.000',20)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(1,'E','2013-12-05 14:53:13.000','2013-12-05 04:20:40.000',25)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'A','2013-12-15 00:38:25.000','2013-12-15 00:38:24.000',5)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'B','2013-12-15 00:38:34.000','2013-12-15 00:38:24.000',10)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'C','2013-12-15 00:38:57.000','2013-12-15 00:38:24.000',15)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'D','2013-12-15 00:38:57.000','2013-12-15 00:38:24.000',20)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'E','2013-12-15 02:12:27.000','2013-12-15 00:38:24.000',25)
Insert into #sample(ID,workid,date1,date2,Routenumber) values(2,'F','2013-12-15 02:12:37.000','2013-12-15 00:38:24.000',30)

;with aSample
AS
(
Select ID, workid, date1, date2, Routenumber
,ROw_Number() OVER(Partition by ID Order by WorkID asc) as rn
From #sample
)

Select A.ID, A.workid, A.date1, A.date2, A.Routenumber,A.rn
,datediff(s,B.date1,A.date1) as diff
,B.*
From aSample as A
FULL JOIN aSample as B
ON A.ID=B.ID
AND A.rn=B.rn+1
Order by A.ID, A.WorkID,A.date1

IF Object_ID('tempDB..#sample') IS NOT NULL
drop table tempDB..#sample
[/code]

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-15 : 05:49:38

Hi Stepson,

Awesome!
Thank you very much for your prompt reply.
Btw, Can you please give me a little bit explanation on how this work the second part of sql statement.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-15 : 06:18:15

I used a CTE (Common Table Expressions) adding a new column - that with rowNumber- to number the rows.
(for ID =1 I have row1,row2,row3..., for ID=2 I have row1 ,row2,...)

I join the same dataSet on ID and (second condition) row with next row.

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-15 : 06:25:07

also , you can use Outer Apply/Cross Apply

like this:


select
	A.ID, A.workid, A.date1, A.date2, A.Routenumber
	,datediff(s,B.date1,A.date1) as diff
from #sample A
		outer apply
		(select top 1 
				B.date1
		 from #sample B
		 Where A.ID=B.ID
			AND A.date1<B.date1
		 Order by B.Id,B.WorkID,B.date1 asc) as B

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-15 : 20:24:39

Thank you very much!

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-15 : 22:26:55

I'm trying to add another codes in my statement by i got this error.
"Argument data type nvarchar is invalid for argument 2 of dateadd function."
May I know how and why i got this error? Thanks.

Btw, My SQL Version is 2008

@timezoneOffset -- Data Type is Int the value is (8)
prt.CREATEDDATETIME, prt.POCreatedDa --Data Type is Datetime

SELECT
dateadd(hour,@timezoneOffset,prt.CREATEDDATETIME) as WRKCTRCREATEDDATE
,dateadd(hour,@timezoneOffset,prt.POCreatedDate) as POCREATEDDATETIME
FROM MYTABLE

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-04-15 : 23:00:20

Solved.

Use this code:

DATEADD(HOUR,convert(int,@timezoneOffset), pr.createddatetime)

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-16 : 01:09:52

Welcome!

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-12 : 03:35:10

Hi Stepson,

I have a follow up query regarding the date diff. how can i get the hours and minute as the result of datediff. thanks

sample:

D1 D2
--------------------------------------------result
5/2/2014 10:27:03 AM--5/2/2014 10:27:52 AM--00:00
5/2/2014 10:27:52 AM--5/2/2014 12:08:58 PM--01:41

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-12 : 03:38:27

[code]CONVERT(VARCHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, D1, D2), 0), 108)[/code]

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-12 : 03:46:35

[code]
with aCTE
AS
(SELECT '5/2/2014 10:27:03 AM' D1, '5/2/2014 10:27:52 AM' D2 UNION ALL
SELECT '5/2/2014 10:27:52 AM','5/2/2014 12:08:58 PM')

select *

,RIGHT('00'+convert(varchar(10), (DATEDIFF(SECOND,d1,d2)/86400)),2) + ':' +
RIGHT('00'+convert(varchar(2), ((DATEDIFF(SECOND,d1,d2)%86400)/3600)),2) + ':'+
RIGHT('00'+convert(varchar(2), (((DATEDIFF(SECOND,d1,d2)%86400)%3600)/60)),2) /*+ ':'+
RIGHT('00'+convert(varchar(2), (((DATEDIFF(SECOND,d1,d2)%86400)%3600)%60)),2)*/ as 'Result[DD:HH:MM:SS]'

from aCTE
[/code]

output
[code]
D1 D2 Result[DD:HH:MM:SS]
5/2/2014 10:27:03 AM 5/2/2014 10:27:52 AM 00:00:00
5/2/2014 10:27:52 AM 5/2/2014 12:08:58 PM 00:01:41
[/code]

sabinWeb MCP

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-05-12 : 03:51:17

I haven't refresh the page, so I didn't saw @khtan's post

sabinWeb MCP

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-12 : 03:57:24

May I know if this is Okey. or do you have any other codes to get the hr and mins.thanks.

Select Convert(varchar(10), TIMEDIFF/3600) + ':'+Convert(varchar(10), (TIMEDIFF % 3600)/60) AS TAT_HOUR

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-12 : 14:01:07

As long as you don't exceed 24 hours in the diff - khtan's solution would be the best. If you need to be able to calculate more than 24 hours difference, then stepson's solution would be the better solution.

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-05-12 : 22:10:07

Thank you very much guys for giving these solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources