I have a positions table in SQL Server 2008R2 (definition below).
In the system boxes there are positions.
I have a requirement to find a box, which has X free positions remaining. However, the X positions must be continuous (left to right, top to bottom i.e. ascending PositionID).
It has been simple to construct a query that finds a box with X positions free. I now have the problem of determining if the positions are continuous.
Any suggestions on a TSQL based solution?
Table Definition
` CREATE TABLE [dbo].[Position]( [PositionID] [int] IDENTITY(1,1) NOT NULL, [BoxID] [int] NOT NULL, [pRow] [int] NOT NULL, [pColumn] [int] NOT NULL, [pRowLetter] [char](1) NOT NULL, [pColumnLetter] [char](1) NOT NULL, [SampleID] [int] NULL, [ChangeReason] [nvarchar](4000) NOT NULL, [LastUserID] [int] NOT NULL, [TTSID] [bigint] NULL, CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED ( [PositionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]`
Edit
http://pastebin.com/V8DLiucN - pastebin link with sample positions for 1 box (all positions empty in sample data)
Edit 2
A 'free' position is one with SampleID = null