October 27, 2010 at 2:13 am
Hi again,
I have the following schema and sample data.
-- schema
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nannySets]') AND type in (N'U'))
DROP TABLE [dbo].[nannySets]
GO
CREATE TABLE [dbo].[nannySets](
[nannySetId] [smallint] NOT NULL,
[name] [varchar](50) NOT NULL,
[initialRisk] [decimal](5, 2) NOT NULL,
[maxDrawDown] [decimal](5, 2) NOT NULL,
[maxLosingTrades] [tinyint] NOT NULL,
CONSTRAINT [PK_nannySets] PRIMARY KEY CLUSTERED
(
[nannySetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nannyValues]') AND type in (N'U'))
DROP TABLE [dbo].[nannyValues]
GO
CREATE TABLE [dbo].[nannyValues](
[numLosingTrades] [int] NOT NULL,
[nannySetId] [smallint] NOT NULL,
[risk] [decimal](7, 4) NOT NULL,
CONSTRAINT [PK_nannyValues] PRIMARY KEY CLUSTERED
(
[numLosingTrades] ASC,
[nannySetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[accountPerformance]') AND type in (N'U'))
DROP TABLE [dbo].[accountPerformance]
GO
CREATE TABLE [dbo].[accountPerformance](
[tradeId] [int] NOT NULL,
[pctAccountRisked] [decimal](4, 2) NOT NULL,
[profit] [decimal](38, 6) NULL,
[tradeDateTime] [datetime] NULL,
CONSTRAINT [PK_accountPerformance] PRIMARY KEY NONCLUSTERED
(
[tradeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-- sample data
INSERT INTO [nannySets] ([nannySetId],[name],[initialRisk],[maxDrawDown],[maxLosingTrades])VALUES(1,'IR:2%, MD: 10%, ML: 10',2.00,10.00,10)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(0,1,2.0000)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(1,1,1.5390)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(2,1,1.2150)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(3,1,1.0429)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(4,1,0.9311)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(5,1,0.8506)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(6,1,0.7891)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(7,1,0.7399)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(8,1,0.6995)
INSERT INTO [nannyValues] ([numLosingTrades],[nannySetId],[risk])VALUES(9,1,0.6654)
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(78,2.00,106.000000,'Jan 4 2010 7:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(63,2.00,-42.000000,'Jan 6 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(17,2.00,-328.000000,'Jan 7 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(64,2.00,-153.000000,'Jan 14 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(79,2.00,211.000000,'Jan 18 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(18,2.00,32.000000,'Jan 18 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(65,2.00,143.000000,'Jan 19 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(1,2.00,110.000000,'Jan 19 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(39,2.00,-158.000000,'Jan 27 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(80,2.00,10.000000,'Jan 27 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(40,2.00,-112.000000,'Jan 28 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(2,2.00,104.000000,'Jan 28 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(19,2.00,218.000000,'Jan 29 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(3,2.00,463.000000,'Feb 3 2010 7:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(20,2.00,394.000000,'Feb 4 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(66,2.00,605.000000,'Feb 4 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(41,2.00,-108.000000,'Feb 10 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(67,2.00,-66.000000,'Feb 11 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(4,2.00,-50.000000,'Feb 15 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(42,2.00,101.000000,'Feb 17 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(21,2.00,432.000000,'Feb 18 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(23,2.00,174.000000,'Feb 23 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(5,2.00,97.000000,'Feb 23 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(81,2.00,796.000000,'Feb 24 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(43,2.00,71.000000,'Feb 25 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(82,2.00,81.000000,'Mar 10 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(44,2.00,-24.000000,'Mar 12 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(83,2.00,-369.000000,'Mar 15 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(6,2.00,59.000000,'Mar 19 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(24,2.00,282.000000,'Mar 23 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(84,2.00,-158.000000,'Mar 23 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(45,2.00,229.000000,'Mar 24 2010 11:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(46,2.00,232.000000,'Mar 30 2010 3:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(68,2.00,494.000000,'Mar 31 2010 3:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(7,2.00,168.000000,'Apr 6 2010 4:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(85,2.00,8.000000,'Apr 8 2010 7:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(25,2.00,14.000000,'Apr 13 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(69,2.00,98.000000,'Apr 13 2010 7:00:00:000PM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(26,2.00,-78.000000,'Apr 20 2010 7:00:00:000AM')
INSERT INTO [accountPerformance] ([tradeId],[pctAccountRisked],[profit],[tradeDateTime])VALUES(8,2.00,259.000000,'Apr 20 2010 3:00:00:000PM')
go
I need to return in a select statement two extra columns on accountPerformance, however I don't want to create 2 new physical columns on the table. The column names to return are the existing columns plus: [numLosingTradesInARow] and [nannyRisk]. I will describe how each column should be calculated.
[numLosingTradesInARow]
This is the number of previous consecutive trades in a row where the profit was < 0 (ordered by tradeDateTime). So with our sample data the value for tradeId 64 would be 2 because there are 2 losing tades in a row before this trade (we don't count the current trade). For tradeId 63 the value would be 0 because the previous trade was a winner. For tradeId 79 the value would be 3 because there were 3 losing trades in a row before this one.
[nannyRisk]
To get the nannyRisk we need to join to the nannyValues table (with a nannySetId of 1) on accountPerformance.numLosingTradesInARow = nannyValues.numLosingTrades. So for tradeId 64 we should get a nannyRisk value of 1.2150 because there were two losing trades in a row before this one (we don't count the current trade). For tradeId 63 the value should be 2.0000 because the previous trade was a winner. For tradeId 79, the value should be 1.0429 because the previous three trades were losers. For tradeId 6 the value should be 1.2150 because there were 2 previous losers to this one in a row. If the numLosingTrades in a Row exceeds the max numLosingTrades in the nannyValues table then just use the risk value of the max(numLosingTrades). So for example if I happen to have 12 losers in a row then the nannyValue should be 0.6654 (the risk value of the maxLosingTrades).
Is there a way to do this set-based without cursors? I'm scratching my head. Thanks for looking.
October 27, 2010 at 4:24 am
Wow, what a great question! And I'm stuck at home with manflu and an expired evaluation copy of SQL2k8.
Ok here goes:
;WITH OrderedData AS (
SELECT accountPerformance, tradeId, pctAccountRisked, profit, tradeDateTime,
rn = ROW_NUMBER() OVER (ORDER BY tradeDateTime DESC)
FROM accountPerformance
), Calculator AS
(
SELECT accountPerformance, tradeId, pctAccountRisked, profit, tradeDateTime,
numLosingTradesInARow = CAST(0 AS INT)
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT t.accountPerformance, t.tradeId, t.pctAccountRisked, t.profit, t.tradeDateTime,
numLosingTradesInARow = CASE WHEN l.profit < 0 THEN l.numLosingTradesInARow+1 ELSE 0 END
FROM OrderedData t
INNER JOIN Calculator l ON l.rn+1 = t.rn
)
SELECT c.*, nannyRisk = ISNULL(n.Risk, @nannyRisk)
FROM Calculator c
LEFT JOIN nannyValues n ON c.numLosingTradesInARow = n.numLosingTrades
-- SELECT @nannyRisk = MAX(Risk) FROM nannyValues
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 4:50 am
Hi Chris, thanks for you reply. It doesn't seem to work, I get this error:
Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@nannyRisk".
October 27, 2010 at 4:53 am
keymoo (10/27/2010)
Hi Chris, thanks for you reply. It doesn't seem to work, I get this error:
Msg 137, Level 15, State 2, Line 18Must declare the scalar variable "@nannyRisk".
Put this at the top of the batch:
DECLARE @nannyRisk decimal(7, 4)
SELECT @nannyRisk = MAX(Risk) FROM nannyValues
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 6:02 am
Thanks I did that and I now get:
Msg 207, Level 16, State 1, Line 5
Invalid column name 'accountPerformance'.
:unsure:
October 27, 2010 at 6:07 am
keymoo (10/27/2010)
Thanks I did that and I now get:
Msg 207, Level 16, State 1, Line 5Invalid column name 'accountPerformance'.
:unsure:
Don't worry, we'll get there.
;WITH OrderedData AS (
SELECT tradeId, pctAccountRisked, profit, tradeDateTime,
rn = ROW_NUMBER() OVER (ORDER BY tradeDateTime DESC)
FROM accountPerformance
), Calculator AS
(
SELECT tradeId, pctAccountRisked, profit, tradeDateTime,
numLosingTradesInARow = CAST(0 AS INT)
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT t.tradeId, t.pctAccountRisked, t.profit, t.tradeDateTime,
numLosingTradesInARow = CASE WHEN l.profit < 0 THEN l.numLosingTradesInARow+1 ELSE 0 END
FROM OrderedData t
INNER JOIN Calculator l ON l.rn+1 = t.rn
)
SELECT c.*, nannyRisk = ISNULL(n.Risk, @nannyRisk)
FROM Calculator c
LEFT JOIN nannyValues n ON c.numLosingTradesInARow = n.numLosingTrades
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 6:23 am
🙂 I hope your manflu is being kind to you. I now get:
Msg 207, Level 16, State 1, Line 18
Invalid column name 'rn'.
October 27, 2010 at 6:32 am
keymoo (10/27/2010)
🙂 I hope your manflu is being kind to you. I now get:
Msg 207, Level 16, State 1, Line 18Invalid column name 'rn'.
LOL thanks! It's not the manflu which is the problem today, it's the absence of SQL Server to test with - especially when you have gone to all the effort of providing scripts. Try this, and meantime I apologise for wasting an hour of your time:
;WITH OrderedData AS (
SELECT tradeId, pctAccountRisked, profit, tradeDateTime,
rn = ROW_NUMBER() OVER (ORDER BY tradeDateTime DESC)
FROM accountPerformance
), Calculator AS
(
SELECT rn, tradeId, pctAccountRisked, profit, tradeDateTime,
numLosingTradesInARow = CAST(0 AS INT)
FROM OrderedData
WHERE rn = 1
UNION ALL
SELECT t.rn, t.tradeId, t.pctAccountRisked, t.profit, t.tradeDateTime,
numLosingTradesInARow = CASE WHEN l.profit < 0 THEN l.numLosingTradesInARow+1 ELSE 0 END
FROM OrderedData t
INNER JOIN Calculator l ON l.rn+1 = t.rn
)
SELECT c.*, nannyRisk = ISNULL(n.Risk, @nannyRisk)
FROM Calculator c
LEFT JOIN nannyValues n ON c.numLosingTradesInARow = n.numLosingTrades
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 27, 2010 at 6:40 am
Well Chris that was pretty impressive without an instance of SQL Server to test on. Works great, and thanks very much! 😎
October 27, 2010 at 6:46 am
You're welcome 🙂 Thanks loads for providing scripts and a perfect spec!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply