Help need in writing Query with cte

• Best_boy26

  SSC-Addicted

  Points: 457

  More actions

  March 18, 2011 at 1:23 pm

  #235357

  I have a SQL table with below DDL.

  USE [abc]

  GO

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[v_Devide](

  [i_S.NO] [nchar](10) NULL,

  [data_14] [money] NULL,

  [data_15] [money] NULL

  ) ON [PRIMARY]

  GO

  When i run a SQL Query

  select * from dbo.v_Devide

  I have below data in the table

  i_S.NO data_14data_15

  10.7214268290.828569686

  20.6806101910.769386137

  30.6319951770.857287127

  40.5868526650.838909911

  50.5449346170.886130631

  60.5060107161.087122172

  70.56629611.009470588

  80.5294191150.937365546

  90.5487454490.870410864

  Now I want to perform a caluculation againest of data_14 and Data_15 and end as a new column

  and in the end as new column I should caluculate the = 100-(100/(1+data_14/Data_15))

  this should be done on each row ending... on complete table...

  so expected results would be...

  i_S.NOdata_14 data_15New_Column

  10.7214268290.82856968646.54377103

  20.6806101910.76938613746.93875273

  30.6319951770.85728712742.43622417

  40.5868526650.83890991141.16061641

  50.5449346170.88613063138.07894976

  60.5060107161.08712217231.76199046

  70.56629611.00947058835.93781392

  80.5294191150.93736554636.09385404

  90.5487454490.87041086438.66701955

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

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  March 18, 2011 at 1:32 pm

  #1300775

  select i_s.NO,data_14,data_15,(((data_14/Data_15)+1)/100)-100 as New_Column from dbo.v_Devide

  unless I have vastly misunderstood I am not sure what the cte in teh subject is in reference too.

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.