Calculate CHANGE and PERCENTAGE CHANGE in SQL Server

Hi

I am trying to Calculate CHANGE and PERCENTAGE CHANGE  Values within a query.

For explaining the details, I have prepared a demo database -

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


This is the query that I am trying to create -

SELECT
  xdate,
  sector,
  symbol = 'dlf',
  [Close Price ]
  -- [Close Price CHANGE ],needs to be added here,
  -- [Close Price PERCENTAGE CHANGE]needs to be added here,
  FROM dbo.Table1001;
GO
  

Open in new window


I want to add 2 more columns to this query, which needs to be calculated -
 
[Close Price CHANGE ]
[Close Price PERCENTAGE CHANGE]

The calculations will be based on the [Close Price ] column value of the date, and one date previous to it.

Please tell how to do this.

Thanks

I am using Microsoft SQL Server Management Studio version-  12.0.2000.8, Microsoft Office 2013 x64 and Windows 7 x64
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.

Mike EghtebasDatabase and Application DeveloperCommented:
Using this:
;with cte1 as
(SELECT xdate, sector, symbol, [Close Price ],
  row_number() Over(Order By xdate) as rn FROM Table1001),
cte2 as
(SELECT [Close Price ], rn+1 as rn2 FROM cte1)
Select 
  xdate, sector, symbol, [Close Price ],
  [Close Price ] - (Select  c.[Close Price ] From cte2 as c Where c.rn2 = rn ) as [Change ],
  (([Close Price ] - (Select  c.[Close Price ] From cte2 as c Where c.rn2 = rn ))/ [Close Price ]) as Perct
from cte1

Open in new window


I am getting this:
2014-01-01 00:00:00	Realty	DLF	171.45	NULL	NULL
2014-01-01 00:00:00	Realty	HDIL	55.6	-115.85	-2.08363309352518
2014-01-01 00:00:00	Realty	UNITECH	15.9	-39.7	-2.49685534591195
2014-01-02 00:00:00	Realty	DLF	166.45	150.55	0.904475818564133
2014-01-02 00:00:00	Realty	HDIL	51.85	-114.6	-2.21022179363549
2014-01-02 00:00:00	Realty	UNITECH	15.35	-36.5	-2.37785016286645
2014-01-03 00:00:00	Realty	DLF	169	153.65	0.909171597633136
2014-01-03 00:00:00	Realty	HDIL	53.5	-115.5	-2.1588785046729
2014-01-03 00:00:00	Realty	UNITECH	15.35	-38.15	-2.48534201954397
2014-01-06 00:00:00	Realty	DLF	167.25	151.9	0.908221225710015
2014-01-06 00:00:00	Realty	HDIL	52.95	-114.3	-2.15864022662889
2014-01-06 00:00:00	Realty	UNITECH	15.3	-37.65	-2.46078431372549
2014-01-07 00:00:00	Realty	DLF	164.6	149.3	0.907047387606318
2014-01-07 00:00:00	Realty	HDIL	52.15	-112.45	-2.15627996164909
2014-01-07 00:00:00	Realty	UNITECH	15	-37.15	-2.47666666666667
2014-01-08 00:00:00	Realty	DLF	162.9	147.9	0.907918968692449
2014-01-08 00:00:00	Realty	HDIL	53.3	-109.6	-2.0562851782364
2014-01-08 00:00:00	Realty	UNITECH	14.95	-38.35	-2.56521739130435
2014-01-09 00:00:00	Realty	DLF	159.5	144.55	0.906269592476489
2014-01-09 00:00:00	Realty	HDIL	50.2	-109.3	-2.17729083665339
2014-01-09 00:00:00	Realty	UNITECH	14.7	-35.5	-2.41496598639456
2014-01-10 00:00:00	Realty	DLF	156.85	142.15	0.906279885240676
2014-01-10 00:00:00	Realty	HDIL	49.55	-107.3	-2.16548940464178
2014-01-10 00:00:00	Realty	UNITECH	14.25	-35.3	-2.47719298245614
2014-01-13 00:00:00	Realty	DLF	161	146.75	0.911490683229814
2014-01-13 00:00:00	Realty	HDIL	49.75	-111.25	-2.23618090452261
2014-01-13 00:00:00	Realty	UNITECH	13.8	-35.95	-2.60507246376812
2014-01-14 00:00:00	Realty	DLF	157.8	144	0.91254752851711
2014-01-14 00:00:00	Realty	HDIL	49.6	-108.2	-2.18145161290323
2014-01-14 00:00:00	Realty	UNITECH	13.5	-36.1	-2.67407407407407
2014-01-15 00:00:00	Realty	DLF	160.5	147	0.91588785046729
2014-01-15 00:00:00	Realty	HDIL	51.3	-109.2	-2.12865497076023
2014-01-15 00:00:00	Realty	UNITECH	14.2	-37.1	-2.61267605633803
2014-01-16 00:00:00	Realty	DLF	162.4	148.2	0.91256157635468
2014-01-16 00:00:00	Realty	HDIL	50.65	-111.75	-2.20631786771964
2014-01-16 00:00:00	Realty	UNITECH	13.95	-36.7	-2.63082437275986
2014-01-17 00:00:00	Realty	DLF	155.6	141.65	0.9103470437018
2014-01-17 00:00:00	Realty	HDIL	48.6	-107	-2.20164609053498
2014-01-17 00:00:00	Realty	UNITECH	13.65	-34.95	-2.56043956043956
2014-01-20 00:00:00	Realty	DLF	156.1	142.45	0.912556053811659
2014-01-20 00:00:00	Realty	HDIL	49.55	-106.55	-2.15035317860747
2014-01-20 00:00:00	Realty	UNITECH	13.8	-35.75	-2.59057971014493
2014-01-21 00:00:00	Realty	DLF	157.8	144	0.91254752851711
2014-01-21 00:00:00	Realty	HDIL	50.9	-106.9	-2.10019646365422
2014-01-21 00:00:00	Realty	UNITECH	13.75	-37.15	-2.70181818181818
2014-01-22 00:00:00	Realty	DLF	159.1	145.35	0.913576367064739
2014-01-22 00:00:00	Realty	HDIL	50.8	-108.3	-2.13188976377953
2014-01-22 00:00:00	Realty	UNITECH	13.75	-37.05	-2.69454545454545
2014-01-23 00:00:00	Realty	DLF	159.05	145.3	0.913549198365294
2014-01-23 00:00:00	Realty	HDIL	50.45	-108.6	-2.15262636273538
2014-01-23 00:00:00	Realty	UNITECH	13.7	-36.75	-2.68248175182482
2014-01-24 00:00:00	Realty	DLF	152.8	139.1	0.910340314136126
2014-01-24 00:00:00	Realty	HDIL	48.1	-104.7	-2.17671517671518
2014-01-24 00:00:00	Realty	UNITECH	13.5	-34.6	-2.56296296296296

Open in new window

1
PortletPaulfreelancerCommented:
IF using SQL Server 2012 or later then the LEAD() function can be used to meet such needs, as it permits comparison of value from a previous row. It s used like this:
SELECT
    xdate
  , sector
  , symbol
  , [Close Price]
  , prevcloseprice = lag([Close Price]) over(partition by symbol order by xdate ASC)
  , [Close Price CHANGE] = [Close Price] - lag([Close Price]) over(partition by symbol order by xdate ASC)  
  , [Close Price PERCENTAGE CHANGE] = ([Close Price] - lag([Close Price]) over(partition by symbol order by xdate ASC)) * 100.0 
                                       / lag([Close Price]) over(partition by symbol order by xdate ASC)
FROM dbo.Table1001
ORDER BY 
    symbol
  , xdate
;

Open in new window

For % change I assume the day change is then divided by the previous day close.
Also it is assumed there is only one record per day.

Used against your data, I got this result:
|               xdate | sector |  symbol | Close Price | prevcloseprice |    Close Price CHANGE | Close Price PERCENTAGE CHANGE |
|---------------------|--------|---------|-------------|----------------|-----------------------|-------------------------------|
| 2014-01-01 00:00:00 | Realty |     DLF |      171.45 |         (null) |                (null) |                        (null) |
| 2014-01-02 00:00:00 | Realty |     DLF |      166.45 |         171.45 |                    -5 |            -2.916302128900554 |
| 2014-01-03 00:00:00 | Realty |     DLF |         169 |         166.45 |    2.5500000000000114 |            1.5319915890657925 |
| 2014-01-06 00:00:00 | Realty |     DLF |      167.25 |            169 |                 -1.75 |           -1.0355029585798816 |
| 2014-01-07 00:00:00 | Realty |     DLF |       164.6 |         167.25 |   -2.6500000000000057 |           -1.5844544095665205 |
| 2014-01-08 00:00:00 | Realty |     DLF |       162.9 |          164.6 |   -1.6999999999999886 |           -1.0328068043742338 |
| 2014-01-09 00:00:00 | Realty |     DLF |       159.5 |          162.9 |   -3.4000000000000057 |           -2.0871700429711515 |
| 2014-01-10 00:00:00 | Realty |     DLF |      156.85 |          159.5 |   -2.6500000000000057 |            -1.661442006269596 |
| 2014-01-13 00:00:00 | Realty |     DLF |         161 |         156.85 |     4.150000000000006 |            2.6458399744979317 |
| 2014-01-14 00:00:00 | Realty |     DLF |       157.8 |            161 |   -3.1999999999999886 |           -1.9875776397515457 |
| 2014-01-15 00:00:00 | Realty |     DLF |       160.5 |          157.8 |    2.6999999999999886 |            1.7110266159695744 |
| 2014-01-16 00:00:00 | Realty |     DLF |       162.4 |          160.5 |    1.9000000000000057 |             1.183800623052963 |
| 2014-01-17 00:00:00 | Realty |     DLF |       155.6 |          162.4 |    -6.800000000000011 |            -4.187192118226608 |
| 2014-01-20 00:00:00 | Realty |     DLF |       156.1 |          155.6 |                   0.5 |            0.3213367609254499 |
| 2014-01-21 00:00:00 | Realty |     DLF |       157.8 |          156.1 |     1.700000000000017 |            1.0890454836643286 |
| 2014-01-22 00:00:00 | Realty |     DLF |       159.1 |          157.8 |     1.299999999999983 |            0.8238276299112692 |
| 2014-01-23 00:00:00 | Realty |     DLF |      159.05 |          159.1 |  -0.04999999999998295 |          -0.03142677561281141 |
| 2014-01-24 00:00:00 | Realty |     DLF |       152.8 |         159.05 |                 -6.25 |            -3.929581892486639 |
| 2014-01-01 00:00:00 | Realty |    HDIL |        55.6 |         (null) |                (null) |                        (null) |
| 2014-01-02 00:00:00 | Realty |    HDIL |       51.85 |           55.6 |                 -3.75 |            -6.744604316546763 |
| 2014-01-03 00:00:00 | Realty |    HDIL |        53.5 |          51.85 |    1.6499999999999986 |            3.1822565091610384 |
| 2014-01-06 00:00:00 | Realty |    HDIL |       52.95 |           53.5 |   -0.5499999999999972 |           -1.0280373831775649 |
| 2014-01-07 00:00:00 | Realty |    HDIL |       52.15 |          52.95 |   -0.8000000000000043 |            -1.510859301227581 |
| 2014-01-08 00:00:00 | Realty |    HDIL |        53.3 |          52.15 |    1.1499999999999986 |             2.205177372962605 |
| 2014-01-09 00:00:00 | Realty |    HDIL |        50.2 |           53.3 |   -3.0999999999999943 |            -5.816135084427757 |
| 2014-01-10 00:00:00 | Realty |    HDIL |       49.55 |           50.2 |   -0.6500000000000057 |           -1.2948207171314854 |
| 2014-01-13 00:00:00 | Realty |    HDIL |       49.75 |          49.55 |   0.20000000000000284 |            0.4036326942482399 |
| 2014-01-14 00:00:00 | Realty |    HDIL |        49.6 |          49.75 |  -0.14999999999999858 |          -0.30150753768843935 |
| 2014-01-15 00:00:00 | Realty |    HDIL |        51.3 |           49.6 |    1.6999999999999957 |             3.427419354838701 |
| 2014-01-16 00:00:00 | Realty |    HDIL |       50.65 |           51.3 |   -0.6499999999999986 |           -1.2670565302144223 |
| 2014-01-17 00:00:00 | Realty |    HDIL |        48.6 |          50.65 |    -2.049999999999997 |            -4.047384007897329 |
| 2014-01-20 00:00:00 | Realty |    HDIL |       49.55 |           48.6 |    0.9499999999999957 |             1.954732510288057 |
| 2014-01-21 00:00:00 | Realty |    HDIL |        50.9 |          49.55 |    1.3500000000000014 |             2.724520686175583 |
| 2014-01-22 00:00:00 | Realty |    HDIL |        50.8 |           50.9 |  -0.10000000000000142 |          -0.19646365422397136 |
| 2014-01-23 00:00:00 | Realty |    HDIL |       50.45 |           50.8 |   -0.3499999999999943 |           -0.6889763779527448 |
| 2014-01-24 00:00:00 | Realty |    HDIL |        48.1 |          50.45 |   -2.3500000000000014 |           -4.6580773042616475 |
| 2014-01-01 00:00:00 | Realty | UNITECH |        15.9 |         (null) |                (null) |                        (null) |
| 2014-01-02 00:00:00 | Realty | UNITECH |       15.35 |           15.9 |   -0.5500000000000007 |           -3.4591194968553505 |
| 2014-01-03 00:00:00 | Realty | UNITECH |       15.35 |          15.35 |                     0 |                             0 |
| 2014-01-06 00:00:00 | Realty | UNITECH |        15.3 |          15.35 | -0.049999999999998934 |          -0.32573289902279434 |
| 2014-01-07 00:00:00 | Realty | UNITECH |          15 |           15.3 |   -0.3000000000000007 |           -1.9607843137254948 |
| 2014-01-08 00:00:00 | Realty | UNITECH |       14.95 |             15 |  -0.05000000000000071 |           -0.3333333333333381 |
| 2014-01-09 00:00:00 | Realty | UNITECH |        14.7 |          14.95 |                 -0.25 |           -1.6722408026755853 |
| 2014-01-10 00:00:00 | Realty | UNITECH |       14.25 |           14.7 |   -0.4499999999999993 |           -3.0612244897959138 |
| 2014-01-13 00:00:00 | Realty | UNITECH |        13.8 |          14.25 |   -0.4499999999999993 |           -3.1578947368421004 |
| 2014-01-14 00:00:00 | Realty | UNITECH |        13.5 |           13.8 |   -0.3000000000000007 |            -2.173913043478266 |
| 2014-01-15 00:00:00 | Realty | UNITECH |        14.2 |           13.5 |    0.6999999999999993 |              5.18518518518518 |
| 2014-01-16 00:00:00 | Realty | UNITECH |       13.95 |           14.2 |                 -0.25 |           -1.7605633802816902 |
| 2014-01-17 00:00:00 | Realty | UNITECH |       13.65 |          13.95 |  -0.29999999999999893 |           -2.1505376344085945 |
| 2014-01-20 00:00:00 | Realty | UNITECH |        13.8 |          13.65 |   0.15000000000000036 |            1.0989010989011014 |
| 2014-01-21 00:00:00 | Realty | UNITECH |       13.75 |           13.8 |  -0.05000000000000071 |          -0.36231884057971525 |
| 2014-01-22 00:00:00 | Realty | UNITECH |       13.75 |          13.75 |                     0 |                             0 |
| 2014-01-23 00:00:00 | Realty | UNITECH |        13.7 |          13.75 |  -0.05000000000000071 |           -0.3636363636363688 |
| 2014-01-24 00:00:00 | Realty | UNITECH |        13.5 |           13.7 |   -0.1999999999999993 |            -1.459854014598535 |

Open in new window

further details: http://sqlfiddle.com/#!6/4ab6e/1
2
happy 1001Author Commented:

@Paul Maxwell
IF using SQL Server 2012 or later then the LEAD() function can be used to meet such needs, as it permits comparison of value from a previous row. It s used like this:

Thanks for the solution Paul.
Can we use the same method of LEAD() in those cases as well, when we have to do the Percentage Calculation for multiple columns in the same query.

For example, in the current case, suppose we have to calculate the Percentage Change for Volume and OpenInterest as well, along with the Close Price Percentage Calculation that you have already done. Then is it possible to do all these 3 in the same query ? Or we will need to use some other method for this ?

SELECT
  xdate,
  sector,
  symbol = 'dlf',
  [Close Price ],
  Volume,
  OpenInterest
  -- [Close Price CHANGE ],needs to be added here,
  -- [Close Price PERCENTAGE CHANGE]needs to be added here,
  -- [Volume PERCENTAGE CHANGE]needs to be added here,
  -- [OpenInterest PERCENTAGE CHANGE]needs to be added here,
  FROM dbo.Table1001;
GO

Open in new window


Thanks for your prompt reply.
0
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.

PortletPaulfreelancerCommented:
>>"Can we use the same method of LEAD() i... , when we have to do the Percentage Calculation for multiple columns in the same query."

Yes, very probably.
But only if you have SQL Server 2012 or later. (earlier versions do not support this function)

In the example I have already provided LEAD() is used several times in a single query. It can also be used on different columns in the same query. There is another similar function LAG() which works in the opposite direction, and it is possible to have a singe query that has both LEAD() and LAG() together.
1
Mike EghtebasDatabase and Application DeveloperCommented:
Hi Paul,

Thank you for teaching something new with every posts. Below, I have used your code in a derived table and formatted the output. I though, using this structure happy 1001 could easily include the other calculation with it.
Select
  xdate
  , sector
  , symbol
  , [Close Price]
  , prevcloseprice
  , cast((prevcloseprice - [Close Price]) as Decimal(5,2)) as [Change]
  , cast(((( [Close Price] - prevcloseprice)/[Close Price])* 100) as Decimal(5,2))  As PercentChange
From ( Select
   xdate
  , sector
  , symbol
  , [Close Price]
  , prevcloseprice = lag([Close Price]) over(partition by symbol order by xdate ASC)
FROM dbo.Table1001) As D;

Open in new window


If previous number is 16 and the current number is 20. my %Change is (20-16)/16= 25% increase; not sure this is what happy 1001 wants.
xdate                 sector   symbol  CloseP  PrevP    Change   %Change
2014-01-01 00:00:00	Realty	DLF	171.45	NULL	NULL	NULL
2014-01-02 00:00:00	Realty	DLF	166.45	171.45	5.00	-3.00
2014-01-03 00:00:00	Realty	DLF	169	166.45	-2.55	1.51
2014-01-06 00:00:00	Realty	DLF	167.25	169	1.75	-1.05
2014-01-07 00:00:00	Realty	DLF	164.6	167.25	2.65	-1.61
2014-01-08 00:00:00	Realty	DLF	162.9	164.6	1.70	-1.04
2014-01-09 00:00:00	Realty	DLF	159.5	162.9	3.40	-2.13
2014-01-10 00:00:00	Realty	DLF	156.85	159.5	2.65	-1.69
2014-01-13 00:00:00	Realty	DLF	161	156.85	-4.15	2.58
2014-01-14 00:00:00	Realty	DLF	157.8	161	3.20	-2.03
2014-01-15 00:00:00	Realty	DLF	160.5	157.8	-2.70	1.68
2014-01-16 00:00:00	Realty	DLF	162.4	160.5	-1.90	1.17
2014-01-17 00:00:00	Realty	DLF	155.6	162.4	6.80	-4.37
2014-01-20 00:00:00	Realty	DLF	156.1	155.6	-0.50	0.32
2014-01-21 00:00:00	Realty	DLF	157.8	156.1	-1.70	1.08
2014-01-22 00:00:00	Realty	DLF	159.1	157.8	-1.30	0.82
2014-01-23 00:00:00	Realty	DLF	159.05	159.1	0.05	-0.03
2014-01-24 00:00:00	Realty	DLF	152.8	159.05	6.25	-4.09
2014-01-01 00:00:00	Realty	HDIL	55.6	NULL	NULL	NULL
2014-01-02 00:00:00	Realty	HDIL	51.85	55.6	3.75	-7.23
2014-01-03 00:00:00	Realty	HDIL	53.5	51.85	-1.65	3.08
2014-01-06 00:00:00	Realty	HDIL	52.95	53.5	0.55	-1.04
2014-01-07 00:00:00	Realty	HDIL	52.15	52.95	0.80	-1.53
2014-01-08 00:00:00	Realty	HDIL	53.3	52.15	-1.15	2.16
2014-01-09 00:00:00	Realty	HDIL	50.2	53.3	3.10	-6.18
2014-01-10 00:00:00	Realty	HDIL	49.55	50.2	0.65	-1.31
2014-01-13 00:00:00	Realty	HDIL	49.75	49.55	-0.20	0.40
2014-01-14 00:00:00	Realty	HDIL	49.6	49.75	0.15	-0.30
2014-01-15 00:00:00	Realty	HDIL	51.3	49.6	-1.70	3.31
2014-01-16 00:00:00	Realty	HDIL	50.65	51.3	0.65	-1.28
2014-01-17 00:00:00	Realty	HDIL	48.6	50.65	2.05	-4.22
2014-01-20 00:00:00	Realty	HDIL	49.55	48.6	-0.95	1.92
2014-01-21 00:00:00	Realty	HDIL	50.9	49.55	-1.35	2.65
2014-01-22 00:00:00	Realty	HDIL	50.8	50.9	0.10	-0.20
2014-01-23 00:00:00	Realty	HDIL	50.45	50.8	0.35	-0.69
2014-01-24 00:00:00	Realty	HDIL	48.1	50.45	2.35	-4.89
2014-01-01 00:00:00	Realty	UNITECH	15.9	NULL	NULL	NULL
2014-01-02 00:00:00	Realty	UNITECH	15.35	15.9	0.55	-3.58
2014-01-03 00:00:00	Realty	UNITECH	15.35	15.35	0.00	0.00
2014-01-06 00:00:00	Realty	UNITECH	15.3	15.35	0.05	-0.33
2014-01-07 00:00:00	Realty	UNITECH	15	15.3	0.30	-2.00
2014-01-08 00:00:00	Realty	UNITECH	14.95	15	0.05	-0.33
2014-01-09 00:00:00	Realty	UNITECH	14.7	14.95	0.25	-1.70
2014-01-10 00:00:00	Realty	UNITECH	14.25	14.7	0.45	-3.16
2014-01-13 00:00:00	Realty	UNITECH	13.8	14.25	0.45	-3.26
2014-01-14 00:00:00	Realty	UNITECH	13.5	13.8	0.30	-2.22
2014-01-15 00:00:00	Realty	UNITECH	14.2	13.5	-0.70	4.93
2014-01-16 00:00:00	Realty	UNITECH	13.95	14.2	0.25	-1.79
2014-01-17 00:00:00	Realty	UNITECH	13.65	13.95	0.30	-2.20
2014-01-20 00:00:00	Realty	UNITECH	13.8	13.65	-0.15	1.09
2014-01-21 00:00:00	Realty	UNITECH	13.75	13.8	0.05	-0.36
2014-01-22 00:00:00	Realty	UNITECH	13.75	13.75	0.00	0.00
2014-01-23 00:00:00	Realty	UNITECH	13.7	13.75	0.05	-0.36
2014-01-24 00:00:00	Realty	UNITECH	13.5	13.7	0.20	-1.48

Open in new window

1
PortletPaulfreelancerCommented:
nicely done eghtebas, using the derived table (also known as "inline view")  we can reuse the column alias (prevcloseprice) in calculations in the outer query level.

This can also be combined with a related question and there can be several columns each with their own case expression.

SELECT
      xdate
    , sector
    , symbol
    , [Close Price]

      /* here we can reuse a column alias created inside the derived table */
    , prevcloseprice
    , CAST((prevcloseprice - [Close Price]) AS decimal(5, 2)) AS [Change]
    , CAST(((([Close Price] - prevcloseprice) / [Close Price]) * 100) AS decimal(5, 2)) AS PercentChange
FROM (
      /* this subquery forms a "derived table" */
      SELECT
            xdate
          , sector
          , symbol
          , [Close Price]

          /* here the column alias prevcloseprice is created */
          , prevcloseprice = LAG([Close Price]) OVER (PARTITION BY symbol ORDER BY xdate ASC)
      FROM dbo.Table1001
     ) AS D
;

Open in new window

0

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