Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Declare @param1 varchar(max);
- Declare @param2 varchar(max);
- Declare @param4 varchar(max);
- Declare @param5 varchar(max);
- Set @param1 = '%village%';
- Set @param2 = '%village%';
- Set @param4 = '%media%';
- Set @param5 = '%media%';
- SELECT p.*, c.Name AS CompanyName, mp.Info AS MobilePhone, bp.Info AS BusinessPhone, a.Address1, a.City, a.[State], a.PostalCode
- FROM ContactPersons p
- LEFT JOIN Locations l ON l.Id = p.LocationId
- OUTER APPLY (
- SELECT TOP 1 m.Info, m.ContactPersonId, m.Label, m.[Type]
- FROM ContactMethods m
- WHERE Label = 'Mobile' AND [Type] = 'Phone' AND p.Id = ContactPersonId
- ) AS mp
- OUTER APPLY (
- SELECT TOP 1 m.Info, m.ContactPersonId, m.Label, m.[Type]
- FROM ContactMethods m
- WHERE Label = 'Business' AND [Type] = 'Phone' AND p.Id = ContactPersonId
- ) AS bp
- OUTER APPLY (
- SELECT TOP 1 a.Address1, a.City, a.[State], a.PostalCode, a.LocationId
- FROM Addresses a
- WHERE a.IsBilling = 1 AND a.LocationId = l.Id
- ) AS a
- LEFT JOIN Companies c ON c.Id = p.CompanyId WHERE ((
- (FirstName LIKE @param2 OR LastName LIKE @param2 OR p.Notes LIKE @param2 OR dbo.ufnStripName(c.Name) LIKE @param1 OR c.Notes LIKE @param2 OR c.OtherNames LIKE @param2)
- OR p.Id IN (SELECT ContactPersonId FROM ContactMethods WHERE Info LIKE @param2)
- OR c.Id IN (SELECT c2.ID FROM Companies c2
- INNER JOIN Company_Class cc on c2.Id = cc.CompanyId
- INNER JOIN Classes cl ON cl.Id = cc.ClassId
- WHERE cl.Tag LIKE @param2)
- OR
- (p.LocationId IS NOT NULL AND p.LocationId IN (
- SELECT l.Id FROM Locations l WHERE Name LIKE @param2 OR l.Id IN (
- SELECT Id FROM Addresses WHERE
- Address1 LIKE @param2 OR
- Address2 LIKE @param2 OR
- City LIKE @param2 OR
- [State] LIKE @param2 OR
- PostalCode LIKE @param2 OR
- Country LIKE @param2
- )
- )
- )
- ) AND (
- (FirstName LIKE @param5 OR LastName LIKE @param5 OR p.Notes LIKE @param5 OR dbo.ufnStripName(c.Name) LIKE @param4 OR c.Notes LIKE @param5 OR c.OtherNames LIKE @param5)
- OR p.Id IN (SELECT ContactPersonId FROM ContactMethods WHERE Info LIKE @param5)
- OR c.Id IN (SELECT c2.ID FROM Companies c2
- INNER JOIN Company_Class cc on c2.Id = cc.CompanyId
- INNER JOIN Classes cl ON cl.Id = cc.ClassId
- WHERE cl.Tag LIKE @param5)
- OR
- (p.LocationId IS NOT NULL AND p.LocationId IN (
- SELECT l.Id FROM Locations l WHERE Name LIKE @param5 OR l.Id IN (
- SELECT Id FROM Addresses WHERE
- Address1 LIKE @param5 OR
- Address2 LIKE @param5 OR
- City LIKE @param5 OR
- [State] LIKE @param5 OR
- PostalCode LIKE @param5 OR
- Country LIKE @param5
- )
- )
- )
- )) ORDER BY c.Name ASC, p.FirstName, p.LastName OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement