Query Help Rownumber 13 Dynamic and 12 static valu

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)

Query Help Rownumber 13 Dynamic and 12 static valu

Author

Topic

best_boy26
Starting Member

42 Posts

Posted - 2011-03-17 : 16:08:27

Below is the SQL Query i used to create as view

CREATE VIEW [v_AMP_C] AS


SELECT     dbo.IC_Raw_In.I_Date, dbo.IC_Raw_In.I_O_P, dbo.IC_Raw_In.I_O_H, dbo.IC_Raw_In.I_O_L, dbo.IC_Raw_In.I_C_O, dbo.AMPS12_C.AMPS12_C
FROM         dbo.IC_Raw_In INNER JOIN
                      dbo.AMPS12_C ON dbo.IC_Raw_In.I_Serial = dbo.AMPS12_C.i_serial

and the data is imported to this table by useing a bulk insert

dbo.IC_Raw_In

and data type are Money except I_Date.

Then when i ran the query i.e,.

select * from v_AMP_C

i got the below as output

I_Date	I_O_P	I_O_H	I_O_L	I_C_O	AMPS12_C
01/10/11	509.75	515	508	512.45	512.45
01/10/11	511.7	511.7	506.1499	506.5499	509.4999
01/10/11	507.1499	510.25	507.1499	510.25	509.7499
01/10/11	510	512.3499	509.2999	512.3499	510.3999
01/10/11	512.5	512.5	511.1499	512	510.7199
01/10/11	512.25	512.5	510.1	510.95	510.7583
01/10/11	510.5499	511.7999	510	511.7999	510.9071
01/10/11	511.1	511.85	508.1499	508.8999	510.6562
01/10/11	508.8999	510	508.5	509.95	510.5777
01/10/11	509.8999	509.8999	508.5	508.85	510.4049
01/10/11	509.5	511.2	509	510.5	510.4136
01/10/11	510.5	511.7999	510.1	510.2	510.3958
01/10/11	510.2999	511.35	510.25	510.75	510.2541
01/10/11	510.35	512	510.35	510.95	510.6208
01/10/11	510.95	511.7999	510.6	511.1	510.6916
01/10/11	511.0499	511.35	509.1	509.1	510.4208
01/10/11	509.5	509.5	508.1	508.5	510.1291
01/10/11	508.45	508.95	507	507	509.7999
01/10/11	507	508.2	503.2999	503.2999	509.0916
01/10/11	504	505	503.5	504.6499	508.7374
01/10/11	505.45	506.35	504	504.7	508.2999
01/10/11	504.7	505.5	504.2	505.5	508.0208
01/10/11	505.35	505.7	503.1	503.6499	507.4499
01/10/11	504.5	504.5	499.5499	500.5	506.6416
01/10/11	500.45	502	500.25	501	505.8291
01/10/11	501	501.2999	499.5499	500.3999	504.9499
01/10/11	500.45	500.7999	498.6499	498.6499	503.9124
01/10/11	498.7	499.25	498.0499	498.35	503.0166
01/10/11	498.75	499.95	498.7	499	502.2249
01/10/11	499.25	499.6499	498.6499	499.45	501.5957
01/10/11	499.2999	501.1499	499.1	500.8999	501.3957
01/10/11	501.1	502.5	500.5499	502.5	501.2166
01/10/11	502.35	502.95	501	501.5	500.9499
01/10/11	501.5	501.5	500	500.5	500.5333
01/10/11	500	501.35	499.5	499.7999	500.2124
01/10/11	499.95	500.3999	499.2999	500.2999	500.1957
01/10/11	500	501.3999	499.5	499.6499	500.0832
01/10/11	499.7999	501.25	499.6499	500.0499	500.0541

Now I want write a SQL Query to get results in new Column called C12WR for the below quetion.

I want to exclude(Use

NULL

) the first 11 rows in

C12WR Column

, and in 12th row of C12WR Column "use a static value which is in “AMPS12_C” (In the above shown table results the value is “510.3958” marked as bold). This value will change every time i import the data to my table so the will change dynamically every time. And in the AMPS12_C Column it should calculate the below formula after 13th row to end of the table.

After 13th Row in C12WR Column = (the value of above row (that is currunt row number -1) from C12WR *11 + Current row value from I_C_O Column) /12

so if i caluculate it should represent above formulas as below..(I don't want to use any static values...for the example of this formula I am taking a static values here just for the results to easy of explanation)

=(510.3958*11+510.2)/12

and after run the desired query i should get the output similar to below


I_Date	I_O_P	I_O_H	I_O_L	I_C_O	AMPS12_C	C12WR
01/10/11	509.75	515	508	512.4500122	512.45	NULL
01/10/11	511.7000122	511.7000122	506.1499939	506.5499878	509.4999	NULL
01/10/11	507.1499939	510.25	507.1499939	510.25	509.7499	NULL
01/10/11	510	512.3499756	509.2999878	512.3499756	510.3999	NULL
01/10/11	512.5	512.5	511.1499939	512	510.7199	NULL
01/10/11	512.25	512.5	510.1000061	510.9500122	510.7583	NULL
01/10/11	510.5499878	511.7999878	510	511.7999878	510.9071	NULL
01/10/11	511.1000061	511.8500061	508.1499939	508.8999939	510.6562	NULL
01/10/11	508.8999939	510	508.5	509.9500122	510.5777	NULL
01/10/11	509.8999939	509.8999939	508.5	508.8500061	510.4049	NULL
01/10/11	509.5	511.2000122	509	510.5	510.4136	NULL
01/10/11	510.5	511.7999878	510.1000061	510.2000122	510.3958333	510.3958333
01/10/11	510.2999878	511.3500061	510.25	510.75	510.2541657	510.3795149
01/10/11	510.3500061	512	510.3500061	510.9500122	510.6208344	510.4103887
01/10/11	510.9500122	511.7999878	510.6000061	511.1000061	510.6916682	510.4553573
01/10/11	511.0499878	511.3500061	509.1000061	509.1000061	510.4208374	510.509078
01/10/11	509.5	509.5	508.1000061	508.5	510.1291707	510.3916554
01/10/11	508.4500122	508.9500122	507	507	509.8000031	510.2340174
01/10/11	507	508.2000122	503.2999878	503.2999878	509.0916697	509.964516
01/10/11	504	505	503.5	504.6499939	508.7375031	509.4091386
01/10/11	505.4500122	506.3500061	504	504.7000122	508.3000031	509.0125432
01/10/11	504.7000122	505.5	504.2000122	505.5	508.0208359	508.6531656
01/10/11	505.3500061	505.7000122	503.1000061	503.6499939	507.450002	508.3904018
01/10/11	504.5	504.5	499.5499878	500.5	506.6416677	507.9953678
01/10/11	500.4500122	502	500.25	501	505.8291677	507.3707539
01/10/11	501	501.2999878	499.5499878	500.3999939	504.9499995	506.8398577
01/10/11	500.4500122	500.7999878	498.6499939	498.6499939	503.9124985	506.3032024
01/10/11	498.7000122	499.25	498.0499878	498.3500061	503.0166651	505.665435
01/10/11	498.75	499.9500122	498.7000122	499	502.2249985	505.0558159
01/10/11	499.25	499.6499939	498.6499939	499.4500122	501.5958328	504.5511646
01/10/11	499.2999878	501.1499939	499.1000061	500.8999939	501.3958333	504.1260686
01/10/11	501.1000061	502.5	500.5499878	502.5	501.2166672	503.857229
01/10/11	502.3500061	502.9500122	501	501.5	500.9499995	503.7441266
01/10/11	501.5	501.5	500	500.5	500.5333328	503.557116
01/10/11	500	501.3500061	499.5	499.7999878	500.212499	503.3023564
01/10/11	499.9500122	500.3999939	499.2999878	500.2999878	500.1958313	503.0104923
01/10/11	500	501.3999939	499.5	499.6499939	500.0833308	502.784617
01/10/11	499.7999878	501.25	499.6499939	500.0499878	500.0541636	502.5233984

looking for help to write above SQL Query

Thanks in Advance,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-17 : 22:04:43

what is the ordering / sequence to determine the first 11 rows ?

KH
[spoiler]Time is always against us[/spoiler]

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 00:17:04

it should be based on the row number

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 01:59:30

for the easy of understanding I am posting here my DDL and DML information... looking for help....

---step 1 ----===Create a SQL Table with below SQL Query

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

---step 2 --===Insert the Data into IC_Raw_In Table as a bulk... Since i get the data every time bulk i must use here a bulk data

BULK INSERT dbo.IC_Raw_In FROM 'C:\ABC\InputData.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '' ) GO

---step 3 ---====Create a SQL View for AMPS12_C

USE [abc] GO   SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO 
CREATE VIEW [dbo].[AMPS12_C] AS   WITH RankedPrices AS (SELECT
 i_serial , I_C_O, ROW_NUMBER() OVER (ORDER BY i_serial) AS rn FROM  IC_Raw_In)
 SELECT    a.i_serial, AVG(b.I_C_O) AS AMPS12_C FROM  RankedPrices AS 
a LEFT  JOIN  RankedPrices AS b ON b.rn BETWEEN a.rn-11 AND a.rn  GROUP BY a.i_serial GO

---step 4 ---=== Create a view as v_AMP_C for easy output view


create view v_AMP_C as SELECT dbo.IC_Raw_In.I_Date, dbo.IC_Raw_In.I_O_P, dbo.IC_Raw_In.I_O_H, 
dbo.IC_Raw_In.I_O_L,
 dbo.IC_Raw_In.I_C_O, dbo.AMPS12_C.AMPS12_C, 
dbo.IC_Raw_In.I_Serial FROM   dbo.IC_Raw_In 
INNER JOIN  dbo.AMPS12_C ON dbo.IC_Raw_In.I_Serial = dbo.AMPS12_C.i_serial

---step 5 pending (I am looking for help here)

Now I want write a SQL Query to get results in new Column called C12WR for the below quetion.

I want to exclude(Use NULL) the first 11 rows in C12WR Column, and in 12th row of C12WR Column "use a static value which is in “AMPS12_C” .

This value will change every time i import the data to my table so the will change dynamically every time.
And in the AMPS12_C Column it should calculate the below formula after 13th row to end of the table.

After 13th Row in C12WR Column = (the value of above row (that is currunt row number -1) from C12WR *11 + Current row value from I_C_O Column) /12

After run the desired query i should get the output with C12WR for each row in the table

Let me know if you need any more details..........

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 03:11:15

looks like you need to do it recursively. Use a recursive CTE

you can refer to BOL on this http://msdn.microsoft.com/en-us/library/ms186243.aspx

KH
[spoiler]Time is always against us[/spoiler]

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 03:56:51

I am poor in writing a complex queries... help me please

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 04:38:26

[code]
with rcte as
(
select RN, I_Date, I_O_P, I_O_H, I_O_L, I_C_O, AMPS12_C, C12WR = convert(decimal(20,4), NULL)
from v_AMP_C
where RN = 1

union all

select v.RN, v.I_Date, v.I_O_P, v.I_O_H, v.I_O_L, v.I_C_O, v.AMPS12_C,
C12WR = case when v.RN <= 11 then convert(decimal(20,4), NULL)
when v.RN = 12 then convert(decimal(20,4), v.AMPS12_C)
else convert(decimal(20,4), ((r.AMPS12_C * 11) + v.I_C_O) / 12)
end
from rcte r
INNER JOIN v_AMP_C v on r.RN = v.RN - 1
)
select *
from rcte
[/code]

KH
[spoiler]Time is always against us[/spoiler]

best_boy26
Starting Member

42 Posts

Posted - 2011-03-18 : 04:46:21

Khtan... Getting error as it is not ale to understand the RN

Msg 207, Level 16, State 1, Line 5
Invalid column name 'RN'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'RN'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'RN'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'RN'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'RN'.
Msg 207, Level 16, State 1, Line 11
Invalid column name 'RN'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-18 : 05:08:02

RN is the row number that you mention here