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
 General SQL Server Forums
 New to SQL Server Programming
 Hi, how do you subtract the difference of two
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JohnCash1
Starting Member

39 Posts

Posted - 10/10/2012 :  14:41:26  Show Profile  Reply with Quote
Dates in different row in the same table?? Thanks

JohnCash1
Starting Member

39 Posts

Posted - 10/10/2012 :  14:45:28  Show Profile  Reply with Quote
Do this in the where statement base on criteria of the columns? Thx
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/10/2012 :  15:01:22  Show Profile  Reply with Quote
You need to JOIN two instances of the table and correlate the JOIN so that the two dates would show in the same row of a SELECT result. Once that is accomplished you can use any of the DATETIME functions like DATEADD, DATEDIFF, etc.

Be One with the Optimizer
TG
Go to Top of Page

JohnCash1
Starting Member

39 Posts

Posted - 10/10/2012 :  20:29:16  Show Profile  Reply with Quote
you mean to say to join the same table?

Could you show an example?

how do you put two different rows in the same table to a single row in the same table?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/10/2012 :  21:13:26  Show Profile  Reply with Quote
Here is an example. You can copy the code and run it from SSMS query window to see what it does:
-- create a test table and populate
CREATE TABLE #tmp(id INT, Dt DATETIME);
INSERT INTO #tmp VALUES (1,'20120101');
INSERT INTO #tmp VALUES (2,'20120107');
INSERT INTO #tmp VALUES (3,'20120112');
INSERT INTO #tmp VALUES (4,'20120122');

-- select id, date and date from the next row.
SELECT
	a.id,
	a.Dt AS DateFromThisRow,
	b.Dt AS DateFromNextRow
FROM
	#tmp a
	LEFT JOIN #tmp b ON a.id+1 = b.id;

-- cleanup
DROP TABLE #tmp;
Go to Top of Page

JohnCash1
Starting Member

39 Posts

Posted - 10/10/2012 :  21:37:45  Show Profile  Reply with Quote
cool,thanks. i'll give it a try and if I have any Q, i'll let you know.
Go to Top of Page

JohnCash1
Starting Member

39 Posts

Posted - 10/11/2012 :  19:14:24  Show Profile  Reply with Quote
I see what you did there. I noticed it only works when your id field is a number and it increments. What if your id field is text and they are all the same?
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 10/12/2012 :  06:38:46  Show Profile  Reply with Quote
This will give you the same result, but you would really need a better column to order by in the row_number(possibly dt to keep them in chronological order).
-- create a test table and populate
CREATE TABLE #tmp(id VARCHAR(5), Dt DATETIME);
INSERT INTO #tmp VALUES ('sdfew','20120101');
INSERT INTO #tmp VALUES ('sdfew','20120107');
INSERT INTO #tmp VALUES ('sdfew','20120112');
INSERT INTO #tmp VALUES ('sdfew','20120122');

-- select id, date and date from the next row.

with cte (id,dt,rn)
as
(SELECT
	id
	,Dt AS DateFromThisRow	
	,ROW_NUMBER() OVER(order by id) as RN
FROM
	#tmp 
)

SELECT
	a.id,
	a.Dt AS DateFromThisRow,
	b.Dt AS DateFromNextRow
FROM
	cte a
LEFT JOIN 
	cte b ON a.rn+1 = b.rn;

drop table #tmp









How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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.08 seconds. Powered By: Snitz Forums 2000