SELECT [dbo].[Receipts].[PolicyHeaderFK], >>> [ReceiptAmount], >>> [ReceiptDate], >>> [ReceiptNumber], >>> [ReceiptPK], >>> [ClientFK], >>> [PlanFK], >>> [PolicyNumber], >>> [PolicyPK], >>> [Code], >>> [Description], >>> [ClientNumber], >>> [CompanyName], >>> [FirstName], >>> [MiddleName], >>> [LastName] >>> FROM [dbo].[Receipts] >>> INNER JOIN [dbo].[Policyheaders] ON [dbo].[Receipts].[PolicyHeaderFK] = [PolicyHeaderPK] >>> INNER JOIN [dbo].[Policies] ON [PolicyHeaderPK] = [dbo].[Policies].[PolicyHeaderFK] >>> INNER JOIN (SELECT [PolicyHeaderFK], MAX(EffectiveDate) as EffectiveDate >>> FROM [dbo].[Policies] >>> GROUP BY [PolicyHeaderFK]) subquery ON [Policies].[PolicyHeaderFk] = subquery.policyHeaderFK AND Policies.EffectiveDate = subquery.EffectiveDate >>> inner join [dbo].[Plans] on [PlanFK] = [PlanPK] >>> inner join [dbo].[Clients] on [ClientFK] = [ClientPK] >>>WHERE 1 = 1 >>> AND ([PolicyNumber] LIKE @PolicyNumber) >>> AND ([LastName] LIKE @ClientName OR [CompanyName] LIKE @ClientName) >>> AND [ReceiptNumber] LIKE @ReceiptNumber>>>
declare @ReceiptNumber nvarchar(20) >>> set @ReceiptNumber = NULL >>> declare @ClientName nvarchar(20) >>> set @ClientName = NULL >>> declare @PolicyNumber nvarchar(20) >>> set @PolicyNumber = 'WLIF20120047'>>>