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-4Days,
PercentRank-OpenInterest-5Days,
PercentRank-OpenInterest-6Days

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
			)

Open in new window


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)
GO

Open in new window


Regards
happy 1001Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
First, I would like to thank you for the quality of your question.  I wish everyone that posted a question in here would take the time that you did to make it easy for anyone that might want to assist.

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.
1
PortletPaulfreelancerCommented:
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
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
  

Open in new window

1
PortletPaulfreelancerCommented:
and I fully concur with Brian, thank you very much for the great question preparation!
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

happy 1001Author Commented:
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.
0
PortletPaulfreelancerCommented:
ok, in your mind it is simple and in mind there remain many unanswered questions.

I will leave you with this result:
|  symbol |      xdate |                      |         Volume4day |                      | sector | Close Price | Volume | Volume Percent |       OpenInterest | OpenInterest Percent |
|---------|------------|----------------------|--------------------|----------------------|--------|-------------|--------|----------------|--------------------|----------------------|
|     DLF | 2014-01-01 |                    0 |              446.1 |                    0 | Realty |      171.45 | 236.57 |           16.5 |          657.59007 |                 6.88 |
|     DLF | 2014-01-02 | 0.058823529411764705 |            1110.75 |  0.29411764705882354 | Realty |      166.45 | 420.65 |          77.81 |  658.6862199999999 |                 0.17 |
|     DLF | 2014-01-03 |  0.11764705882352941 | 1625.4199999999998 |   0.8235294117647058 | Realty |         169 | 369.57 |         -12.14 |  707.7443699999999 |                 7.45 |
|     DLF | 2014-01-06 |  0.17647058823529413 |            1485.84 |   0.7647058823529411 | Realty |      167.25 | 217.96 |         -41.02 |  722.1504399999999 |                 2.04 |
|     DLF | 2014-01-07 |  0.23529411764705882 |            1265.96 |   0.5294117647058824 | Realty |       164.6 | 265.73 |          21.92 |          710.63243 |                -1.59 |
|     DLF | 2014-01-08 |  0.29411764705882354 |            1135.99 |   0.4117647058823529 | Realty |       162.9 | 258.36 |          -2.77 |  697.2023099999998 |                -1.89 |
|     DLF | 2014-01-09 |  0.35294117647058826 |            1476.22 |   0.7058823529411765 | Realty |       159.5 | 186.23 |         -27.92 |           699.0476 |                 0.27 |
|     DLF | 2014-01-10 |   0.4117647058823529 |            1824.87 |   0.9411764705882353 | Realty |      156.85 | 200.27 |           7.54 |  699.8979999999998 |                 0.12 |
|     DLF | 2014-01-13 |  0.47058823529411764 | 1020.2199999999999 |  0.17647058823529413 | Realty |         161 | 197.51 |          -1.38 |  703.7260399999998 |                 0.55 |
|     DLF | 2014-01-14 |   0.5294117647058824 | 1000.3499999999999 |  0.11764705882352941 | Realty |       157.8 | 188.98 |          -4.32 |  728.4462599999998 |                 3.51 |
|     DLF | 2014-01-15 |   0.5882352941176471 |            1047.95 |  0.23529411764705882 | Realty |       160.5 | 197.29 |            4.4 |  728.8259100000004 |                 0.05 |
|     DLF | 2014-01-16 |   0.6470588235294118 | 1395.7799999999997 |   0.6470588235294118 | Realty |       162.4 | 208.88 |           5.87 |          735.66176 |                 0.94 |
|     DLF | 2014-01-17 |   0.7058823529411765 | 1822.9799999999998 |   0.8823529411764706 | Realty |       155.6 | 284.44 |          36.17 |  756.6858900000002 |                 2.86 |
|     DLF | 2014-01-20 |   0.7647058823529411 | 1162.6100000000001 |  0.47058823529411764 | Realty |       156.1 |  238.3 |         -16.22 |  784.6797100000001 |                  3.7 |
|     DLF | 2014-01-21 |   0.8235294117647058 |            1132.63 |  0.35294117647058826 | Realty |       157.8 | 201.98 |         -15.24 |  788.5747300000002 |                  0.5 |
|     DLF | 2014-01-22 |   0.8823529411764706 |  991.8699999999999 | 0.058823529411764705 | Realty |       159.1 | 194.93 |          -3.49 |  794.0979600000003 |                  0.7 |
|     DLF | 2014-01-23 |   0.9411764705882353 |             1285.3 |   0.5882352941176471 | Realty |      159.05 | 199.54 |           2.36 |  801.6675000000004 |                 0.95 |
|     DLF | 2014-01-24 |                    1 |            1876.76 |                    1 | Realty |       152.8 | 396.39 |          98.65 |  882.0059100000003 |                10.02 |
|    HDIL | 2014-01-01 |                    0 |              446.1 |                    0 | Realty |        55.6 |   89.4 |          61.43 |           152.6776 |                17.62 |
|    HDIL | 2014-01-02 | 0.058823529411764705 |            1110.75 |  0.29411764705882354 | Realty |       51.85 | 124.27 |             39 | 147.08239999999998 |                -3.67 |
|    HDIL | 2014-01-03 |  0.11764705882352941 | 1625.4199999999998 |   0.8235294117647058 | Realty |        53.5 |  87.35 |         -29.71 | 158.88595999999998 |                 8.03 |
|    HDIL | 2014-01-06 |  0.17647058823529413 |            1485.84 |   0.7647058823529411 | Realty |       52.95 |  50.37 |         -42.34 | 157.18616000000006 |                -1.07 |
|    HDIL | 2014-01-07 |  0.23529411764705882 |            1265.96 |   0.5294117647058824 | Realty |       52.15 | 108.35 |         115.11 | 167.99551999999997 |                 6.87 |
|    HDIL | 2014-01-08 |  0.29411764705882354 |            1135.99 |   0.4117647058823529 | Realty |        53.3 |  70.91 |         -34.55 |          174.64184 |                 3.95 |
|    HDIL | 2014-01-09 |  0.35294117647058826 |            1476.22 |   0.7058823529411765 | Realty |        50.2 | 103.81 |           46.4 |          182.34456 |                 4.41 |
|    HDIL | 2014-01-10 |   0.4117647058823529 |            1824.87 |   0.9411764705882353 | Realty |       49.55 |  91.61 |         -11.75 |          180.54768 |                -0.98 |
|    HDIL | 2014-01-13 |  0.47058823529411764 | 1020.2199999999999 |  0.17647058823529413 | Realty |       49.75 |  72.79 |         -20.54 | 183.90999999999997 |                 1.86 |
|    HDIL | 2014-01-14 |   0.5294117647058824 | 1000.3499999999999 |  0.11764705882352941 | Realty |        49.6 |   79.6 |           9.36 | 203.92476000000008 |                10.88 |
|    HDIL | 2014-01-15 |   0.5882352941176471 |            1047.95 |  0.23529411764705882 | Realty |        51.3 |  73.09 |          -8.18 |          208.26824 |                 2.13 |
|    HDIL | 2014-01-16 |   0.6470588235294118 | 1395.7799999999997 |   0.6470588235294118 | Realty |       50.65 |  68.36 |          -6.47 |          208.24432 |                -0.01 |
|    HDIL | 2014-01-17 |   0.7058823529411765 | 1822.9799999999998 |   0.8823529411764706 | Realty |        48.6 |  86.94 |          27.18 | 210.18943999999993 |                 0.94 |
|    HDIL | 2014-01-20 |   0.7647058823529411 | 1162.6100000000001 |  0.47058823529411764 | Realty |       49.55 |   73.1 |         -15.92 |            213.888 |                 1.76 |
|    HDIL | 2014-01-21 |   0.8235294117647058 |            1132.63 |  0.35294117647058826 | Realty |        50.9 |  71.67 |          -1.96 | 218.67824000000007 |                 2.24 |
|    HDIL | 2014-01-22 |   0.8823529411764706 |  991.8699999999999 | 0.058823529411764705 | Realty |        50.8 |  68.03 |          -5.08 | 217.37912000000003 |                -0.59 |
|    HDIL | 2014-01-23 |   0.9411764705882353 |             1285.3 |   0.5882352941176471 | Realty |       50.45 |  61.91 |             -9 |          218.71092 |                 0.61 |
|    HDIL | 2014-01-24 |                    1 |            1876.76 |                    1 | Realty |        48.1 | 105.47 |          70.36 | 226.20192000000003 |                 3.43 |
| UNITECH | 2014-01-01 |                    0 |              446.1 |                    0 | Realty |        15.9 | 120.13 |             45 |          282.43848 |                  6.4 |
| UNITECH | 2014-01-02 | 0.058823529411764705 |            1110.75 |  0.29411764705882354 | Realty |       15.35 | 119.73 |          -0.33 | 273.67355999999995 |                 -3.1 |
| UNITECH | 2014-01-03 |  0.11764705882352941 | 1625.4199999999998 |   0.8235294117647058 | Realty |       15.35 |  57.75 |         -51.77 | 279.11279999999994 |                 1.99 |
| UNITECH | 2014-01-06 |  0.17647058823529413 |            1485.84 |   0.7647058823529411 | Realty |        15.3 |  38.19 |         -33.87 |           286.6113 |                 2.69 |
| UNITECH | 2014-01-07 |  0.23529411764705882 |            1265.96 |   0.5294117647058824 | Realty |          15 |  70.69 |           85.1 |  290.4961199999999 |                 1.36 |
| UNITECH | 2014-01-08 |  0.29411764705882354 |            1135.99 |   0.4117647058823529 | Realty |       14.95 |  55.43 |         -21.59 | 291.71525999999994 |                 0.42 |
| UNITECH | 2014-01-09 |  0.35294117647058826 |            1476.22 |   0.7058823529411765 | Realty |        14.7 |  50.19 |          -9.45 |  294.8962799999999 |                 1.09 |
| UNITECH | 2014-01-10 |   0.4117647058823529 |            1824.87 |   0.9411764705882353 | Realty |       14.25 |  56.77 |          13.11 |  296.6907599999999 |                 0.61 |
| UNITECH | 2014-01-13 |  0.47058823529411764 | 1020.2199999999999 |  0.17647058823529413 | Realty |        13.8 |  61.04 |           7.52 | 300.79445999999996 |                 1.38 |
| UNITECH | 2014-01-14 |   0.5294117647058824 | 1000.3499999999999 |  0.11764705882352941 | Realty |        13.5 |  51.78 |         -15.17 |  305.9927999999998 |                 1.73 |
| UNITECH | 2014-01-15 |   0.5882352941176471 |            1047.95 |  0.23529411764705882 | Realty |        14.2 | 125.87 |         143.09 |           317.5926 |                 3.79 |
| UNITECH | 2014-01-16 |   0.6470588235294118 | 1395.7799999999997 |   0.6470588235294118 | Realty |       13.95 |  70.59 |         -43.92 | 314.64828000000006 |                -0.93 |
| UNITECH | 2014-01-17 |   0.7058823529411765 | 1822.9799999999998 |   0.8823529411764706 | Realty |       13.65 |  55.82 |         -20.92 |          313.36584 |                -0.41 |
| UNITECH | 2014-01-20 |   0.7647058823529411 | 1162.6100000000001 |  0.47058823529411764 | Realty |        13.8 |  76.18 |          36.47 | 318.67127999999997 |                 1.69 |
| UNITECH | 2014-01-21 |   0.8235294117647058 |            1132.63 |  0.35294117647058826 | Realty |       13.75 |   44.2 |         -41.98 |          318.27546 |                -0.12 |
| UNITECH | 2014-01-22 |   0.8823529411764706 |  991.8699999999999 | 0.058823529411764705 | Realty |       13.75 |  23.48 |         -46.88 |  323.4762599999999 |                 1.63 |
| UNITECH | 2014-01-23 |   0.9411764705882353 |             1285.3 |   0.5882352941176471 | Realty |        13.7 |  31.98 |           36.2 | 322.67046000000005 |                -0.25 |
| UNITECH | 2014-01-24 |                    1 |            1876.76 |                    1 | Realty |        13.5 |   89.6 |         180.18 | 337.96794000000006 |                 4.74 |

Open in new window

That was produced by this query:
select
        symbol
      , format(xdate, 'yyyy-MM-dd') as xdate
      , percent_rank() over(partition by symbol order by xdate)
      , ca4.Volume4day
      , percent_rank() over(partition by symbol order by ca4.Volume4day)
      , sector,  [Close Price], Volume, [Volume Percent], OpenInterest, [OpenInterest Percent]
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
order by
        symbol, xdate
;

Open in new window

You can even go here: http://sqlfiddle.com/#!6/fb682/11 and "play" with the numbers if you like.

My point is, I have displayed a way to produce the numbers needed for the extra columns. I do not see how you use them because you have not provided an "expected result" (i.e. we cannot judge for ourselves if we are meeting the expectation or not.)

Good luck. I do hope others will take up the challenge.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
happy 1001Author Commented:
@ 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-4Days
PercentRank-OpenInterest-5Days
PercentRank-OpenInterest-6Days

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.pngPercentile-Rank-Query-Format.xlsx
0
PortletPaulfreelancerCommented:
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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.