Want to get records only from particular set of numbers.

Question

Want to get records only from particular set of numbers.

selpoivre

Say Hey Kid

Points: 702
More actions
June 27, 2014 at 10:20 am

#296109

Table : Consumer
KNO Amount
8789 10
8896 15
6963 0
8959 30
8789 0
8896 0
6963 0
8959 0
select KNO from Consumer where KNO in ('8789','8896','6963') and amount > 0
above query result is
KNO
8789
8896
I want query so that put particular KNO and get that KNO whose amount is 0
Output :
KNO
6963

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

atultiwari.eng06 (6/27/2014)
Table : Consumer
KNO Amount
8789 10
8896 15
6963 0
8959 30
8789 0
8896 0
6963 0
8959 0
select KNO from Consumer where KNO in ('8789','8896','6963') and amount > 0
above query result is
KNO
8789
8896
I want query so that put particular KNO and get that KNO whose amount is 0
Output :
KNO
6963

WHERE KNO in ('8789','8896','6963')

AND KNO = 0

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

selpoivre Say Hey Kid Points: 702 More actions · Answer 2

WHERE KNO in ('8789','8896','6963')

AND KNO = 0

this will return empty records....

i want output

KNO

6963

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

SELECT KNO

FROM dbo.Consumer

WHERE KNO IN ('8789','8896','6963')

GROUP BY KNO

HAVING SUM(Amount) = 0

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

atultiwari.eng06 (6/27/2014)
WHERE KNO in ('8789','8896','6963')
AND KNO = 0
this will return empty records....
i want output
KNO
6963

hehe meant to put Amount = 0. Hopefully the query Jeff posted is close to what you are looking for.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/