SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Datediff between dates vertically
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 04/15/2014 :  02:43:27  Show Profile  Reply with Quote
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





Edited by - Villanuev on 04/15/2014 02:47:37

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 04/15/2014 :  03:31:24  Show Profile  Reply with Quote

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





sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 04/15/2014 :  05:49:38  Show Profile  Reply with Quote
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.

Edited by - Villanuev on 04/15/2014 05:50:47
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 04/15/2014 :  06:18:15  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 04/15/2014 :  06:25:07  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 04/15/2014 :  20:24:39  Show Profile  Reply with Quote
Thank you very much!
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 04/15/2014 :  22:26:55  Show Profile  Reply with Quote
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

Edited by - Villanuev on 04/15/2014 22:36:54
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 04/15/2014 :  23:00:20  Show Profile  Reply with Quote
Solved.

Use this code:

DATEADD(HOUR,convert(int,@timezoneOffset), pr.createddatetime)
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 04/16/2014 :  01:09:52  Show Profile  Reply with Quote
Welcome!


sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 05/12/2014 :  03:35:10  Show Profile  Reply with Quote
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


Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 05/12/2014 :  03:38:27  Show Profile  Reply with Quote
CONVERT(VARCHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, D1, D2), 0), 108)



KH
Time is always against us

Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 05/12/2014 :  03:46:35  Show Profile  Reply with Quote

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


output

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



sabinWeb MCP
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
439 Posts

Posted - 05/12/2014 :  03:51:17  Show Profile  Reply with Quote
I haven't refresh the page, so I didn't saw @khtan's post


sabinWeb MCP
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 05/12/2014 :  03:57:24  Show Profile  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
806 Posts

Posted - 05/12/2014 :  14:01:07  Show Profile  Reply with Quote
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.

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 05/12/2014 :  22:10:07  Show Profile  Reply with Quote
Thank you very much guys for giving these solution.

Edited by - Villanuev on 05/12/2014 22:10:36
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000