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
 General SQL Server Forums
 New to SQL Server Programming
 Hi, how do you subtract the difference of two

Author  Topic 

JohnCash1
Starting Member

39 Posts

Posted - 2012-10-10 : 14:41:26
Dates in different row in the same table?? Thanks

JohnCash1
Starting Member

39 Posts

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-10-10 : 15:01:22
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 - 2012-10-10 : 20:29:16
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-10 : 21:13:26
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 - 2012-10-10 : 21:37:45
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 - 2012-10-11 : 19:14:24
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
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-10-12 : 06:38:46
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
   

- Advertisement -