ChrisM@Work (7/22/2014)
Something like this?
DECLARE @MinPeriod int
SELECT @MinPeriod = MIN(a.period)
FROM agr.dbo.atrans a
CROSS APPLY (
SELECT CutOffTid = ISNULL(MAX(ccas_id),0)
FROM dbo.fbs_trans
) x
WHERE a.agrtid > x.CutOffTid
As Chris said, and then just wrap that in a CREATE PROCEDURE with @MinPeriod being a parameter. Should be fine. If not, ask your DBA for an execution plan, and we can evaluate further.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability