I want to create the following indexed view:
CREATE VIEW [Cic].[vwMarker] WITH SCHEMABINDING
AS
Select
SubId,
marker.EquipmentID,
marker.ReadTime,
marker.CdsLotOpside,
marker.CdsLotBackside,
marker.CdteLotOpside,
marker.CdTeLotBackside
From dbo.Marker
Where dbo.Marker.ReadTime >= Convert(dateTime,'10/5/2011',120)
GO
CREATE UNIQUE CLUSTERED INDEX IX_vwMarker_ReadTime_EquipmentID
ON Cic.vwMarker (ReadTime, EquipmentID);
This works fine. However, what I would really like to do is to only include rows in this view that are two days old or newer, as of the current date/time the view is queried. I can't find a way to do this because I cannot use GetDate() in the Where predicate because it is non-deterministic. In other words, I'd like to do something like this, but cannot:
Where dbo.Marker.ReadTime >= Convert(dateTime,DateAdd(dd,-2,GetDate()) ,120)
Is there a way around this?