Query Help

Question

Post reply

Query Help

yogi123

SSCrazy

Points: 2932
More actions
December 21, 2012 at 12:18 pm

#271853

Hello
I need to develop one logic.
Please help me in that.
Here is table and data
create table #temp
(cStudentID VARCHAR(12),
cCurrentGradeCode VARCHAR(12),
cAnticipatedGradeCode VARCHAR(12),
)
insert into #temp values ('002121506','99','99')
insert into #temp values ('002124963','99','01')
insert into #temp values ('002141019','99','99')
insert into #temp values ('002147726','01','99')
insert into #temp values ('002203944','02','03')
insert into #temp values ('002206137','99','04')
insert into #temp values ('002220423','03','99')
Now i need to follow some business rule
if cCurrentGradeCode and cAnticipatedGradeCode both '99' then i need to exclude.
if cCurrentGradeCode = '99' and cAnticipatedGradeCode <> '99' then need to display
if cCurrentGradeCode <> '99' and cAnticipatedGradeCode = '99' then need to display
so expected output is
cStudentIDcCurrentGradeCodecAnticipatedGradeCode
0021249639901
0021477260199
0022039440203
0022061379904
0022204230399

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

Excellent job posting ddl, sample date and desired output!!! I wish everybody would post their questions like this.

Here is one way to do this.

select * from #temp

where cCurrentGradeCode + cAnticipatedGradeCode <> '9999'

order by cStudentID

_______________________________________________________________

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/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Here is another way.

select t.* from #temp t

join #temp t2 on t.cStudentID = t2.cStudentID

where t2.cCurrentGradeCode <> '99' or t2.cAnticipatedGradeCode <> '99'

order by t2.cStudentID

_______________________________________________________________

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/

yogi123 SSCrazy Points: 2932 More actions · Answer 3

Sean Lange (12/21/2012)
Here is another way.
select t.* from #temp t
join #temp t2 on t.cStudentID = t2.cStudentID
where t2.cCurrentGradeCode <> '99' or t2.cAnticipatedGradeCode <> '99'
order by t2.cStudentID

Great !!!

Thank You so Much

vinu512 SSCoach Points: 15729 More actions · Answer 4

Sean Lange (12/21/2012)
Excellent job posting ddl, sample date and desired output!!! I wish everybody would post their questions like this.
Here is one way to do this.
select * from #temp
where cCurrentGradeCode + cAnticipatedGradeCode <> '9999'
order by cStudentID

This was good thinking Sean....I thought of this too....but then saw that you had already posted it. Nice 1. 🙂

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

Am I missing something? Seems to me that this should work:

select cStudentID, cCurrentGradeCode, cAnticipatedGradeCode

from #temp

where cCurrentGradeCode <> '99' or cAnticipatedGradeCode <> '99'

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Adi Cohn-120898 (12/26/2012)
Am I missing something? Seems to me that this should work:
select cStudentID, cCurrentGradeCode, cAnticipatedGradeCode
from #temp
where cCurrentGradeCode <> '99' or cAnticipatedGradeCode <> '99'
Adi

According to the requirements that won't work.

if cCurrentGradeCode and cAnticipatedGradeCode both '99' then i need to exclude.

The way you have it coded it will return a row if either of those columns is not 99. Make sense now?

_______________________________________________________________

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/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

Makes perfect sense. I was missing something:-)

Adi

edited: Sorry, just had a look at it. It does seems to work. According to the where clause one of the columns has to be different then 99

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Adi Cohn-120898 (12/26/2012)
Makes perfect sense. I was missing something:-)
Adi
edited: Sorry, just had a look at it. It does seems to work. According to the where clause one of the columns has to be different then 99
Adi

But the original requirement is that BOTH columns are not 99 not just one of them.

_______________________________________________________________

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/

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 9

The original request was that if both of the values are 99, then we have to exclude that line. It is enough that one of the columns won't be 99. The original poster did not specify what to do if both of the values don't equal 99, but from his desired results you can see that he wouldn't want to exclude them (see the third row in the expected results). In that case if one of the columns is different then 99 or both of them are different then 99, they should be included in the resultset. I think that the criteria WHERE cCurrentGradeCode <> '99' or cAnticipatedGradeCode <> '99' does the job.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

You are right Adi. I was the one missing something. :blush:

_______________________________________________________________

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/