10 rows to check the high value and do some calcs

Question

10 rows to check the high value and do some calcs

Best_boy26

SSC-Addicted

Points: 457
More actions
March 20, 2011 at 12:50 pm

#236522

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
Thanks in Advance...
trying to learn SQL Query World

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please provide some sample data in the form "INSERT INTO ... SELECT ... UNION ALL" and your expected result based on the sample. Especially, please make sure to explain what you mean by "above 10 rows".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Try this:

with cte as

(

select rn = ROW_NUMBER() over( order by (select null)) ,

i_c_o

from IC_Raw_In

)

, tiled as

(

select NT = NTILE(10) over (order by rn) , rn , i_c_o from cte

)

select i_c_o , MAX_ico = MAX(i_c_o) over(partition by nt)

, result1 = (( MAX(i_c_o) over(partition by nt) ) / 10 ) * 100

from tiled

Best_boy26 SSC-Addicted Points: 457 More actions · Answer 3

Do you know any easy way to get the data for this from exisiting table and exisitng data to post as a sample... ? I mean from SQL Studio Management ?

Thanks in Advance...
trying to learn SQL Query World

Best_boy26 SSC-Addicted Points: 457 More actions · Answer 4

Currently MAX_ico is the value coming from I_C_O highest value from last above and below rows, this should be set to check the value all the time from above.

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 what row...

I_DateI_O_PI_O_HI_O_LI_C_O

1/10/11511.7511.7506.14506.54

1/10/11507.14510.25507.14510.25

1/10/11510512.34509.29512.34

1/10/11512.5512.5511.14512

1/10/11512.25512.5510.1510.95

1/10/11510.54511.79510511.79

1/10/11511.1511.85508.14508.89

1/10/11508.89510508.5509.95

1/10/11509.89509.89508.5508.85

1/10/11509.5511.2509510.5

1/10/11510.5511.79510.1510.2

1/10/11510.29511.35510.25510.75

after running the SQL Query

I_DateI_O_PI_O_HI_O_LI_C_OI_Serialresult1result2

1/10/11511.7511.7506.14506.542NULLNULL

1/10/11507.14510.25507.14510.253NULLNULL

1/10/11510512.34509.29512.344NULLNULL

1/10/11512.5512.5511.145125NULLNULL

1/10/11512.25512.5510.1510.956NULLNULL

1/10/11510.54511.79510511.797NULLNULL

1/10/11511.1511.85508.14508.898NULLNULL

1/10/11508.89510508.5509.959NULLNULL

1/10/11509.89509.89508.5508.8510NULLNULL

1/10/11509.5511.2509510.5118/10*1009/10*100

1/10/11510.5511.79510.1510.2129/10*10010/10*100

1/10/11510.29511.35510.25510.751310/10*1005/10*100

Just to explain more in the results 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.

Thanks in Advance...
trying to learn SQL Query World

LutzM SSC Guru Points: 107049 More actions · Answer 5

What is the order criteria to get the values for I_Serial?

Regarding getting ready to use sample data out of SSMS: please have a look at the first article referenced in my signature. There's a script sample included.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Best_boy26 (3/20/2011)
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 what row...

My query is exactly doing the same thing , but it is not placing the result in the 11th row, instead populates in all the rows in that range.. if thats not what u are after, can u explain further what using ur sample data?

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 7

Best_boy26 (3/20/2011)
Do you know any easy way to get the data for this from exisiting table and exisitng data to post as a sample... ? I mean from SQL Studio Management ?

Install SSMS Tools Pack[/url] so you can right click the Grid Results Pane after running any select statement and choose "Script Grid Results".

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Best_boy26 SSC-Addicted Points: 457 More actions · Answer 8

Thank you opc.three .............. You saved made my job easy for any time if i want to post the results in the format of insert or update!!!!!!!!!!!!

Thanks in Advance...
trying to learn SQL Query World

WayneS SSC Guru Points: 95461 More actions · Answer 9

Best_boy26 (3/20/2011)
Do you know any easy way to get the data for this from exisiting table and exisitng data to post as a sample... ? I mean from SQL Studio Management ?

Please read the first link in my signature below.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 10

Glad to help...now please use it to post some DML so we can help you with your solution 😉

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato