Have a table/column that supports scheduling something for a future effective date. So, there can be several rows of which only one is currently in effect. Lame design aside, is there a way to model this relationship in the designer? What strategies might one use to fetch only the row currently in effect?
sproc snippet
FROM dbo.Plan iplan WITH (NOLOCK)
INNER JOIN dbo.PlanInstance planinstance WITH (NOLOCK)
ON iplan.InsurancePlanID = planinstance.InsurancePlanID
INNER JOIN (
SELECT
instance2.InsuranceInstanceID,
instance2.ClaimTitle,
instance2.Notes,
instance2.ClaimType,
instance2.SendElectronically,
instance2.TrackParticipation,
instance2.AllowUsage,
instance2.Email,
instance2.SwitchImplementationType,
instance2.EffectiveDate,
instance2.AssignsPin,
instance2.AssignsGrp,
instance2.IsGovernment,
instance2.InsuranceInstanceVersion
FROM dbo.InsuranceInstance instance2 WITH (NOLOCK)
INNER JOIN dbo.PlanInstance planinstance2 WITH (NOLOCK)
ON instance2.InsuranceInstanceID = planinstance2.InsuranceInstanceID
WHERE instance2.EffectiveDate <= GETDATE()
AND instance2.EffectiveDate = (
SELECT MAX( instance3.EffectiveDate )
FROM dbo.InsuranceInstance instance3 WITH (NOLOCK)
INNER JOIN dbo.PlanInstance planinstance3 WITH (NOLOCK)
ON instance3.InsuranceInstanceID = planinstance3.InsuranceInstanceID
WHERE planinstance3.InsurancePlanID = planinstance2.InsurancePlanID
AND instance3.EffectiveDate <= GETDATE()
)
) instance
ON planinstance.InsuranceInstanceID = instance.InsuranceInstanceID
