Home > database >  SQL Server - How to filter data based on data combination from two or more columns
SQL Server - How to filter data based on data combination from two or more columns

Time:06-21

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,

BrandName Year
Toys 2008
Footwear 2008
Cookware 2008
Toys 2009
Toys 2007
Footwear 2009
Footwear 2007
Clothes 2008

Expected output:

BrandName Year
Toys 2008
Footwear 2008
Toys 2009
Toys 2007
Footwear 2009
Footwear 2007

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 |
 ---- ----------------- ------- 
  • Related