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