March 18, 2011 at 5:12 am
---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_OPositiveNagtive
100 0
122 0
131 0
150 2
90 6
81 0
53 0
Looking for this query help..........
Thanks in Advance...
trying to learn SQL Query World
March 18, 2011 at 5:25 am
Whats the order of the data? If you want to compare rows like that, they should have a logical order in the data and it only appears you have a data column that could provide that. Also, why are you storing date information as a varchar(50)?
This questions doesn't look very much like an actual SQL Server question as much as it does a question for a class.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 18, 2011 at 5:41 am
i don't understand your comment.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 18, 2011 at 5:42 am
ORDER BY i_serial i will use...
however how to get the data in the same row i.e, the diffrence of two rwos in the current row
Thanks in Advance...
trying to learn SQL Query World
March 18, 2011 at 5:55 am
Thanks it worked after left joining and order by ...
Thanks in Advance...
trying to learn SQL Query World
March 18, 2011 at 5:57 am
Here's the foundation of what you need: (there are some tweaks you'll need though for the zeros and first/last records)
select T1.Serial, (T1.I_C_O - T2.I_C_O) as Positive, (T2.I_C_O - T1.I_C_O) as Negative
from
(Select Serial, I_C_O, IC_Raw_In, row_number() over (order by Serial) as RowNum
from IC_Raw_In) T1
JOIN
(Select Serial, I_C_O, IC_Raw_In, row_number() over (order by Serial) as RowNum
from IC_Raw_In) T2
on T1.RowNum = T2.RowNum+1
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply