November 13, 2011 at 2:25 am
Sorry sir,i forgot to paste the last line
i.e. Group by
----Below is corrected code-----------------
select Symbol_Code,
Transaction_date, sum(TR) as TR14,
sum(Positive_DM) AS PDM14,
sum(Negative_DM) as NDM14,
case when (sum(TR)>0) then(100*(sum(Positive_DM)/(sum(TR))))
when (sum(TR)=0) then 0 end
as PDI14,
case when (sum(TR)>0) then(100*(sum(Negative_DM)/(sum(TR))))
when (sum(TR)=0) then 0 end
as NDI14
,
case when (sum(TR)>0) then abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))
when (sum(TR)=0) then 0 end
as DI14diff,
case when (sum(TR)>0) then((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR)))))
when (sum(TR)=0) then 0 end
as DI14Sum
--,case when (sum(TR)>0) then 100*(abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))/((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR))))))
--when (sum(TR)=0) then 0 end
--as DX
from #ADX_Data
--where d1.seq+13=d2.seq
group by Symbol_Code,
Transaction_date
November 13, 2011 at 2:39 am
sushilb (11/13/2011)
I have commented the line which is giving the error
what error messge are you getting please?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 13, 2011 at 3:18 am
Sir,
I am not able to calculate the
TR14,+DM14,-DM14,+DI14,-DI14,DI14Diff,DI14Sum,DX,ADX
From #ADX_Data
The Calculation is done as per attached excel sheet.
November 13, 2011 at 3:31 am
I am sorry but if i break your code out...I still get divide by zero errors...
probably best if you can repost your set up scripts, with all the recent amendments and then hopefully we will both be on the same page 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
November 13, 2011 at 7:56 am
Dear sushilb
based on the spreadsheet you provided, I have taken the liberty to use this as the source for some sample data.
Hopefully.........we can then start to see where your code does not produce the results you are expecting (as per your spreadsheet)
pls review / run the following code and post back to confrim if my assumptions are correct.
you will notice that I have added @dt to replace Getdate() to correspond to your spreadsheet data.
from what I have got so far from running your code...the results do not match your spreadsheet.
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ADX_RESULTS]') AND type in (N'U'))
DROP TABLE [dbo].[ADX_RESULTS]
GO
--== FOLLOWING TABLE IS BASED ON THE SPREADSHEET PROVIDED
CREATE TABLE [dbo].[ADX_RESULTS](
[transaction_id] [int] NULL,
[transaction_date] [datetime] NULL,
[symbol_code] [varchar](50) NULL,
[symbol_name] [varchar](50) NULL,
[open_price] [money] NULL,
[High_price] [money] NULL,
[Low_price] [money] NULL,
[Close_price] [money] NULL,
[decimal](18, 2) NULL,
[(+DM 1)] [decimal](18, 2) NULL,
[(-DM 1)] [decimal](18, 2) NULL,
[(TR14)] [decimal](18, 2) NULL,
[(+DM14)] [decimal](18, 2) NULL,
[(-DM14)] [decimal](18, 2) NULL,
[(+DI14)] [decimal](18, 2) NULL,
[(-DI14)] [decimal](18, 2) NULL,
[(DI 14 Diff)] [decimal](18, 2) NULL,
[(DI 14 Sum)] [decimal](18, 2) NULL,
[DX] [decimal](18, 2) NULL,
[ADX] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[ADX_RESULTS]([transaction_id], [transaction_date], [symbol_code], [symbol_name], [open_price], [High_price], [Low_price], [Close_price],
, [(+DM 1)], [(-DM 1)], [(TR14)], [(+DM14)], [(-DM14)], [(+DI14)], [(-DI14)], [(DI 14 Diff)], [(DI 14 Sum)], [DX], [ADX])
SELECT 478, '20110103 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 55.6900, 54.9500, 55.3100, 1.23, 1.07, 0.00, 7.73, 2.25, 1.63, 29.06, 21.02, 8.04, 50.07, 16.06, 14.88 UNION ALL
SELECT 479, '20110104 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 55.5500, 54.9200, 55.2650, 0.63, 0.00, 0.03, 7.81, 2.09, 1.54, 26.71, 19.71, 7.01, 46.42, 15.10, 14.90 UNION ALL
SELECT 480, '20110105 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 55.7600, 55.0700, 55.7400, 0.69, 0.21, 0.00, 7.94, 2.15, 1.43, 27.04, 17.99, 9.04, 45.03, 20.08, 15.27 UNION ALL
SELECT 481, '20110106 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 55.9600, 55.6800, 55.9200, 0.28, 0.20, 0.00, 7.66, 2.19, 1.33, 28.66, 17.34, 11.32, 46.00, 24.62, 15.93 UNION ALL
SELECT 482, '20110107 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.0500, 55.3200, 55.8700, 0.73, 0.00, 0.36, 7.84, 2.04, 1.59, 25.99, 20.31, 5.68, 46.30, 12.26, 15.67 UNION ALL
SELECT 483, '20110110 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.1800, 55.5800, 56.0800, 0.60, 0.13, 0.00, 7.88, 2.02, 1.48, 25.66, 18.77, 6.89, 44.43, 15.52, 15.66 UNION ALL
SELECT 484, '20110111 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.3600, 55.9500, 56.1600, 0.41, 0.18, 0.00, 7.73, 2.06, 1.37, 26.63, 17.77, 8.86, 44.40, 19.95, 15.97 UNION ALL
SELECT 485, '20110112 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.5600, 56.2000, 56.5550, 0.40, 0.20, 0.00, 7.57, 2.11, 1.27, 27.86, 16.83, 11.03, 44.70, 24.68, 16.59 UNION ALL
SELECT 486, '20110113 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.7300, 56.4100, 56.5750, 0.32, 0.17, 0.00, 7.35, 2.13, 1.18, 28.96, 16.10, 12.86, 45.06, 28.54, 17.44 UNION ALL
SELECT 487, '20110114 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.0200, 56.4600, 57.0000, 0.56, 0.29, 0.00, 7.39, 2.27, 1.10, 30.69, 14.88, 15.81, 45.57, 34.70, 18.68 UNION ALL
SELECT 488, '20110118 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.2300, 56.4900, 57.1600, 0.74, 0.21, 0.00, 7.60, 2.32, 1.02, 30.47, 13.43, 17.04, 43.90, 38.81, 20.11 UNION ALL
SELECT 489, '20110119 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.2600, 56.3200, 56.5100, 0.94, 0.00, 0.17, 8.00, 2.15, 1.12, 26.89, 13.98, 12.91, 40.86, 31.59, 20.93 UNION ALL
SELECT 490, '20110120 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.3500, 55.6800, 56.1100, 0.83, 0.00, 0.64, 8.26, 2.00, 1.68, 24.18, 20.32, 3.86, 44.51, 8.67, 20.06 UNION ALL
SELECT 491, '20110121 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.4900, 55.6500, 55.6800, 0.84, 0.14, 0.00, 8.51, 1.99, 1.56, 23.44, 18.32, 5.12, 41.76, 12.27, 19.50 UNION ALL
SELECT 492, '20110124 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.4600, 55.6800, 56.4500, 0.78, 0.00, 0.00, 8.68, 1.85, 1.45, 21.33, 16.67, 4.66, 38.01, 12.27, 18.99 UNION ALL
SELECT 493, '20110125 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.5500, 56.0500, 56.5325, 0.50, 0.09, 0.00, 8.56, 1.81, 1.34, 21.14, 15.70, 5.44, 36.84, 14.77, 18.68 UNION ALL
SELECT 494, '20110126 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.9800, 56.4500, 56.8300, 0.53, 0.43, 0.00, 8.48, 2.11, 1.25, 24.89, 14.72, 10.17, 39.61, 25.69, 19.18 UNION ALL
SELECT 495, '20110127 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.3500, 56.9200, 57.1800, 0.52, 0.37, 0.00, 8.39, 2.33, 1.16, 27.76, 13.80, 13.95, 41.56, 33.57, 20.21 UNION ALL
SELECT 496, '20110128 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.2200, 55.4700, 55.7300, 1.75, 0.00, 1.45, 9.54, 2.16, 2.53, 22.67, 26.47, 3.80, 49.13, 7.74, 19.32 UNION ALL
SELECT 497, '20110131 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 56.1600, 55.3900, 56.0000, 0.77, 0.00, 0.08, 9.63, 2.01, 2.43, 20.85, 25.18, 4.33, 46.04, 9.40, 18.61 UNION ALL
SELECT 498, '20110201 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.1800, 56.3600, 57.0500, 1.18, 1.02, 0.00, 10.12, 2.89, 2.25, 28.50, 22.25, 6.25, 50.75, 12.32, 18.16 UNION ALL
SELECT 499, '20110202 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.1700, 56.8400, 56.9550, 0.33, 0.00, 0.00, 9.73, 2.68, 2.09, 27.53, 21.49, 6.04, 49.03, 12.32, 17.75 UNION ALL
SELECT 500, '20110203 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.1400, 56.4000, 57.0575, 0.74, 0.00, 0.44, 9.77, 2.49, 2.38, 25.45, 24.37, 1.08, 49.82, 2.17, 16.63 UNION ALL
SELECT 501, '20110204 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.4200, 56.9000, 57.3750, 0.52, 0.28, 0.00, 9.60, 2.59, 2.21, 26.99, 23.05, 3.94, 50.03, 7.88, 16.01 UNION ALL
SELECT 502, '20110207 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 57.9700, 57.4000, 57.6500, 0.59, 0.55, 0.00, 9.51, 2.95, 2.05, 31.08, 21.60, 9.48, 52.69, 17.99, 16.15 UNION ALL
SELECT 503, '20110208 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 58.0700, 57.5600, 58.0250, 0.51, 0.10, 0.00, 9.34, 2.84, 1.91, 30.46, 20.42, 10.03, 50.88, 19.72, 16.40 UNION ALL
SELECT 504, '20110209 00:00:00.000', N'Wipro', N'Wipro', 0.0000, 58.1200, 57.7500, 57.9300, 0.37, 0.05, 0.00, 9.04, 2.69, 1.77, 29.76, 19.59, 10.18, 49.35, 20.62, 16.71
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NSE_Stock_Data]') AND type in (N'U'))
DROP TABLE [dbo].[NSE_Stock_Data]
GO
--== FOLLOWING CREATES TABLE FOR ANALYSIS BASED ON DATA PROVIDED IN SPREADSHEET
SELECT
transaction_id,
transaction_date,
symbol_code,
symbol_name,
open_price,
High_price,
Low_price,
Close_price
INTO NSE_Stock_Data
FROM ADX_RESULTS
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Greatest]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Greatest]
GO
CREATE function [dbo].[Greatest](@val1 decimal(18,2), @val2 decimal(18,2),@val3 decimal(18,2))
returns decimal(18,2)
as
begin
if (@val1 >= @val2 and @val1>=@val3)
return @val1
if (@val2 >= @val1 and @val2>=@val3)
return @val2
if (@val3 >= @val1 and @val3>=@val2)
return @val3
return null
end
GO
--first section
DECLARE
@Cnt INT,
@crt INT,
@newcnt INT,
@dt datetime --- JLS @dt replaces Getdsate() in original code to accommodate sample data
SET @dt = '2011-02-09'
IF Object_id(N'tempdb..#Adxdata', N'U') IS NOT NULL
DROP TABLE #Adxdata;
CREATE TABLE #Adxdata
(
ID INT NOT NULL,
Symbol_Code VARCHAR(50),
Transaction_date DATETIME,
High_Price DECIMAL(18, 2),
Low_Price DECIMAL(18, 2),
Close_Price DECIMAL(18, 2)
)
SET @Cnt=(SELECT
COUNT(*)
FROM NSE_Stock_Data
WHERE Transaction_date BETWEEN Dateadd(DAY, -28, @dt) AND @dt
AND Symbol_Code = 'Wipro')
IF Object_id(N'tempdb..#ADX_Data', N'U') IS NOT NULL
DROP TABLE #ADX_Data;
IF Object_id(N'tempdb..#ADX_Data1', N'U') IS NOT NULL
DROP TABLE #ADX_Data1;
IF --if rows are less than 28 i.e 28 days data
( @Cnt < 28 )
SET @crt=28 - @Cnt
SET @newcnt=@crt + 28 ---to get exact 28 rows i.e 28 days data
--print @newcnt
--print @crt
--print @Cnt
---Insert data into temporary table
INSERT INTO #Adxdata (ID,Symbol_Code,Transaction_date,High_Price,Low_Price,Close_Price)
SELECT
Transaction_Id,
Symbol_Code,
Transaction_date,
High_Price,
Low_Price,
Close_Price
FROM NSE_Stock_Data
WHERE Transaction_date BETWEEN Dateadd(DAY, -@newcnt, @dt) AND @dt
AND Symbol_Code = 'Wipro';
WITH OrderedData
AS (SELECT
seq = Row_number() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,
Symbol_Code,
Transaction_date,
High_Price,
Low_Price,
Close_Price
FROM #Adxdata
WHERE Transaction_date BETWEEN Dateadd(DAY, -@newcnt, @dt) AND @dt)
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.High_Price,
d1.Low_Price,
d1.Close_Price,
d1.seq AS d1,
d1.seq AS d2,
--Greatest function returns the highest value out of three values
dbo.Greatest(( d2.High_Price - d2.Low_Price ), Abs(d2.High_Price - d1.Close_Price), Abs(d2.Low_Price - d1.Close_Price))AS TR,
--Postive DM
CASE
WHEN ( ( d2.High_Price - d1.High_Price ) > ( d1.Low_Price - d1.Low_Price ) )
THEN dbo.Greatest(( d2.High_Price - d1.High_Price ), 0, 0)
WHEN ( ( d2.High_Price - d1.High_Price ) < ( d1.Low_Price - d1.Low_Price ) )
THEN 0
END AS Positive_DM,
--Negative DM
CASE
WHEN ( ( d1.Low_Price - d1.Low_Price ) > ( d2.High_Price - d1.High_Price ) )
THEN dbo.Greatest(( d1.Low_Price - d1.Low_Price ), 0, 0)
WHEN ( ( d1.Low_Price - d1.Low_Price ) < ( d2.High_Price - d1.High_Price ) )
THEN 0
END AS Negative_DM
INTO #ADX_Data
FROM OrderedData d1
LEFT JOIN OrderedData d2 ON d2.Symbol_Code = d1.Symbol_Code
AND d1.seq + 1 = d2.seq
AND d1.Transaction_date BETWEEN Dateadd(DAY, -@newcnt, @dt) AND @dt
--select * from #ADX_Data
---next section
SELECT
Symbol_Code,
Transaction_date,
SUM(TR) AS TR14,
SUM(Positive_DM) AS PDM14,
SUM(Negative_DM) AS NDM14,
CASE
WHEN ( SUM(TR) > 0 )
THEN( 100 * ( SUM(Positive_DM) / ( SUM(TR) ) ) )
WHEN ( SUM(TR) = 0 )
THEN 0
END AS PDI14,
CASE
WHEN ( SUM(TR) > 0 )
THEN( 100 * ( SUM(Negative_DM) / ( SUM(TR) ) ) )
WHEN ( SUM(TR) = 0 )
THEN 0
END AS NDI14,
CASE
WHEN ( SUM(TR) > 0 )
THEN Abs(( 100 * ( SUM(Positive_DM) / ( SUM(TR) ) ) ) - ( 100 * ( SUM(Negative_DM) / ( SUM(TR) ) ) ))
WHEN ( SUM(TR) = 0 )
THEN 0
END AS DI14diff,
CASE
WHEN ( SUM(TR) > 0 )
THEN( ( 100 * ( SUM(Positive_DM) / ( SUM(TR) ) ) ) + ( 100 * ( SUM(Negative_DM) / ( SUM(TR) ) ) ) )
WHEN ( SUM(TR) = 0 )
THEN 0
END AS DI14Sum
-- THE FOLLOWING CASE STAREMENT THROWS A DIVIDE BY ZERO ERROR
--CASE
-- WHEN ( SUM(TR) > 0 )
-- THEN 100 * ( Abs(( 100 * ( SUM(Positive_DM) / ( SUM(TR) ) ) ) - ( 100 * ( SUM(Negative_DM) / ( SUM(TR) ) ) )) / ( ( 100 * ( SUM(Positive_DM) / ( SUM(TR) ) ) ) + ( 100 * ( SUM(Negative_DM) / ( SUM(TR) ) ) ) ) )
-- WHEN ( SUM(TR) = 0 )
-- THEN 0
-- END AS DX
FROM #ADX_Data
--where d1.seq+13=d2.seq
GROUP BY
Symbol_Code,
Transaction_date
select * from ADX_RESULTS
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply