February 20, 2010 at 6:53 pm
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.
February 20, 2010 at 8:10 pm
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/
February 20, 2010 at 8:27 pm
Thank you, SSC Veteran.
The main part is how to calculate the change by SQL query. In original dataset, there is no change column.
February 20, 2010 at 9:14 pm
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/
February 21, 2010 at 12:59 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 8:21 am
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.
February 21, 2010 at 8:24 am
"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.
February 21, 2010 at 9:23 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 21, 2010 at 2:22 pm
Really appreciate it.
I am trying to digest it.
February 21, 2010 at 9:20 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2010 at 1:11 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 23, 2010 at 2:07 pm
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