requirsive Time Difference query

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)

requirsive Time Difference query

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2010-08-12 : 07:28:23

Hello
I want to Compare Hours difference between Orders Date in Year 1997 vs year 1998 in NorthWind DB.
I use for loop in asp.net to do this , but I also wanted to know if there is any to use only TSQL for this.

Regards

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 10:00:10

I want to Compare Hours difference between Orders Date in Year 1997 vs year 1998

Please explain in more detail.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

s2002
Starting Member

49 Posts

Posted - 2010-08-12 : 12:44:35

Hello

Orders Table scheme

OrderID OrderDate
1720 1/1/2010
1721 2/1/2010
1722 4/1/2010

Note: OrderID 1721 ordered 24 hours after OrderID 1720 and OrderID 1722 ordered 48 hours after OrderID 1721. I want to have these time intervals.

So query result be some thing like below

OrderID TimeDifference
1720 0
1721 24
1722 48

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-12 : 13:27:23

Here's one way

--Sample data
declare @Orders table
(OrderID int, OrderDate datetime)
insert @Orders
select 1720, '20100101'
union all select 1721, '20100102'
union all select 1722, '20100104'

--Query
;with cte1 (seq,OrderID,OrderDate)
AS
(
select row_number() over(order by OrderID) as seq , * from @Orders
) 

select a.OrderID,coalesce(datediff(hh,b.OrderDate,a.OrderDate),0) as [Diff]
from cte1 a
left join cte1 b on a.seq = b.seq + 1

--Result
OrderID     Diff
----------- -----------
1720        0
1721        24
1722        48

s2002
Starting Member

49 Posts

Posted - 2010-08-12 : 17:31:10

Thanks for your reply,
When I customize query with DB Table I encounter "Invalid column name 'seq'. " error for Last Line!

Could you also please tell me more about your query structure. what's the role of "seq" , and
;with cte1 (seq,OrderID,OrderDate)

gautham09
Starting Member

2 Posts

Posted - 2010-08-12 : 18:28:56

CREATE TABLE PK1(NO INT, ORDERNAME VARCHAR(20) ,DT DATE)
INSERT INTO PK1 VALUES(6,'MELL','2010-01-05')
INSERT INTO PK1 VALUES(2,'MELL','2010-01-02')
INSERT INTO PK1 VALUES(8,'MELL','2010-01-08')
SELECT X.*,
coalesce(DATEDIFF(hh,X.DT,Y.DT),0)
FROM
(SELECT ROW_NUMBER()OVER(ORDER BY NO) AS SEQ,
NO,ORDERNAME,DT FROM pk1) X,

(SELECT ROW_NUMBER()OVER(ORDER BY NO) AS SEQ,
NO,ORDERNAME,DT FROM pk1
) Y
WHERE X.SEQ=Y.SEQ+1
VIJAY SOLUTION IS ELEGANT AND NICE SOLUTION

s2002
Starting Member

49 Posts

Posted - 2010-08-12 : 22:17:27

vijayisonl & gautham09 your both solutions working fine
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-13 : 10:03:47

Great. You're welcome. What I have used is a Common table Expression (CTE). You can read about it in BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources