January 27, 2011 at 2:27 am
Hi,
This is a bit of a long one, but quite interesting.
I have the following (simplified) tables:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tradeStops_ssc]') AND type in (N'U'))
DROP TABLE [dbo].[tradeStops_ssc]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[orders_ssc]') AND type in (N'U'))
DROP TABLE [dbo].[orders_ssc]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[trades_ssc]') AND type in (N'U'))
DROP TABLE [dbo].[trades_ssc]
GO
CREATE TABLE [dbo].[trades_ssc](
[tradeId] [int] NOT NULL,
[symbol] [char](6) NOT NULL,
[amountPerUnit] [decimal](18, 2) NOT NULL,
[pctAccountRisked] [decimal](4, 2) NOT NULL,
[accountId] [int] NULL,
CONSTRAINT [PK_trades_ssc] PRIMARY KEY CLUSTERED
(
[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
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_orders_ssc_trades_ssc]') AND parent_object_id = OBJECT_ID(N'[dbo].[orders_ssc]'))
ALTER TABLE [dbo].[orders_ssc] DROP CONSTRAINT [FK_orders_ssc_trades_ssc]
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_orders_ssc]') AND parent_object_id = OBJECT_ID(N'[dbo].[orders_ssc]'))
ALTER TABLE [dbo].[orders_ssc] DROP CONSTRAINT [CK_orders_ssc]
GO
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_orders_ssc_1]') AND parent_object_id = OBJECT_ID(N'[dbo].[orders_ssc]'))
ALTER TABLE [dbo].[orders_ssc] DROP CONSTRAINT [CK_orders_ssc_1]
GO
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tradeStops_ssc_trades_ssc]') AND parent_object_id = OBJECT_ID(N'[dbo].[tradeStops_ssc]'))
ALTER TABLE [dbo].[tradeStops_ssc] DROP CONSTRAINT [FK_tradeStops_ssc_trades_ssc]
GO
CREATE TABLE [dbo].[tradeStops_ssc](
[tradeStopId] [int] NOT NULL,
[tradeId] [int] NOT NULL,
[price] [decimal](18, 6) NOT NULL,
[time] [datetime] NOT NULL,
CONSTRAINT [PK_tradeStops_ssc] PRIMARY KEY CLUSTERED
(
[tradeStopId] 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
ALTER TABLE [dbo].[tradeStops_ssc] WITH CHECK ADD CONSTRAINT [FK_tradeStops_ssc_trades_ssc] FOREIGN KEY([tradeId])
REFERENCES [dbo].[trades_ssc] ([tradeId])
GO
ALTER TABLE [dbo].[tradeStops_ssc] CHECK CONSTRAINT [FK_tradeStops_ssc_trades_ssc]
GO
CREATE TABLE [dbo].[orders_ssc](
[orderId] [int] NOT NULL,
[tradeId] [int] NOT NULL,
[units] [tinyint] NOT NULL,
[side] [char](1) NOT NULL,
[price] [decimal](18, 6) NOT NULL,
[signalTypeId] [varchar](3) NOT NULL,
[orderDateTime] [datetime] NOT NULL,
CONSTRAINT [PK_orders_ssc] PRIMARY KEY CLUSTERED
(
[orderId] 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
ALTER TABLE [dbo].[orders_ssc] WITH CHECK ADD CONSTRAINT [FK_orders_ssc_trades_ssc] FOREIGN KEY([tradeId])
REFERENCES [dbo].[trades_ssc] ([tradeId])
GO
ALTER TABLE [dbo].[orders_ssc] CHECK CONSTRAINT [FK_orders_ssc_trades_ssc]
GO
ALTER TABLE [dbo].[orders_ssc] WITH CHECK ADD CONSTRAINT [CK_orders_ssc] CHECK (([side]='S' OR [side]='B'))
GO
ALTER TABLE [dbo].[orders_ssc] CHECK CONSTRAINT [CK_orders_ssc]
GO
ALTER TABLE [dbo].[orders_ssc] WITH CHECK ADD CONSTRAINT [CK_orders_ssc_1] CHECK (([signalTypeId]='XXX' OR [signalTypeId]='SC' OR [signalTypeId]='MX' OR [signalTypeId]='IE'))
GO
ALTER TABLE [dbo].[orders_ssc] CHECK CONSTRAINT [CK_orders_ssc_1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LatestFxPrices_ssc]') AND type in (N'U'))
DROP TABLE [dbo].[LatestFxPrices_ssc]
GO
CREATE TABLE [dbo].[LatestFxPrices_ssc](
[bid] [decimal](19, 6) NOT NULL,
[ask] [decimal](19, 6) NOT NULL,
[midPrice] [decimal](22, 8) NULL,
[symbol] [char](6) NOT NULL,
[dt] [datetime] NOT NULL,
CONSTRAINT [PK_LatestFxPrices_ssc] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[dt] 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
With the following sample data:
SET NOCOUNT ON
BEGIN TRANSACTION
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(167,'CHFJPY',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(168,'CHFJPY',100000.00,3.33,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(169,'CHFJPY',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(170,'CHFJPY',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(171,'CHFJPY',100000.00,2.18,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(172,'CHFJPY',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(173,'CHFJPY',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(174,'EURCHF',100000.00,3.33,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(175,'EURCHF',100000.00,1.13,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(176,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(177,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(178,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(179,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(180,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(181,'EURCHF',100000.00,2.18,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(182,'EURCHF',100000.00,2.18,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(183,'EURCHF',100000.00,3.00,1)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(184,'EURUSD',100000.00,3.00,NULL)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(188,'GBPUSD',100000.00,2.00,NULL)
INSERT INTO [trades_ssc] ([tradeId],[symbol],[amountPerUnit],[pctAccountRisked],[accountId])VALUES(189,'AUDJPY',50000.00,3.00,2)
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(165,167,84.310000,'May 14 2010 7:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(166,168,81.200000,'Jun 30 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(167,169,81.610000,'Jul 7 2010 7:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(168,170,82.590000,'Aug 9 2010 11:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(169,171,81.960000,'Aug 31 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(170,172,82.270000,'Sep 14 2010 7:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(171,173,84.070000,'Sep 22 2010 11:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(172,174,1.489200,'Jan 6 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(173,175,1.474500,'Jan 28 2010 11:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(174,176,1.473200,'Feb 4 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(175,177,1.471300,'Feb 8 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(176,178,1.464500,'Mar 10 2010 3:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(177,179,1.427700,'May 10 2010 11:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(178,180,1.428400,'Jun 3 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(179,181,1.394600,'Feb 17 2010 7:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(180,182,1.359400,'Aug 3 2010 7:00:00:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(181,183,1.369700,'Aug 13 2010 3:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(182,184,1.391200,'Nov 3 2010 7:00:00:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(183,184,1.391400,'Nov 3 2010 7:23:23:000PM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(187,188,1.580000,'Jan 26 2011 1:12:07:000AM')
INSERT INTO [tradeStops_ssc] ([tradeStopId],[tradeId],[price],[time])VALUES(188,189,81.000000,'Jan 26 2011 9:24:45:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(623,167,2,'S',82.510000,'IE','May 14 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(624,167,1,'B',80.390000,'MX','May 17 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(625,167,2,'S',81.060000,'SC','May 18 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(626,167,1,'B',79.980000,'MX','May 19 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(627,167,2,'B',79.690000,'XXX','May 19 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(628,168,2,'B',82.240000,'IE','Jun 30 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(629,168,1,'S',82.010000,'MX','Jul 1 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(630,168,1,'S',82.290000,'XXX','Jul 2 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(631,169,2,'B',83.170000,'IE','Jul 7 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(632,169,1,'S',83.640000,'MX','Jul 8 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(633,169,1,'S',83.580000,'XXX','Jul 9 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(634,170,2,'S',81.700000,'IE','Aug 9 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(635,170,1,'B',81.320000,'MX','Aug 10 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(636,170,2,'S',80.930000,'SC','Aug 11 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(637,170,1,'B',80.520000,'MX','Aug 12 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(638,170,2,'B',81.770000,'XXX','Aug 12 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(639,171,2,'B',83.030000,'IE','Aug 31 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(640,171,1,'S',83.090000,'MX','Sep 1 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(641,171,1,'S',83.170000,'XXX','Sep 3 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(642,172,2,'B',83.320000,'IE','Sep 14 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(643,172,1,'S',85.280000,'MX','Sep 15 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(644,172,1,'S',85.210000,'XXX','Sep 16 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(645,173,2,'B',85.540000,'IE','Sep 22 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(646,173,1,'S',85.580000,'MX','Sep 23 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(647,173,1,'S',86.030000,'XXX','Sep 24 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(648,174,2,'S',1.481000,'IE','Jan 6 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(649,174,1,'B',1.479900,'MX','Jan 7 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(650,174,1,'B',1.483400,'XXX','Jan 7 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(651,175,2,'S',1.468700,'IE','Jan 28 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(652,175,2,'B',1.474500,'XXX','Jan 29 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(653,176,2,'S',1.467200,'IE','Feb 4 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(654,176,1,'B',1.460300,'MX','Feb 4 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(655,176,1,'B',1.473200,'XXX','Feb 5 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(656,177,2,'S',1.464700,'IE','Feb 8 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(657,177,1,'B',1.467700,'MX','Feb 9 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(658,177,1,'B',1.467500,'XXX','Feb 9 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(659,178,2,'S',1.461500,'IE','Mar 10 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(660,178,1,'B',1.462100,'MX','Mar 11 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(661,178,1,'B',1.452700,'XXX','Mar 17 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(662,179,2,'S',1.417000,'IE','May 10 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(663,179,1,'B',1.409500,'MX','May 11 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(664,179,2,'S',1.403900,'SC','May 12 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(665,179,3,'B',1.404600,'XXX','May 13 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(666,180,2,'S',1.410600,'IE','Jun 3 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(667,180,1,'B',1.397800,'MX','Jun 4 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(668,180,2,'S',1.389600,'SC','Jun 4 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(669,180,1,'B',1.388400,'MX','Jun 7 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(670,180,1,'B',1.388800,'MX','Jun 8 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(671,180,1,'B',1.379700,'XXX','Jun 8 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(672,181,2,'S',1.383400,'IE','Jun 17 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(673,181,1,'B',1.377300,'MX','Jun 17 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(674,181,2,'S',1.371600,'SC','Jun 18 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(675,181,1,'B',1.373300,'MX','Jun 21 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(676,181,2,'S',1.367500,'SC','Jun 21 2010 11:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(677,181,1,'B',1.359800,'MX','Jun 23 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(678,181,1,'B',1.346400,'MX','Jun 25 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(679,181,2,'B',1.325100,'XXX','Jun 30 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(680,182,2,'B',1.371200,'IE','Aug 3 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(681,182,1,'S',1.382500,'MX','Aug 5 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(682,182,2,'B',1.388500,'SC','Aug 10 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(683,182,1,'S',1.383400,'MX','Aug 10 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(684,182,2,'S',1.378500,'XXX','Aug 11 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(685,183,2,'S',1.345400,'IE','Aug 13 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(686,183,1,'B',1.332700,'MX','Aug 16 2010 3:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(687,183,2,'S',1.337000,'SC','Aug 18 2010 7:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(688,183,1,'B',1.314100,'MX','Aug 23 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(689,183,1,'B',1.304300,'MX','Aug 25 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(690,183,1,'B',1.307000,'XXX','Aug 26 2010 3:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(691,184,2,'B',1.407600,'IE','Nov 3 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(692,184,1,'S',1.420700,'XXX','Nov 5 2010 7:00:00:000PM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(693,184,1,'S',1.407400,'MX','Nov 5 2010 11:00:00:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(697,188,2,'B',1.583800,'IE','Jan 26 2011 1:12:07:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(698,189,2,'B',81.500000,'IE','Jan 26 2011 9:24:45:000AM')
INSERT INTO [orders_ssc] ([orderId],[tradeId],[units],[side],[price],[signalTypeId],[orderDateTime])VALUES(699,189,1,'S',81.980000,'IE','Jan 26 2011 11:00:00:000AM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.991930,0.992380,0.99215500,'AUDCAD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.941300,0.941690,0.94149500,'AUDCHF','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(81.925000,81.959000,81.94200000,'AUDJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.300600,1.301100,1.30085000,'AUDNZD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.996710,0.997000,0.99685500,'AUDUSD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.948670,0.949190,0.94893000,'CADCHF','Jan 26 2011 1:42:13:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(82.570000,82.608000,82.58900000,'CADJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(87.015000,87.053000,87.03400000,'CHFJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.371770,1.372170,1.37197000,'EURAUD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.360970,1.361460,1.36121500,'EURCAD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.291590,1.291910,1.29175000,'EURCHF','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(7.451290,7.453090,7.45219000,'EURDKK','Jan 26 2011 1:41:31:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.862180,0.862440,0.86231000,'EURGBP','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(112.408000,112.437000,112.42250000,'EURJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(7.871650,7.875100,7.87337500,'EURNOK','Jan 26 2011 1:42:06:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.784390,1.785020,1.78470500,'EURNZD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(8.862700,8.866200,8.86445000,'EURSEK','Jan 26 2011 1:42:02:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(2.158390,2.159660,2.15902500,'EURTRY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.367540,1.367790,1.36766500,'EURUSD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.590820,1.591310,1.59106500,'GBPAUD','Jan 26 2011 1:42:15:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.578310,1.578880,1.57859500,'GBPCAD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.497770,1.498230,1.49800000,'GBPCHF','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(130.353000,130.397000,130.37500000,'GBPJPY','Jan 26 2011 1:42:15:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(2.069390,2.070420,2.06990500,'GBPNZD','Jan 26 2011 1:42:13:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.585910,1.586210,1.58606000,'GBPUSD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(14.265000,14.295000,14.28000000,'NOKJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.762490,0.763000,0.76274500,'NZDCAD','Jan 26 2011 1:42:13:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.723510,0.724050,0.72378000,'NZDCHF','Jan 26 2011 1:42:10:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(62.973000,63.009000,62.99100000,'NZDJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.766170,0.766440,0.76630500,'NZDUSD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(12.668000,12.698000,12.68300000,'SEKJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.995180,0.995420,0.99530000,'USDCAD','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(0.944330,0.944600,0.94446500,'USDCHF','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(5.447850,5.449870,5.44886000,'USDDKK','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(7.784490,7.786210,7.78535000,'USDHKD','Jan 26 2011 1:41:19:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(82.188000,82.214000,82.20100000,'USDJPY','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(12.048500,12.052200,12.05035000,'USDMXN','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(5.755090,5.758300,5.75669500,'USDNOK','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(6.479700,6.483200,6.48145000,'USDSEK','Jan 26 2011 1:42:14:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.280690,1.281610,1.28115000,'USDSGD','Jan 26 2011 1:42:11:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(1.578140,1.578810,1.57847500,'USDTRY','Jan 26 2011 1:41:35:000PM')
INSERT INTO [LatestFxPrices_ssc] ([bid],[ask],[midPrice],[symbol],[dt])VALUES(7.097860,7.102500,7.10018000,'USDZAR','Jan 26 2011 1:42:14:000PM')
COMMIT
I also have a TALLY Table[/url] 😛
I need to explain some things before I get to the actual question:
* A trade has many orders. The orders_ssc table are orders into the forex market.
* An order consists of a number of logical [units] mulitplied by the amount per unit [amountPerUnit] to give an [amount].
* orders_ssc.[side] denotes a Buy (B) or a Sell (S)
* orders_ssc.[signalTypeId] describes the type of order. IE is the initial entry for the trade. SC is a Scale-In or an addition to the position. MX is a minor exit (scale-out) and XXX is a final exit (trade is now flat with no position in the market).
* tradeStops_ssc contains information about the STOP price for a trade.
* LatestFxPrices_ssc is actually a view in my database to the latest price in the market - this gets updated frequently. The price I am interested in is the midPrice (halfway between the bid and the ask price).
When a new trade is created, a row is created in trades_ssc, orders_ssc and tradeStops_ssc. the trades table contains high level information about the entire trade. The orders table contains information about the order that has been placed in the market, and the tradeStops table contains the current STOP price. If the market reaches the stop price then the trade gets closed.
The forex market moves in increments called pips. So if I buy GBP/USD at 1.5860 with a stop at 1.5840 and sell at 1.5870 I make a profit of 10 pips, with an initial risk of 20 pips (1.5860 - 1.5840). If the market dropped after I placed my trade I would get out of the market at 1.5840 with a 20 pip loss. Each pip has a value depending on how much is traded.
Trade amounts are grouped logically into units. The initial entry into the market is always placed with 2 units. So if I am trading an amount of 10,000 units of currency per logical unit and I placed 2 units in the market, I have 2 * 10,000 units in the market (20,000). The trades table stores the amount per logical unit (which stays the same for the entire trade), and the orders table stores the number of units traded. So a typical trade might go like this:
* 11:00am Buy 2 units (signalTypeId IE) of GBP/USD at 1.5860 with 10,000 per unit, stop at 1.5850 [current price 1.5860]
* 11:30am current price is now 1.5870, so I have a floating profit/loss of 10 pips (1.5870 - 1.5860) per unit, with a total floating profit of +20 pips and a floating risk of 20 pips. Floating risk is the initial entry price (1.5860) from the trade stop price (1.5850) times the number of units (2) = 20 pips.
* 11:32am current price is now 1.5875 so I decide to sell 1 unit (signalTypeId MX) at 1.5875. So I now have banked +15 pips (fixed profit) and have a remaining floating profit of +15 to give a total of +30 pips profit. Floating risk is now the entry price (1.5860) minus the current stop price (1.5850) * number of units (1) = 10 pips.
* 11:58am current price is now 1.5890, I decide to buy 2 more units (signalTypeId SC) at 1.5890 with the stop at 1.5850. My current floating risk now is initial entry (1.5860) - current stop price (1.5850) * number of units (1) = 10 pips, PLUS the 2 new units I have just bought: 1.5890 minus current stop (1.5850) * number of units (2) = 80 pips. Add that to the initial entry (10 pips risk) and we have a total floating risk of 90 pips. Total profit now is +15 (banked fixed profit) plus the floating profit which is current price minus the buy price of the outstanding units (1 @ 1.5860 = +30pips) and (2 @ 1.5890 = 0 pips) for a total of 15 pips fixed and 30 pips floating = 45 pips total profit.
* 12:02pm current price is now 1.5910, and the Stop price gets updated to 1.5870. Total floating profit is current price (1.5910) minus buy price of the first unit (1.5860) * number of open units (1) = 50 pips PLUS current price (1.5910) minus buy price of the two new units (1.5890) * number of open units(2) = 20 * 2 = 40 pips. Add to the original unit and we have a floating profit of 50 + 40 = 90 pips. Add THAT to the banked fixed profit and we have a total combined profit of 90 + 15 = 105 pips. The current pips at risk is the buy price of each unit - the stop price (1.5860 * 1) - 1.5870 = -10 pips PLUS (1.5890 * 2) - 1.5870 = +40 pips. So the total pips at risk is -10 + 40 = 30 pips.
* 12:10pm current price is now 1.5895 and I decide to sell all three open units (signalTypeId XXX). Total profit is the fixed profit of 15 pips, plus (1.5895 - 1.5860) * 1 plus (1.5895 - 1.5890) * 2 = 15 + 35 + 10 = TOTAL PROFIT of 60 pips.
At this point as there are no open units the trade is effectively closed because the number of buys = the number of sells for the trade. The above scenario happens in reverse when going short. So GBP/USD could be sold at 1.5860 with a buy tradeStop at 1.5900 with the hope that prices fall.
What I want to do from my data is have a query that calculates, for each trade, the following columns:
* fixedProfitPips (described above as the current fixed profit level)
* floatingProfitPips (described above in the example as the current floating profit in pips)
* combinedProfitPips (effectively fixedProfit + floatingProfit)
* floatingRiskPips
The values will only be non-zero for currently open trades. In my sample data there are only two open trades, the rest are closed. The query must work for buy trades and sell trades ie where the side is B (buy) or S (sell) for the initial entry (signalTypeID = 'IE').
Thanks very much for reading, I feel like offering an amazon voucher to the person that solves it as it's not an easy question. Should I do that, or is it too vulgar? :w00t:
January 27, 2011 at 11:55 am
I tried to find the data in the tables used in your verbal description. But I couldn't.
You should start offering a voucher for the person that would provide ready to use sample data matching your verbal description...
The next voucher would go to the person providing the expected result based on your sample data.
Edit: Did you try to search for a freelancer/consultant doing it for you? The request exceeds the purpose of a forum post "just slightly".
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply