[Category SQL]
with tmp_Data as
(
Select Distinct StandardCategory_StandardCategory, ListingCategory_StandardCategoryID
, ListingCategory_Category, ListingCategory_CategoryID
, ReferralCount = ISNULL((Select Sum(ReferralCount) From Listing Inner Join ReferralSummary On ReferralSummary.ListingID = Listing.ListingID Where Listing.CategoryID = ListingCategory_CategoryID AND ReferralDate > DateAdd(day, -1, GetDate())), 0)
From Category_View Where CategoryActiveMemberListingCount > 0
)
Select ListingCategory_Category, ListingCategory_CategoryID, ListingCategory_StandardCategoryID as StandardCategoryID
From tmp_Data
Where
(ReferralCount = 0)
OR
(ListingCategory_CategoryID in (Select top 1 tmp_Data2.ListingCategory_CategoryID From tmp_Data tmp_Data2 Where tmp_Data2.ListingCategory_StandardCategoryID = tmp_data.ListingCategory_StandardCategoryID))
OR
(ListingCategory_StandardCategoryID) = 0
Order By ListingCategory_StandardCategoryID, StandardCategory_StandardCategory, ReferralCount DESC
Directory of Business Council of Westchester Business - The Business Council of Westchester | Rye Brook, NY