Sql 2005 query problem, thanks for your help

  • 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.

  • 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/

  • Thank you, SSC Veteran.

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

  • 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/

  • 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

  • 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.

  • "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.

  • 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;

  • Really appreciate it.

    I am trying to digest it.

  • 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

  • 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

  • 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.

Viewing 12 posts - 1 through 11 (of 11 total)

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