Hello Jeff,

Thank you for your reply and thanks for explaining best practises. Sorry I havent got back to your earlier, but I have been away from work. Please find the following data based upon your "Correct Way to Post Data".

As I stated earlier my problem is not getting the query to work, but the speed of the query. I have developed a vb6 front end and the intention is to allow to end-user to enter the number of days they wish to scan for a matching pattern and then to loop through all current patients to see if each patient has a matching pattern in the entire table ( currently over 4 millions records ). However, just finding a matching pattern for single patient can take 15 to 20 seconds. I also intended that once one or more patterns have been found that the/a query would then look at the following day results and see if this could forecast the patients blood pressure for the next day.

I hope this isn't too confusing and that you will let me know if you require any further information.

Kind regards

Laurence

--===== If the test table already exists, drop it

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Patient Data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[Patient Data]

GO

--===== Create the test table with

CREATE TABLE [dbo].[Patient Data] (

[TodayID] [int] IDENTITY (1, 1) NOT NULL ,

[PreviousDayID] [int] NULL ,

[PatientID] [int] NULL ,

[Date] [datetime] NULL ,

[Open%] [float] NULL ,

[High%] [float] NULL ,

[Low%] [float] NULL ,

[Close%] [float] NULL

) ON [PRIMARY]

GO

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY

--===== All Inserts into the IDENTITY column

SET IDENTITY_INSERT [dbo].[Patient Data] ON

--===== Insert the test data into the test table

INSERT INTO [dbo].[Patient Data]

([TodayID], [PreviousDayID], [PatientID], [Date], [Open%], [High%], [Low%], [Close%])

SELECT '4629170','4624881','12','29/02/2008',-1.12,1.12,-1.12,1.12UNION ALL

SELECT '4624881','4620598','12','28/02/2008',-3.84,-3.84,-3.84,-3.84UNION ALL

SELECT '4620598','4615809','12','27/02/2008',-1.74,0.6,-2.13,0.6UNION ALL

SELECT '4615809','4611052','12','26/02/2008',-0.44,1.77,-0.44,1.77UNION ALL

SELECT '4611052','4606819','12','25/02/2008',0,0.44,0,0.44UNION ALL

SELECT '4606819','4602038','12','22/02/2008',-1.79,-1.79,-1.79,-1.79UNION ALL

SELECT '4602038','4597285','12','21/02/2008',-0.39,-0.39,-0.39,-0.39UNION ALL

SELECT '4597285','4592909','12','20/02/2008',2.09,2.09,-2.13,-2.13UNION ALL

SELECT '4592909','4588286','12','19/02/2008',3.3,3.3,3.3,3.3UNION ALL

SELECT '4588286','4583621','12','18/02/2008',-0.02,-0.02,-1.11,-1.11UNION ALL

SELECT '4583621','4578897','12','15/02/2008',-2.56,-2.56,-2.56,-2.56UNION ALL

SELECT '4578897','4574160','12','14/02/2008',6.26,6.26,2.65,2.65UNION ALL

SELECT '4574160','4569529','12','13/02/2008',-4.17,-4.17,-4.17,-4.17UNION ALL

SELECT '4569529','4564912','12','12/02/2008',-3.93,0.25,-3.93,0.25UNION ALL

SELECT '4564912','4560435','12','11/02/2008',2.49,6.4,2.49,6.4UNION ALL

SELECT '4560435','4555994','12','08/02/2008',-1.45,-1.45,-6.64,-6.64UNION ALL

SELECT '4555994','4551511','12','07/02/2008',-5.88,-5.49,-5.88,-5.49UNION ALL

SELECT '4551511','4547020','12','06/02/2008',0,2,-0.76,2UNION ALL

SELECT '4626564','4622254','14','29/02/2008',0.67,0.67,0.67,0.67UNION ALL

SELECT '4622254','4617575','14','28/02/2008',0.99,1.97,0,1.48UNION ALL

SELECT '4617575','4612732','14','27/02/2008',0.5,1.93,0.5,0.5UNION ALL

SELECT '4612732','4608220','14','26/02/2008',0.25,1.17,-0.74,-0.74UNION ALL

SELECT '4608220','4603782','14','25/02/2008',0.29,0.73,-0.44,-0.44UNION ALL

SELECT '4603782','4599017','14','22/02/2008',0.25,0.44,-0.25,0.44UNION ALL

SELECT '4599017','4594539','14','21/02/2008',1.01,2.51,1.01,2.26UNION ALL

SELECT '4594539','4589944','14','20/02/2008',0.25,0.25,-1.26,0.25UNION ALL

SELECT '4589944','4585335','14','19/02/2008',-0.88,0.63,-0.88,0.25UNION ALL

SELECT '4585335','4580641','14','18/02/2008',0,0.76,-0.76,0.76UNION ALL

SELECT '4580641','4575900','14','15/02/2008',-1.5,-0.05,-1.9,-1.75UNION ALL

SELECT '4575900','4571181','14','14/02/2008',1.98,2.5,0.25,2.5UNION ALL

SELECT '4571181','4566589','14','13/02/2008',0.13,1.1,-0.32,-0.32UNION ALL

SELECT '4566589','4562080','14','12/02/2008',1.84,3.29,1.31,3.29UNION ALL

SELECT '4562080','4557661','14','11/02/2008',0,0,-2.07,-1.8UNION ALL

SELECT '4557661','4553190','14','08/02/2008',1.06,2.12,0.53,2.12UNION ALL

SELECT '4553190','4548718','14','07/02/2008',-1.05,-0.31,-1.05,-1.05UNION ALL

SELECT '4548718','4544184','14','06/02/2008',-2.08,-0.78,-2.08,-0.78UNION ALL

SELECT '4544184','4539679','14','05/02/2008',1.61,4.3,1.61,3.49UNION ALL

SELECT '4539679','4535235','14','04/02/2008',3.01,3.56,1.92,1.92

--===== Set the identity insert back to normal

SET IDENTITY_INSERT [dbo].[Patient Data] OFF