I was trying to filter out data in the dashboard based on two fields, for example, product category and year.

I have data where some products are sold in 2007, 2008, and 2009, while others are sold only in 2008. I want to exclude data where sale data for products with only 2008 year as the sale year.

For example,

Expected output:

I tried following SQL but does not seem to work

Select BrandName, Concat(BrandName,Year(UpdateDate)), SOUNDEX(Concat(BrandName,Year(UpdateDate))) as Data from Dbo.DimProduct
Group by BrandName, Concat(BrandName,Year(UpdateDate))
having Count(SOUNDEX(Concat(BrandName,Year(UpdateDate)))) > 1
Order by SOUNDEX(Concat(BrandName,Year(UpdateDate)))

Basically, if there is only one record like Clothes2008 and Cookware2008, exclude them from the resultset.

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ProductCategory VARCHAR(30), _year CHAR(4));
INSERT INTO @tbl (ProductCategory, _year) VALUES
('Toys', '2008'),
('Footwear', '2008'),
('Cookware', '2008'),
('Toys', '2009'),
('Toys', '2007'),
('Footwear', '2009'),
('Footwear', '2007'),
('Clothes', '2008');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT * 
       , total_count = COUNT(*) OVER (PARTITION BY ProductCategory ORDER BY ProductCategory)
       , _2008_count = SUM(IIF (_year='2008', 1, 0)) OVER (PARTITION BY ProductCategory ORDER BY ProductCategory)
    FROM @tbl
)
SELECT ID, ProductCategory, _year 
FROM rs
WHERE total_count <> _2008_count
ORDER BY ID;

Output

 ---- ----------------- ------- 
| ID | ProductCategory | _year |
 ---- ----------------- ------- 
|  1 | Toys            |  2008 |
|  2 | Footwear        |  2008 |
|  4 | Toys            |  2009 |
|  5 | Toys            |  2007 |
|  6 | Footwear        |  2009 |
|  7 | Footwear        |  2007 |
 ---- ----------------- -------