Created
March 4, 2019 15:07
-
-
Save ahrherrera/683ff0248213ceb3ed3d440e86334a11 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @Result = | |
| ( | |
| Select DISTINCT p.ProfileID as 'ProfileID', | |
| p.names as 'Names', | |
| p.lat as 'Latitude', | |
| p.lng as 'Longitude', | |
| p.city as 'City', | |
| p.state as 'State', | |
| p.picUrl as 'picUrl', | |
| p.rating as 'rate', | |
| (select | |
| s.ServiceID, | |
| s.name | |
| from dbo.Service s | |
| inner join serviceProfiles sp | |
| on sp.service_id = s.ServiceID | |
| inner join profiles pro | |
| on pro.ProfileID = sp.profile_id | |
| where pro.ProfileID = p.ProfileID | |
| FOR JSON PATH | |
| ) as 'Services', | |
| ( | |
| select | |
| r.rating as 'Rating', | |
| r.description as 'Description', | |
| r.dateCreated as 'Date', | |
| prof.names as 'Customer.Name', | |
| prof.picUrl as 'Customer.picUrl' | |
| from dbo.Reviews r | |
| inner join profiles prof | |
| on prof.ProfileID = r.customer_id | |
| where r.contractor_id = p.ProfileID | |
| FOR JSON PATH | |
| ) as 'Reviews', | |
| p.insurance as 'Insurance', | |
| p.license as 'License' | |
| from dbo.profiles p | |
| inner join dbo.serviceProfiles sp | |
| on p.ProfileID = sp.profile_id | |
| inner join dbo.service s | |
| on s.ServiceID = @Service | |
| where p.type_id = 2 and p.availability = 1 and | |
| NOT EXISTS ( | |
| SELECT profile.ProfileID from Profiles profile | |
| inner join searches search | |
| on search.ProfileID = @ProfileID | |
| inner join invitation iv | |
| on iv.search_id = search.SearchID | |
| where iv.status = 0 | |
| ) | |
| for json path, INCLUDE_NULL_VALUES | |
| ) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment