happy 1001
asked on
SQL Server Percent Rank
I need to create a query that will show 6 more columns in addition to the columns that are already present in this test table, by the names of -
PercentRank-Volume-4Days,
PercentRank-Volume-5Days,
PercentRank-Volume-6Days
And
PercentRank-OpenInterest-4
PercentRank-OpenInterest-5
PercentRank-OpenInterest-6
Which will be showing the [PERCENT RANK] for PREVIOUS 4,5 and 6 day values of column named [Volume] and [OpenInterest] respectively.
By Previous 4 days, I mean the previous 4 dates as shown in the column [xdate] and this could be refereed in the query by using some method like this -
or maybe some other method which might be more efficient for referring to the past 4, 5 and 6 days data based on column [xdate] values respectively.
One example of Percent Rank function is available at this link - http://stevestedman.com/2012/03/more-tsql-analytic-functions-percent_rank/
Here is the script that will create the Sample Database named " TestDB1001 " and Table named " Table1001 " used in this work.
Regards
PercentRank-Volume-4Days,
PercentRank-Volume-5Days,
PercentRank-Volume-6Days
And
PercentRank-OpenInterest-4
PercentRank-OpenInterest-5
PercentRank-OpenInterest-6
Which will be showing the [PERCENT RANK] for PREVIOUS 4,5 and 6 day values of column named [Volume] and [OpenInterest] respectively.
By Previous 4 days, I mean the previous 4 dates as shown in the column [xdate] and this could be refereed in the query by using some method like this -
FROM [Table1001]
WHERE [Table1001].XDATE >= (
SELECT Min(ts)
FROM (
SELECT DISTINCT TOP 4 XDATE AS [ts]
FROM [Table1001]
ORDER BY XDATE DESC
) A
)or maybe some other method which might be more efficient for referring to the past 4, 5 and 6 days data based on column [xdate] values respectively.
One example of Percent Rank function is available at this link - http://stevestedman.com/2012/03/more-tsql-analytic-functions-percent_rank/
Here is the script that will create the Sample Database named " TestDB1001 " and Table named " Table1001 " used in this work.
CREATE DATABASE TestDB1001
GO
USE TestDB1001
GO
CREATE TABLE TestDB1001.dbo.Table1001 (
xdate DATETIME2(0) NULL,
sector NVARCHAR(255) NULL,
symbol NVARCHAR(255) NULL,
[Close Price ] FLOAT NULL,
Volume FLOAT NULL,
[Volume Percent ] FLOAT NULL,
OpenInterest FLOAT NULL,
[OpenInterest Percent] FLOAT NULL
) ON [PRIMARY]
GO
SET DATEFORMAT ymd
SET ARITHABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS, XACT_ABORT OFF
GO
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-01 00:00:00.0000000', N'Realty', N'DLF', 171.45, 236.57, 16.5, 657.59007, 6.88)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-01 00:00:00.0000000', N'Realty', N'HDIL', 55.6, 89.4, 61.43, 152.6776, 17.62)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-01 00:00:00.0000000', N'Realty', N'UNITECH', 15.9, 120.13, 45, 282.43848, 6.4)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-02 00:00:00.0000000', N'Realty', N'DLF', 166.45, 420.65, 77.81, 658.68621999999993, 0.17)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-02 00:00:00.0000000', N'Realty', N'HDIL', 51.85, 124.27, 39, 147.08239999999998, -3.67)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-02 00:00:00.0000000', N'Realty', N'UNITECH', 15.35, 119.73, -0.33, 273.67355999999995, -3.1)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-03 00:00:00.0000000', N'Realty', N'DLF', 169, 369.57, -12.14, 707.74436999999989, 7.45)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-03 00:00:00.0000000', N'Realty', N'HDIL', 53.5, 87.35, -29.71, 158.88595999999998, 8.03)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-03 00:00:00.0000000', N'Realty', N'UNITECH', 15.35, 57.75, -51.77, 279.11279999999994, 1.99)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-06 00:00:00.0000000', N'Realty', N'DLF', 167.25, 217.96, -41.02, 722.15043999999989, 2.04)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-06 00:00:00.0000000', N'Realty', N'HDIL', 52.95, 50.37, -42.34, 157.18616000000006, -1.07)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-06 00:00:00.0000000', N'Realty', N'UNITECH', 15.3, 38.19, -33.87, 286.6113, 2.69)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-07 00:00:00.0000000', N'Realty', N'DLF', 164.6, 265.73, 21.92, 710.63243, -1.5899999999999999)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-07 00:00:00.0000000', N'Realty', N'HDIL', 52.15, 108.35, 115.11, 167.99551999999997, 6.87)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-07 00:00:00.0000000', N'Realty', N'UNITECH', 15, 70.69, 85.1, 290.49611999999991, 1.3599999999999999)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-08 00:00:00.0000000', N'Realty', N'DLF', 162.9, 258.36, -2.77, 697.20230999999978, -1.8900000000000001)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-08 00:00:00.0000000', N'Realty', N'HDIL', 53.3, 70.91, -34.55, 174.64184, 3.95)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-08 00:00:00.0000000', N'Realty', N'UNITECH', 14.95, 55.43, -21.59, 291.71525999999994, 0.42)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-09 00:00:00.0000000', N'Realty', N'DLF', 159.5, 186.23, -27.92, 699.0476, 0.27)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-09 00:00:00.0000000', N'Realty', N'HDIL', 50.2, 103.81, 46.4, 182.34456, 4.41)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-09 00:00:00.0000000', N'Realty', N'UNITECH', 14.7, 50.19, -9.45, 294.89627999999988, 1.09)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-10 00:00:00.0000000', N'Realty', N'DLF', 156.85, 200.27, 7.54, 699.8979999999998, 0.12)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-10 00:00:00.0000000', N'Realty', N'HDIL', 49.55, 91.61, -11.75, 180.54768, -0.98)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-10 00:00:00.0000000', N'Realty', N'UNITECH', 14.25, 56.77, 13.11, 296.6907599999999, 0.61)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-13 00:00:00.0000000', N'Realty', N'DLF', 161, 197.51, -1.38, 703.72603999999978, 0.55)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-13 00:00:00.0000000', N'Realty', N'HDIL', 49.75, 72.79, -20.54, 183.90999999999997, 1.8599999999999999)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-13 00:00:00.0000000', N'Realty', N'UNITECH', 13.8, 61.04, 7.52, 300.79445999999996, 1.38)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-14 00:00:00.0000000', N'Realty', N'DLF', 157.8, 188.98, -4.32, 728.44625999999982, 3.51)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-14 00:00:00.0000000', N'Realty', N'HDIL', 49.6, 79.6, 9.36, 203.92476000000008, 10.88)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-14 00:00:00.0000000', N'Realty', N'UNITECH', 13.5, 51.78, -15.17, 305.99279999999982, 1.73)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-15 00:00:00.0000000', N'Realty', N'DLF', 160.5, 197.29, 4.4, 728.82591000000036, 0.05)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-15 00:00:00.0000000', N'Realty', N'HDIL', 51.3, 73.09, -8.18, 208.26824, 2.13)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-15 00:00:00.0000000', N'Realty', N'UNITECH', 14.2, 125.87, 143.09, 317.5926, 3.79)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-16 00:00:00.0000000', N'Realty', N'DLF', 162.4, 208.88, 5.87, 735.66176, 0.94)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-16 00:00:00.0000000', N'Realty', N'HDIL', 50.65, 68.36, -6.47, 208.24432000000002, -0.01)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-16 00:00:00.0000000', N'Realty', N'UNITECH', 13.95, 70.59, -43.92, 314.64828000000006, -0.93)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-17 00:00:00.0000000', N'Realty', N'DLF', 155.6, 284.44, 36.17, 756.6858900000002, 2.86)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-17 00:00:00.0000000', N'Realty', N'HDIL', 48.6, 86.94, 27.18, 210.18943999999993, 0.94)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-17 00:00:00.0000000', N'Realty', N'UNITECH', 13.65, 55.82, -20.92, 313.36584, -0.41)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-20 00:00:00.0000000', N'Realty', N'DLF', 156.1, 238.3, -16.22, 784.67971000000011, 3.7)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-20 00:00:00.0000000', N'Realty', N'HDIL', 49.55, 73.1, -15.92, 213.88799999999998, 1.76)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-20 00:00:00.0000000', N'Realty', N'UNITECH', 13.8, 76.18, 36.47, 318.67127999999997, 1.69)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-21 00:00:00.0000000', N'Realty', N'DLF', 157.8, 201.98, -15.24, 788.57473000000016, 0.5)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-21 00:00:00.0000000', N'Realty', N'HDIL', 50.9, 71.67, -1.96, 218.67824000000007, 2.24)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-21 00:00:00.0000000', N'Realty', N'UNITECH', 13.75, 44.2, -41.98, 318.27546, -0.12)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-22 00:00:00.0000000', N'Realty', N'DLF', 159.1, 194.93, -3.49, 794.0979600000004, 0.7)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-22 00:00:00.0000000', N'Realty', N'HDIL', 50.8, 68.03, -5.08, 217.37912000000003, -0.59)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-22 00:00:00.0000000', N'Realty', N'UNITECH', 13.75, 23.48, -46.88, 323.47625999999991, 1.63)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-23 00:00:00.0000000', N'Realty', N'DLF', 159.05, 199.54, 2.36, 801.66750000000025, 0.95)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-23 00:00:00.0000000', N'Realty', N'HDIL', 50.45, 61.91, -9, 218.71092000000002, 0.61)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-23 00:00:00.0000000', N'Realty', N'UNITECH', 13.7, 31.98, 36.2, 322.67046000000005, -0.25)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-24 00:00:00.0000000', N'Realty', N'DLF', 152.8, 396.39, 98.65, 882.00591000000031, 10.02)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-24 00:00:00.0000000', N'Realty', N'HDIL', 48.1, 105.47, 70.36, 226.20192000000003, 3.43)
INSERT TestDB1001.dbo.Table1001(xdate, sector, symbol, [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]) VALUES ('2014-01-24 00:00:00.0000000', N'Realty', N'UNITECH', 13.5, 89.6, 180.18, 337.96794000000006, 4.74)
GORegards
Try this please. I'm not certain about the OpenInterest in particular, do you want to SUM() the prior 4, 5, 6 days? or is in an average you want?
& That's before the percent_rank() is applied
& That's before the percent_rank() is applied
select
ca4.*
, ca5.*
, ca6.*
, t1.*
from Table1001 as t1
cross apply (
select
sum(Volume) as Volume4day
, sum(OpenInterest) as OpenInterest4day
from Table1001 as t4
where t4.xdate between dateadd(day,-4,t1.xdate) and t1.xdate
) as ca4
cross apply (
select
sum(Volume) as Volume5day
, sum(OpenInterest) as OpenInterest5day
from Table1001 as t5
where t5.xdate between dateadd(day,-5,t1.xdate) and t1.xdate
) as ca5
cross apply (
select
sum(Volume) as Volume6day
, sum(OpenInterest) as OpenInterest6day
from Table1001 as t6
where t6.xdate between dateadd(day,-6,t1.xdate) and t1.xdate
) as ca6
and I fully concur with Brian, thank you very much for the great question preparation!
ASKER
Thank you Brian Crowe and Paul Maxwell for the appreciation. I try to make it as easy as possible, for someone who is trying to help me.
I am sorry I forgot to mention this earlier, I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office 2013 x64
and Windows 7 x64
Hopefully now much efficient method could be used for this work, rather then using the inefficient TOP 4/5/6 subqueries code that I posted earlier.
I have one question for the administrators -
If I want to EDIT my first post, for adding the SQL Server Version Information etc. then how can I do that ? I am not seeing an option to EDIT that first post. Please suggest.
@ Paul, I am sorry, but I am a bit confused about your question. We are not supposed to do any total or average in this particular query.
We simply want to calculate the percent rank of these 2 columns [Volume] and [OpenInterest]
for PREVIOUS 4,5 and 6 day values respectively.
For example, the latest percent rank of Volume Value will be different if it is calculated based on past 4 days data, Vs if it is calculated based on past 20 days data and so on.
I am looking for the percent rank values of 3 different time durations each for the two columns I mentioned.
Hopefully the requirement is clear to you now. If still in doubts then please ask, I will explain more.
The confusions arises because I am working with Stock Market Databases which are quite different then the other databases which are used in other fields.
Thanks a lot for your help.
I am sorry I forgot to mention this earlier, I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office 2013 x64
and Windows 7 x64
Hopefully now much efficient method could be used for this work, rather then using the inefficient TOP 4/5/6 subqueries code that I posted earlier.
I have one question for the administrators -
If I want to EDIT my first post, for adding the SQL Server Version Information etc. then how can I do that ? I am not seeing an option to EDIT that first post. Please suggest.
@ Paul, I am sorry, but I am a bit confused about your question. We are not supposed to do any total or average in this particular query.
We simply want to calculate the percent rank of these 2 columns [Volume] and [OpenInterest]
for PREVIOUS 4,5 and 6 day values respectively.
For example, the latest percent rank of Volume Value will be different if it is calculated based on past 4 days data, Vs if it is calculated based on past 20 days data and so on.
I am looking for the percent rank values of 3 different time durations each for the two columns I mentioned.
Hopefully the requirement is clear to you now. If still in doubts then please ask, I will explain more.
The confusions arises because I am working with Stock Market Databases which are quite different then the other databases which are used in other fields.
Thanks a lot for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ Paul Maxwell, thank again for the great help.
Let me try to explain the requirement in different manner.
We do not have to change the format of the table in the query output. It will remain the same, as shown in the attached excel file and the snapshot. We will basically Order By - xdate and symbol. The final query output will have the following columns in this sequence -
xdate
sector
symbol
Close Price
Volume
Volume Percent
OpenInterest
OpenInterest Percent
PercentRank-Volume-4Days
PercentRank-Volume-5Days
PercentRank-Volume-6Days
PercentRank-OpenInterest-4
PercentRank-OpenInterest-5
PercentRank-OpenInterest-6
I know that it is not easy to design this query. Generally one need to calculate the Percentile Rank of One Item based on the Complete Dataset. But my requirement is quite different.
I have to calculate the Percent Rank for Each Symbol Separately, which is given in the [Symbol] Column
and this rank should NOT be based on the Entire Dataset, but based on the past 4,5,6 Day Values of Volume and OpenInterest Columns.
That makes it Complicated for most programmers.
But hopefully, someone will be able to figure it out somehow.
Thanks a lot for any help in making such kind of Percent Ranks possible.
With my best regards
Percentile-Rank-Query-Format.xlsx
Let me try to explain the requirement in different manner.
We do not have to change the format of the table in the query output. It will remain the same, as shown in the attached excel file and the snapshot. We will basically Order By - xdate and symbol. The final query output will have the following columns in this sequence -
xdate
sector
symbol
Close Price
Volume
Volume Percent
OpenInterest
OpenInterest Percent
PercentRank-Volume-4Days
PercentRank-Volume-5Days
PercentRank-Volume-6Days
PercentRank-OpenInterest-4
PercentRank-OpenInterest-5
PercentRank-OpenInterest-6
I know that it is not easy to design this query. Generally one need to calculate the Percentile Rank of One Item based on the Complete Dataset. But my requirement is quite different.
I have to calculate the Percent Rank for Each Symbol Separately, which is given in the [Symbol] Column
and this rank should NOT be based on the Entire Dataset, but based on the past 4,5,6 Day Values of Volume and OpenInterest Columns.
That makes it Complicated for most programmers.
But hopefully, someone will be able to figure it out somehow.
Thanks a lot for any help in making such kind of Percent Ranks possible.
With my best regards
Percentile-Rank-Query-Format.xlsx
sigh...
the PERCENT_RANK() function takes NO parameters
So, the only way for multiple percent_rank columns to be different is to alter the ORDER BY
Please try producing some results using the code I have provided so far. Then you might understand my frustration.
Note: In this question, and a very similar one, I have shown 2 methods of calculating the the metrics such as Volume-4Days. Now it is up to you to show how you want "Percent Rank" applied to that column.
please, do try.
the PERCENT_RANK() function takes NO parameters
So, the only way for multiple percent_rank columns to be different is to alter the ORDER BY
Please try producing some results using the code I have provided so far. Then you might understand my frustration.
Note: In this question, and a very similar one, I have shown 2 methods of calculating the the metrics such as Volume-4Days. Now it is up to you to show how you want "Percent Rank" applied to that column.
please, do try.
I do have one question...what version of SQL Server are you running?
If you are running 2012 or later than we can take advantage of LAG/LEAD functions which will be much faster than the TOP 4/5/6 subqueries that you are running.