Click here to Skip to main content
14,920,094 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I HAVE 2 ITEM ,ITEM NAME 'CHAIR' AND 'BASKET' AND A QUANTITY...I NEED THAT WHERE QUANTITY > 20 AND ITEM='CHAIR'; AND FOR ITEM='BASKET' ,QUANTITY >'09' SHOULD COME IN OUTPUT..

I FAILED ,NULLS ARE COMING..

I NEED THAT FOR ITEM CHAIR DQTY SHOULD SHOW >20 AND FOR ITEM BASKET DQTY SHOULD BE > 9,,IT SHOULD BE SHOWED IN MY OUTPUT..I DON'T WANT OTHER DQTY NUMBER IN MY OUTPUT

USING SQL-server-2014

What I have tried:

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,sasitename,symn,dqty,count(distinct(orna)) as orna
from ABCD

WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') 
GROUP BY TI.SubCategoryName,op.site,sasitename,symn,dqty;
Posted
Updated 8-Jan-18 23:38pm

How can you compare quantity with a string like '20' and '9'?
Just Remove '' and make sure your column data type is integer. if datatype is not integer then in sql convert your column data to integer and make sure for data which are not in integer format, o/p it as 0.Then obviously 0>20 and 0>9 will always false for non integer values and you will get nulls.Please up vote if this helps you out.
   
v3
Comments
Member 13518187 9-Jan-18 3:19am
   
@debasish mishra ......it is already integer and i have written > 20 only ..by mistake i wrote here
Member 13518187 9-Jan-18 3:19am
   
but output is not coming correct as per my question
[no name] 9-Jan-18 3:35am
   
You have not mentioned any table with alias TI and OP.Please write the sql query properly else it would be hard to know what is going wrong
You use op.dqty AND ti.SubCategoryName that means u have two tables. First check without case condition that ur normal query return all data or not. other wise provide the full query with join clause so that it clearly understandable the table relation.
   
Comments
Member 13518187 9-Jan-18 4:26am
   
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

WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET')
GROUP BY TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty;
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.
   
Comments
Member 13518187 9-Jan-18 7:59am
   
GIVING DIFFERENT RESULTS

SubCategoryName SubCategoryName site sasitename symn dqty
NULL 560-Tea 10201362 AL SARIB CAFE. November 1
NULL 560-Tea 10201653 Sherman restaurent December 0.0278
NULL 140-Dairy 10610489 GALAH FS October 0.25
NULL 140-Dairy 10610497 FLWER CITY December 1
NULL 140-Dairy 10610513 TAHA FS July 0.5
NULL 140-Dairy 10610516 EJAZA GRO January 0.25
NULL 140-Dairy 10610565 AL DOHA GRO November 1
NULL 140-Dairy 10610592 AL TAHIRE F/S August 2
NULL 140-Dairy 10610592 AL TAHIRE F/S September 2
140-Dairy 140-Dairy 10610602 MARWAN CAFTERIA February 20
NULL 140-Dairy 10610602 MARWAN CAFTERIA June 4
NULL 140-Dairy 10610602 MARWAN CAFTERIA May 8
NULL 140-Dairy 10610638 SARAYA FOODSTUFF January 0.25
NULL 140-Dairy 10610681 SHAIMA CAFÉ January 2
NULL 140-Dairy 10610714 JARIAH FOODSTUFF September 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900