As per ur query, i write down the below script. please check this.
CREATE TABLE item
(
Id int,
SubCategoryName nvarchar(100)
)
go
insert into item values (1,'CHAIR')
go
insert into item values (2,'BASKET')
go
insert into item values (3,'TOOL')
go
CREATE TABLE ABCD
(
ID INT,
CategoryId Int,
[site] nvarchar(100),
dqty nvarchar(100),
sasitename nvarchar(100),
symn nvarchar(100),
orna int
)
GO
insert into abcd values (1,1,'00',25,'ss','22',1)
go
insert into abcd values (1,1,'00',15,'ss1','221',1)
go
insert into abcd values (1,2,'00',25,'ss2','222',2)
go
insert into abcd values (1,2,'00',18,'ss3','223',2)
go
insert into abcd values (1,1,'00',45,'ss4','224',1)
go
insert into abcd values (1,3,'00',25,'ss5','225',0)
go
insert into abcd values (1,3,'00',25,'ss6','226',0)
go
SELECT
CASE WHEN op.dqty > 20 AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName
WHEN op.dqty > 9 AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName
ELSE NULL END AS SubCategoryName ,
TI.SubCategoryName,
op.[site],
op.sasitename,
op.symn,
op.dqty,
count(distinct(orna)) as orna
from ABCD op
inner join item ti on op.CategoryId=ti.id
WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET')
GROUP BY TI.SubCategoryName,op.[site],op.sasitename,op.symn,op.dqty;
Hope this will solve ur problem. Please up vote if this helps you out.