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)
 10 rows to check the high value and do some calcs

Author  Topic 

best_boy26
Starting Member

42 Posts
Posted - 2011-03-20 : 14:48:03
I am looking for a help in writting a sql query...

below is the SQL table i won DDL

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 another two new rows should be ended as results1 and results2..

results1 :--

in the same row the query should run on i_C_O column that is from current row to above 10 rows to check the highest value and return as number of rows since highest value/10*100


results2:--

and same should be done for i_o_P that is in the same row the query should run on i_O_P column that is from current row to above 10 rows to check the low value and return as number of rows since low value/10*100

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-20 : 15:04:31
Can you also post some sample input data and the corresponding output data that you expect? I got only a vague understanding of what you are trying to do; sample data would help tremendously.
Go to Top of Page

best_boy26
Starting Member

42 Posts
Posted - 2011-03-20 : 15:28:42
below is my current data in the table


I_Date	       I_O_P	I_O_H	I_O_L	I_C_O	I_Serial
1/10/2011 9:21	511	511	506	506	2
1/10/2011 9:26	507	510	507	510	3
1/10/2011 9:31	510	512	509	512.3	4
1/10/2011 9:36	512	512	511	512	5
1/10/2011 9:41	512	512	510	510.9	6
1/10/2011 9:46	510	511	510	511.7	7
1/10/2011 9:51	511	511	508	508.8	8
1/10/2011 9:56	508	510	508	509	9
1/10/2011 10:01	509	509	508	508.85	10
1/10/2011 10:06	509	511	509	510.5	11
1/10/2011 10:11	510	511	510	510.2	12
1/10/2011 10:16	510	511	510	510.75	13
1/10/2011 10:21	510	512	510	510.95	14



after applying the caluclations I should get the a similar to below output.


I_Date	       I_O_P	I_O_H	I_O_L	I_C_O	I_Serial result1	    result2
1/10/2011 9:21	511	511	506	506	2	NULL	NULL
1/10/2011 9:26	507	510	507	510	3	NULL	NULL
1/10/2011 9:31	510	512	509	512.3	4	NULL	NULL
1/10/2011 9:36	512	512	511	512	5	NULL	NULL
1/10/2011 9:41	512	512	510	510.9	6	NULL	NULL
1/10/2011 9:46	510	511	510	511.7	7	NULL	NULL
1/10/2011 9:51	511	511	508	508.8	8	NULL	NULL
1/10/2011 9:56	508	510	508	509	9	NULL	NULL
1/10/2011 10:01	509	509	508	508.85	10	NULL	NULL
1/10/2011 10:06	509	511	509	510.5	11	70	90
1/10/2011 10:11	510	511	510	510.2	12	80	40
1/10/2011 10:16	510	511	510	510.75	13	90	50
1/10/2011 10:21	510	512	510	510.95	14	100	60
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-20 : 16:09:27
Tell me if this gives you the right results. I think it does, but my counting may be off by one - not sure.
select
   c1.*,
   case when I_serial > 10 then c2.result1 end as result1,
   case when I_serial > 10 then c3.result2 end as result2
from
   IC_Raw_In c1
   cross apply 
   ( 
      select top 1 (c1.I_serial-c2.I_serial)*100/10 as result1 
      from IC_Raw_In c2 
      where c2.I_serial <= c1.I_serial
         and c2.I_serial >= c1.I_serial-10 
      order by I_C_O desc 
   ) c2
   cross apply 
   ( 
      select top 1 (c1.I_serial-c3.I_serial)*100/10 as result2 
      from IC_Raw_In c3 
      where c3.I_Serial <= c1.I_serial
         and c3.I_serial >= c1.I_serial-10 
      order by I_O_P asc
    ) c3
order by
   I_serial
Go to Top of Page

best_boy26
Starting Member

42 Posts
Posted - 2011-03-20 : 16:24:03
Looks like this will gives me the results.. I have confident on this query.. I will play with it there could be some caluclation logic missing anyhow i will work on it...


Thank you Sunitha
Go to Top of Page

best_boy26
Starting Member

42 Posts
Posted - 2011-03-20 : 16:29:04
I am not looking for the highest value, I am looking to get from above rows (current row - 10 above rows) the highest value listed in ...

the raw data is in this format...

I_Date  I_O_P   I_O_H   I_O_L   I_C_O
1/10/11 511.7   511.7   506.14  506.54
1/10/11 507.14  510.25  507.14  510.25
1/10/11 510     512.34  509.29  512.34
1/10/11 512.5   512.5   511.14  512
1/10/11 512.25  512.5   510.1   510.95
1/10/11 510.54  511.79  510     511.79
1/10/11 511.1   511.85  508.14  508.89
1/10/11 508.89  510     508.5   509.95
1/10/11 509.89  509.89  508.5   508.85
1/10/11 509.5   511.2   509     510.5
1/10/11 510.5   511.79  510.1   510.2
1/10/11 510.29  511.35  510.25  510.75


after running the SQL Query, I am expecting the results should come in this format...


I_Date  I_O_P   I_O_H   I_O_L   I_C_O   I_Serial        result1 result2
1/10/11 511.7   511.7   506.14  506.54  2       NULL    NULL
1/10/11 507.14  510.25  507.14  510.25  3       NULL    NULL
1/10/11 510     512.34  509.29  512.34  4       NULL    NULL
1/10/11 512.5   512.5   511.14  512     5       NULL    NULL
1/10/11 512.25  512.5   510.1   510.95  6       NULL    NULL
1/10/11 510.54  511.79  510     511.79  7       NULL    NULL
1/10/11 511.1   511.85  508.14  508.89  8       NULL    NULL
1/10/11 508.89  510     508.5   509.95  9       NULL    NULL
1/10/11 509.89  509.89  508.5   508.85  10      NULL    NULL
1/10/11 509.5   511.2   509     510.5   11      8/10*100        9/10*100
1/10/11 510.5   511.79  510.1   510.2   12      9/10*100        10/10*100
1/10/11 510.29  511.35  510.25  510.75  13      10/10*100       5/10*100


Just to explain more in the results1 8/10*100, Where 8 is from current row to above 8th row has the highest value and divided by 10 and multiplication of 100.

Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-03-20 : 20:19:11
best_boy, it must be a particularly dense day for me; I am not able to understand your requirement; I thought the query I posted earlier was doing exactly what you are asking it to do.

The sample results you posted initially is different from the latest sample data, so that is adding to my confusion.

The issue may be either or both of the following:

a) does the number of rows you want to go back (which is 10) is inclusive of the current row or is it excluding the current row.

b) when counting the distance from the current row to the row where the max (or min in the case of result2) is found, do you include or exclude the current row.

You can change the behavior of the first by changing <= to <, and you can change the second by adding 1 to the select in the sub-query. See the portions marked in red below.

      select top 1 (1+c1.I_serial-c2.I_serial)*100/10 as result1 
      from IC_Raw_In c2 
      where c2.I_serial <= c1.I_serial
         and c2.I_serial >= c1.I_serial-10 
      order by I_C_O desc
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site