Today Visual Changes is a recognized international leader in producing the most advanced, results-producing, corrective skin care products. As an innovator in unsurpassed product technology and outstanding product formulations, we are dedicated to constant and never-ending improvement to always bring you the very best in the skin care industry. Our goal is to empower you to achieve exceptional results and unprecedented levels of success in your quest for superb skin care. We strive for your complete satisfaction.
select min(USID) as idProduct, min(USprice) as price, max(USprice) as maxPrice, max(USlistprice) as listPrice, min(description) as description, min(prodName) as prodName , min(inventory.USimageUrl) as imageURL, sum(sold365) as sales, min(prodGroupItems.prodGroupID) as prodGroupID, min(gCount.groupCount) as groupCount, min(prodGroups.divider) as divider, min(prodGroups.name) as groupName, sum(count) as count , avg(avgstars) as avgstars, min(US_callOrder) as IH_callOrder, min(US_showPrice) as IH_ShowPrice, min(US_CAllOrderPrice) as callOrderPrice, min(brands.idbrand) as idbrand, min(brands.brandName) as brandName, min(freezes) as freezes, min(aerosol) as aerosol, min(leaks) as leaks, min(podeclined) as podeclined, max(freeGiftID) as freeGiftID, max(USTreshHold) as treshHold FROM [dbo].[inventory] left join brands on brands.idbrand = inventory.idbrand left join prodGroupItems on prodGroupItems.itemID = inventory.itemID left join prodGroups on prodGroupItems.prodGroupID = prodGroups.prodGroupID left join (select prodGroupId, count(prodGroupID) as groupCount from prodGroupItems left join inventory on inventory.itemID = prodGroupItems.itemID Where USActive = 1 group by prodGroupID) as gCount on gCount.prodGroupId = prodGroups.prodGroupID left join (SELECT itemID, COUNT(stars) AS COUNT, round(cast(SUM([stars]) as float)/COUNT([stars]) *2,0)/2 AS avgstars FROM pcReviews WHERE active=1 group by itemID) as reviews on reviews.itemID = inventory.itemID left join freegifts on freeGifts.itemID = inventory.itemID WHERE [inventory].[active]=-1 and USID <>0 and USActive= 1 and brands.USBlock = 0 AND brands.IDBrand in (258) group by CASE When prodGroupItems.prodgroupID is null then cast(newID() as varchar(255)) ELSE cast(prodGroupItems.prodgroupID as varchar(255)) END