March 16, 2011 at 2:27 pm
I have below table structure with the similar to below output.
S_NO_T_PO_P H_PL_P C_P SC_12
11509.75 515508 512.4500122
22511.7000122 511.7000122506.1499939506.5499878
34507.1499939510.25507.1499939510.25
45510 512.3499756509.2999878512.3499756
53512.5512.5511.1499939512
68512.25512.5510.1000061510.9500122
71510.5499878511.7999878510511.7999878
82511.1000061511.8500061508.1499939508.8999939
95508.8999939510 508.5509.9500122
10 6 509.8999939509.8999939508.5508.8500061
11 8509.5511.2000122509510.5
12 9510.5511.7999878510.1000061510.2000122**510.4**
13 12510.2999878511.3500061510.25510.75510.25
14 14510.3500061512510.3500061510.9500122510.62
15 15510.9500122511.7999878510.6000061511.1000061510.69
16 17511.0499878511.3500061509.1000061509.1000061510.42
17 13509.5509.5508.1000061508.5510.13
18 13508.4500122508.9500122507 507 509.8
19 19507 508.2000122503.2999878503.2999878509.09
20 11504 505 503.5 504.6499939508.74
21 17505.4500122506.3500061 504504.7000122 508.3
22 18504.7000122505.5504.2000122505.5508.02
23 19505.3500061505.7000122503.1000061503.6499939507.45
I want write a SQL Query to get results in C12WR column.
And I want on row of C12WR Column.use a static value which is in “SC_12” (In the shown table the value is “510.4”) and in the SC_12 Column it should calculate the below formula after 13th row number and it continue it till the end of the table records
After 13th Row in C12WR Column = (the value of above row*11 + Current row value from C_P Column) /12
The final output should be similar to below
S_NO_T_PO_P H_P L_PC_P SC_12 c12-WWR
1 1509.75515 508512.4500122
22511.7000122511.7000122506.1499939506.5499878
34507.1499939510.25507.1499939510.25
45510 512.3499756509.2999878512.3499756
53512.5512.5511.1499939512
68512.25512.5510.1000061510.9500122
71510.5499878511.7999878510511.7999878
82511.1000061511.8500061508.1499939508.8999939
95508.8999939510508.5509.9500122
106509.8999939509.8999939508.5508.8500061
118509.5511.2000122509510.5
129510.5511.7999878510.1000061510.2000122510.4510.4
1312510.2999878511.3500061510.25510.75510.25510.3833344
1414510.3500061512510.3500061510.9500122510.62510.4138898
1515510.9500122511.7999878510.6000061511.1000061510.69510.4585667
1617511.0499878511.3500061509.1000061509.1000061510.42510.51202
1713509.5509.5508.1000061508.5 510.13510.3943521
1818508.4500122508.9500122507507 509.8510.2364895
1919507508.2000122503.2999878503.2999878509.09509.966782
2011504505503.5504.6499939 508.74509.4112158
2117505.4500122506.3500061504504.7000122508.3509.0144473
2218504.7000122505.5504.2000122505.5508.02508.6549111
2319505.3500061505.7000122503.1000061503.6499939 507.45 508.3920018
Thanks in Advance...
trying to learn SQL Query World
March 16, 2011 at 3:18 pm
Can you post the DDL for your tables, some DML to create test data and the query you're using now so we have a starting point?
Please have a look at this article: http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 16, 2011 at 3:43 pm
Best_boy26 (3/16/2011)
I want write a SQL Query to get results in C12WR column.
As mentioned above, please provide DDL and sample data inserts. It will make this easier.
And I want on row of C12WR Column.use a static value which is in “SC_12” (In the shown table the value is “510.4”) and in the SC_12 Column it should calculate the below formula after 13th row number and it continue it till the end of the table records
So this new column should be NULL for the first 11 records?
After 13th Row in C12WR Column = (the value of above row*11 + Current row value from C_P Column) /12
Until we get DDL, I'll give you a verbal method to include into your code. You want to use a ROW_NUMBER in a cte, then join it to itself on rownumber = rownumber - 1 to get the previous row's value, then conclude your calculation, then finally link back to the original table on the PK and perform the update.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 1:54 pm
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[/code]
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[/code] i got the below as output
I_DateI_O_PI_O_HI_O_LI_C_OAMPS12_C
01/10/11509.75515508512.45512.45
01/10/11511.7511.7506.1499506.5499509.4999
01/10/11507.1499510.25507.1499510.25509.7499
01/10/11510512.3499509.2999512.3499510.3999
01/10/11512.5512.5511.1499512510.7199
01/10/11512.25512.5510.1510.95510.7583
01/10/11510.5499511.7999510511.7999510.9071
01/10/11511.1511.85508.1499508.8999510.6562
01/10/11508.8999510508.5509.95510.5777
01/10/11509.8999509.8999508.5508.85510.4049
01/10/11509.5511.2509510.5510.4136
01/10/11510.5511.7999510.1510.2510.3958
01/10/11510.2999511.35510.25510.75510.2541
01/10/11510.35512510.35510.95510.6208
01/10/11510.95511.7999510.6511.1510.6916
01/10/11511.0499511.35509.1509.1510.4208
01/10/11509.5509.5508.1508.5510.1291
01/10/11508.45508.95507507509.7999
01/10/11507508.2503.2999503.2999509.0916
01/10/11504505503.5504.6499508.7374
01/10/11505.45506.35504504.7508.2999
01/10/11504.7505.5504.2505.5508.0208
01/10/11505.35505.7503.1503.6499507.4499
01/10/11504.5504.5499.5499500.5506.6416
01/10/11500.45502500.25501505.8291
01/10/11501501.2999499.5499500.3999504.9499
01/10/11500.45500.7999498.6499498.6499503.9124
01/10/11498.7499.25498.0499498.35503.0166
01/10/11498.75499.95498.7499502.2249
01/10/11499.25499.6499498.6499499.45501.5957
01/10/11499.2999501.1499499.1500.8999501.3957
01/10/11501.1502.5500.5499502.5501.2166
01/10/11502.35502.95501501.5500.9499
01/10/11501.5501.5500500.5500.5333
01/10/11500501.35499.5499.7999500.2124
01/10/11499.95500.3999499.2999500.2999500.1957
01/10/11500501.3999499.5499.6499500.0832
01/10/11499.7999501.25499.6499500.0499500.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_DateI_O_PI_O_HI_O_LI_C_OAMPS12_CC12WR
01/10/11509.75515508512.4500122512.45NULL
01/10/11511.7000122511.7000122506.1499939506.5499878509.4999NULL
01/10/11507.1499939510.25507.1499939510.25509.7499NULL
01/10/11510512.3499756509.2999878512.3499756510.3999NULL
01/10/11512.5512.5511.1499939512510.7199NULL
01/10/11512.25512.5510.1000061510.9500122510.7583NULL
01/10/11510.5499878511.7999878510511.7999878510.9071NULL
01/10/11511.1000061511.8500061508.1499939508.8999939510.6562NULL
01/10/11508.8999939510508.5509.9500122510.5777NULL
01/10/11509.8999939509.8999939508.5508.8500061510.4049NULL
01/10/11509.5511.2000122509510.5510.4136NULL
01/10/11510.5511.7999878510.1000061510.2000122510.3958333510.3958333
01/10/11510.2999878511.3500061510.25510.75510.2541657510.3795149
01/10/11510.3500061512510.3500061510.9500122510.6208344510.4103887
01/10/11510.9500122511.7999878510.6000061511.1000061510.6916682510.4553573
01/10/11511.0499878511.3500061509.1000061509.1000061510.4208374510.509078
01/10/11509.5509.5508.1000061508.5510.1291707510.3916554
01/10/11508.4500122508.9500122507507509.8000031510.2340174
01/10/11507508.2000122503.2999878503.2999878509.0916697509.964516
01/10/11504505503.5504.6499939508.7375031509.4091386
01/10/11505.4500122506.3500061504504.7000122508.3000031509.0125432
01/10/11504.7000122505.5504.2000122505.5508.0208359508.6531656
01/10/11505.3500061505.7000122503.1000061503.6499939507.450002508.3904018
01/10/11504.5504.5499.5499878500.5506.6416677507.9953678
01/10/11500.4500122502500.25501505.8291677507.3707539
01/10/11501501.2999878499.5499878500.3999939504.9499995506.8398577
01/10/11500.4500122500.7999878498.6499939498.6499939503.9124985506.3032024
01/10/11498.7000122499.25498.0499878498.3500061503.0166651505.665435
01/10/11498.75499.9500122498.7000122499502.2249985505.0558159
01/10/11499.25499.6499939498.6499939499.4500122501.5958328504.5511646
01/10/11499.2999878501.1499939499.1000061500.8999939501.3958333504.1260686
01/10/11501.1000061502.5500.5499878502.5501.2166672503.857229
01/10/11502.3500061502.9500122501501.5500.9499995503.7441266
01/10/11501.5501.5500500.5500.5333328503.557116
01/10/11500501.3500061499.5499.7999878500.212499503.3023564
01/10/11499.9500122500.3999939499.2999878500.2999878500.1958313503.0104923
01/10/11500501.3999939499.5499.6499939500.0833308502.784617
01/10/11499.7999878501.25499.6499939500.0499878500.0541636502.5233984
looking for help to write above SQL Query
Thanks,
JJ
Thanks in Advance...
trying to learn SQL Query World
March 17, 2011 at 2:01 pm
JJ,
The data is not in a testable or consumable format for us. You need to read that first link in my signature, it'll explain to you what we're looking for. We're not going to rebuild a method to make your data so we can test a solution or method to provide to you, you're going to have to do that, not just display it, but give us a way to use it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 2:02 pm
As mentioned before if you post ddl and some sample data in a format that makes it easy for people to help you there is little anybody can provide. Please see the link in my signature for best practices on posting a question.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 2:02 pm
Craig Farrell (3/17/2011)
JJ,The data is not in a testable or consumable format for us. You need to read that first link in my signature, it'll explain to you what we're looking for. We're not going to rebuild a method to make your data so we can test a solution or method to provide to you, you're going to have to do that, not just display it, but give us a way to use it.
jinx 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 17, 2011 at 2:19 pm
Can i get the DDL and DML from my SQL Query analyser ?
if so what is the command i can use? please...
Thanks in Advance...
trying to learn SQL Query World
March 17, 2011 at 2:24 pm
Best_boy26 (3/17/2011)
Can i get the DDL and DML from my SQL Query analyser ?if so what is the command i can use? please...
Click on the link, read the article, it will walk you through it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 2:48 pm
OKey... understood the output is not in readable format please find the input and output in excel format
Thanks in Advance...
trying to learn SQL Query World
March 17, 2011 at 2:49 pm
Best_boy26 (3/17/2011)
OKey... understood the output is not in readable format please find the input and output in excel format
Nooooo....
Read the article. You need to generate CREATE TABLE statements, INSERT INTO statements, etc. There is no easy button. You need to do some work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 17, 2011 at 11:45 pm
My apology that I could not get the DDL and DML... finally I got it from SQL...(I finally learned how to get these DDL and DML.. it's interesting learning
---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 similar the attached sample excel file name Called finall output data sample.xlsx (for getting this sample excel file output i need a sql query and posting seeking help for C12WR )
Let me know if you need any more details..........
Thanks in Advance...
trying to learn SQL Query World
March 18, 2011 at 12:35 am
The data is not in a testable or consumable format for us. You need to read that first link in my signature, it'll explain to you what we're looking for. We're not going to rebuild a method to make your data so we can test a solution or method to provide to you, you're going to have to do that, not just display it, but give us a way to use it.
March 18, 2011 at 2:50 am
I have posted here step by step all i have done and what ever i am trying to do here...
Please let me know what data you don't have in readble format.. what format you are expecting
Thanks in Advance...
trying to learn SQL Query World
March 18, 2011 at 3:46 am
thanks this worked after using the http://msdn.microsoft.com/en-us/library/ms186243.aspx
Thanks in Advance...
trying to learn SQL Query World
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply