Row values diffrence in same row

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)

Row values diffrence in same row

Author

Topic

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 07:09:07

---I have below SQL Table.....

USE [abc]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[IC_Raw_In](
	[I_Date] [varchar](50) NULL,
	[I_O_P] [money] NULL,
	[I_O_H] [money] NULL,
	[I_O_L] [money] NULL,
	[I_C_O] [money] NULL,
	[I_Serial] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

--

Now i want to query on every row of I_C_O of last two rows difference, say example if the data has like below.

I_C_O
10
12
13
15
9
8
5

now I want the difference in sepearate two columns called positive and Nagtive Columns. and the fist row should be 0 in positive and Nagtive.

from there onwords it should show the diffrence of last two rows of I_C_O, if the diffrence is in Positive (+) show in Positive Column else show in Nagtive Column (- value).


I_C_O	Positive	Nagtive
10	0                0
12	2                0
13	1               0
15	0		2
9	0		6
8	1               0
5	3               0

Looking for this query help..........

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 07:25:55

How is the order of the records guaranteed? Without that you can't write a query that will always produce the same results.

Jim

Everyday I learn something that somebody else already knew

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 07:31:06

(ORDER BY i_serial )
order by is fine to have however, how would i get the difference in sepearate two columns called positive and Nagtive Columns.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 07:40:22

Something like this I guess

SELECT t1.I_C_O
, [Positive] = CASE WHEN t2.i_c_o - t1.i_c_o > 0 THEN t2.ico - t1.ico ELSE 0 END
, [Negative] = CASE WHEN t2.i_c_o - t1.i_c_o <= 0 THEN t2.ico - t1.ico ELSE 0 END

FROM IC_Raw_In t1
LEFT JOIN
IC_Raw_In t2

ON t1.id = t2.id - 1

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 07:45:46

;with cte as

(select iSerial,i_c_o
,[rown] = row_number() over(order by iserial)
from IC_Raw_In
)

SELECT t1.I_C_O
, [Positive] = CASE WHEN t2.i_c_o - t1.i_c_o > 0 THEN t2.i_c_o - t1.i_c_o ELSE 0 END
, [Negative] = CASE WHEN t2.i_c_o - t1.i_c_o <= 0 THEN t2.i_c_o - t1.i_c_o ELSE 0 END

FROM cte t1
LEFT JOIN
cte t2
ON t1.rown = t2.rown - 1

Jim

Everyday I learn something that somebody else already knew

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 07:53:56

it worked for me JIMF..

Thanks really help full for me

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-18 : 08:21:15

No problem ChazMan. What was wrong with the answer you got at Sequel Server Central?

Jim

Everyday I learn something that somebody else already knew

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 08:40:10

I am useing yours only.... the first comes is the best answers for me...