I have a table, which is destined to rapidly grow. To give you an idea, the table is :
CREATE TABLE [dbo].[Production]
[ID] [int] IDENTITY(1,1) NOT NULL,
[OlympusID] [int] NOT NULL,
[DateTimeStamp] [datetime] NOT NULL,
[PackCount] [int] NOT NULL,
[CaseCount] [int] NOT NULL,
[WorkOrderID] [int] NOT NULL,
[State] [bit] NOT NULL,
[RowID] [INT] INT
There will be a query that will read counts occuring within a shift (ie 06:00 - 14:00), HOWEVER
, since the counts occur every 5 minutes, I will need to include the last row before 06:00 AND the 1st row following 14:00.
Counts Time10 05:54
25 13:5891 14:02
So , the query will also need to include the above highlighted rows as well.
A solution that I have considered is a self join using rownumber().
I would join them as
select * from (SELECT * FROM ProductionTest where datetime='Some day') A INNER JOIN ProductionTest B ON A.RowID=B.RowID-1
Bear in mind that the table will REALLY grow. Undoubtedly, the table with include indexes(probably on date and RowID).
However, do you see any other better way ?
As always, I will really appreciate your thoughts.