Post reply

Query Help Rownumber 13 Dynamic and 12 static values with a formula results to the same row after caluculation as separate column

  • March 17, 2011 at 1:53 pm

    #247105

    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 in Advance,

    JJ

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

  • March 17, 2011 at 1:59 pm

    #1300201

    Please don't double post.

    See the original thread here

    _______________________________________________________________

    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/

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

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