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'>