Sql 2005 query problem, thanks for your help

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 20, 2010 at 6:53 pm

  #142502

  I have a data table of following:

  Date Close

  1/4/201010583.96

  1/5/201010572.02

  1/6/201010573.68

  1/7/201010606.86

  1/8/201010618.19

  1/11/201010663.99

  1/12/201010627.26

  1/13/201010680.77

  1/14/201010710.55

  1/15/201010609.65

  1/19/201010725.43

  1/20/201010603.15

  1/21/201010389.88

  1/22/201010172.98

  I need to query out the change of the close, descending order. like this:

  Date Close change

  1/19/201010725.430.010912707

  1/13/201010680.770.005035164

  1/11/201010663.990.004313353

  1/7/201010606.860.00313798

  1/14/201010710.550.002788188

  1/8/201010618.190.001068177

  1/6/201010573.680.000157018

  1/5/201010572.02-0.001128122

  1/12/201010627.26-0.003444302

  1/15/201010609.65-0.009420618

  1/20/201010603.15-0.011400942

  1/21/201010389.88-0.020113834

  1/22/201010172.98-0.020876083

  I tried row_number(), etc., but failed. Any suggestions or hints are appreciated.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 20, 2010 at 8:10 pm

  #1122409

  I believe that this is what you are asking.

  Replace [YOUR TABLE NAME] with the name of the table.

  SELECT Date, [Close change]

  FROM [YOUR TABLE NAME]

  ORDER BY [Close change] DESC

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 20, 2010 at 8:27 pm

  #1122411

  Thank you, SSC Veteran.

  The main part is how to calculate the change by SQL query. In original dataset, there is no change column.

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  February 20, 2010 at 9:14 pm

  #1122414

  What is the definition of the Change Column; what does it mean & how can it be derived, i.e what is the formula for determining the Change column or is it stored in another table?

  For better, quicker answers on T-SQL questions, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Paul White

  SSC Guru

  Points: 150468

  More actions

  February 21, 2010 at 12:59 am

  #1122450

  My best guess...

  DECLARE @Data

  TABLE (

  [date] DATETIME NOT NULL PRIMARY KEY,

  [close] DECIMAL(9,2) NOT NULL

  );

  INSERT @Data ([date], [close]) VALUES ('20100104', 10583.96);

  INSERT @Data ([date], [close]) VALUES ('20100105', 10572.02);

  INSERT @Data ([date], [close]) VALUES ('20100106', 10573.68);

  INSERT @Data ([date], [close]) VALUES ('20100107', 10606.86);

  INSERT @Data ([date], [close]) VALUES ('20100108', 10618.19);

  INSERT @Data ([date], [close]) VALUES ('20100111', 10663.99);

  INSERT @Data ([date], [close]) VALUES ('20100112', 10627.26);

  INSERT @Data ([date], [close]) VALUES ('20100113', 10680.77);

  INSERT @Data ([date], [close]) VALUES ('20100114', 10710.55);

  INSERT @Data ([date], [close]) VALUES ('20100115', 10609.65);

  INSERT @Data ([date], [close]) VALUES ('20100119', 10725.43);

  INSERT @Data ([date], [close]) VALUES ('20100120', 10603.15);

  INSERT @Data ([date], [close]) VALUES ('20100121', 10389.88);

  INSERT @Data ([date], [close]) VALUES ('20100122', 10172.98);

  WITH Ordered

  AS (

  -- Assume date order

  SELECT rn = ROW_NUMBER() OVER (ORDER BY [date] ASC),

  [date],

  [close]

  FROM @Data

  )

  SELECT CurrentRow.[date],

  CurrentRow.[close],

  delta = (CurrentRow.[close] - PreviousRow.[close]) / PreviousRow.[close] * 100

  FROM Ordered CurrentRow

  OUTER

  APPLY (

  -- Previous row

  SELECT [close]

  FROM Ordered ORD

  WHERE ORD.rn = CurrentRow.rn - 1

  )

  AS PreviousRow;

  Paul

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 21, 2010 at 8:21 am

  #1122499

  Thank you so much, Paul. You are so helpful.

  One more question, in your script, is the "delta" column sortable? I tried "row_number()" function to generate a column in metadata but I can not sort with the column.

  Appreciate it.

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 21, 2010 at 8:24 am

  #1122500

  "change" is defined as: (current close - previous close)/current close.

  Original data table has "date" as date format, "close" as float format.

  I need to query out the "date", "close", and "change" sorted by "change" descending.

  Thanks.

• Paul White

  SSC Guru

  Points: 150468

  More actions

  February 21, 2010 at 9:23 am

  #1122504

  Yes, sortable.

  Slight change to make the ORDER BY easier to read:

  DECLARE @Data

  TABLE (

  [date] DATETIME NOT NULL PRIMARY KEY,

  [close] DECIMAL(9,2) NOT NULL

  );

  INSERT @Data ([date], [close]) VALUES ('20100104', 10583.96);

  INSERT @Data ([date], [close]) VALUES ('20100105', 10572.02);

  INSERT @Data ([date], [close]) VALUES ('20100106', 10573.68);

  INSERT @Data ([date], [close]) VALUES ('20100107', 10606.86);

  INSERT @Data ([date], [close]) VALUES ('20100108', 10618.19);

  INSERT @Data ([date], [close]) VALUES ('20100111', 10663.99);

  INSERT @Data ([date], [close]) VALUES ('20100112', 10627.26);

  INSERT @Data ([date], [close]) VALUES ('20100113', 10680.77);

  INSERT @Data ([date], [close]) VALUES ('20100114', 10710.55);

  INSERT @Data ([date], [close]) VALUES ('20100115', 10609.65);

  INSERT @Data ([date], [close]) VALUES ('20100119', 10725.43);

  INSERT @Data ([date], [close]) VALUES ('20100120', 10603.15);

  INSERT @Data ([date], [close]) VALUES ('20100121', 10389.88);

  INSERT @Data ([date], [close]) VALUES ('20100122', 10172.98);

  WITH Ordered

  AS (

  -- Assume date order

  SELECT rn = ROW_NUMBER() OVER (ORDER BY [date] ASC),

  [date],

  [close]

  FROM @Data

  )

  SELECT CurrentRow.[date],

  CurrentRow.[close],

  DV.delta

  FROM Ordered CurrentRow

  OUTER

  APPLY (

  -- Previous row

  SELECT [close]

  FROM Ordered ORD

  WHERE ORD.rn = CurrentRow.rn - 1

  )

  AS PreviousRow

  CROSS

  APPLY (

  SELECT delta = (CurrentRow.[close] - PreviousRow.[close]) / PreviousRow.[close] * 100

  )

  AS DV (delta)

  ORDER BY

  DV.delta DESC;

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 21, 2010 at 2:22 pm

  #1122576

  Really appreciate it.

  I am trying to digest it.

• Paul White

  SSC Guru

  Points: 150468

  More actions

  February 21, 2010 at 9:20 pm

  #1122672

  Let me know if you have any trouble understanding and aspect of it.

  The key point is probably to appreciate the APPLY just calls a table-valued function for every row of the outer statement.

  Paul

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• Paul White

  SSC Guru

  Points: 150468

  More actions

  February 23, 2010 at 1:11 am

  #1123463

  If it helps, this is a version that produces the same results and execution plan, but with a perhaps more obvious structure:

  DECLARE @Data

  TABLE (

  [date] DATETIME NOT NULL PRIMARY KEY,

  [close] DECIMAL(9,2) NOT NULL

  );

  INSERT @Data ([date], [close]) VALUES ('20100104', 10583.96);

  INSERT @Data ([date], [close]) VALUES ('20100105', 10572.02);

  INSERT @Data ([date], [close]) VALUES ('20100106', 10573.68);

  INSERT @Data ([date], [close]) VALUES ('20100107', 10606.86);

  INSERT @Data ([date], [close]) VALUES ('20100108', 10618.19);

  INSERT @Data ([date], [close]) VALUES ('20100111', 10663.99);

  INSERT @Data ([date], [close]) VALUES ('20100112', 10627.26);

  INSERT @Data ([date], [close]) VALUES ('20100113', 10680.77);

  INSERT @Data ([date], [close]) VALUES ('20100114', 10710.55);

  INSERT @Data ([date], [close]) VALUES ('20100115', 10609.65);

  INSERT @Data ([date], [close]) VALUES ('20100119', 10725.43);

  INSERT @Data ([date], [close]) VALUES ('20100120', 10603.15);

  INSERT @Data ([date], [close]) VALUES ('20100121', 10389.88);

  INSERT @Data ([date], [close]) VALUES ('20100122', 10172.98);

  SELECT CurrentRow.[date],

  CurrentRow.[close],

  delta = (CurrentRow.[close] - PreviousRow.[close]) / PreviousRow.[close] * 100

  FROM (

  -- Rows from the input table, numbered in ascending

  -- date order

  SELECT rn = ROW_NUMBER() OVER (ORDER BY [date] ASC),

  [date],

  [close]

  FROM @Data

  )

  AS CurrentRow

  LEFT

  JOIN (

  -- Rows from the input table, numbered in ascending

  -- date order

  SELECT rn = ROW_NUMBER() OVER (ORDER BY [date] ASC),

  [date],

  [close]

  FROM @Data

  )

  AS PreviousRow

  ON PreviousRow.rn = CurrentRow.rn - 1

  ORDER BY

  delta DESC;

  Paul

  Paul White
  All articles available on SQL.kiwi
  @SQL_Kiwi

• SQL-Newbie

  SSC Veteran

  Points: 226

  More actions

  February 23, 2010 at 2:07 pm

  #1123883

  Thank you so much. Your code is elegant!

  I can understand your logic well. But if it is time for me to design, I always flounder. My knowledge on SQL is very limited. How should I improve?

  I appreciate your help. Thank you again.