SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
yogi123
yogi123
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 394
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


cStudentID cCurrentGradeCode cAnticipatedGradeCode
002124963 99 01
002147726 01 99
002203944 02 03
002206137 99 04
002220423 03 99

Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26050 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26050 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
yogi123
yogi123
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 394
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
vinu512
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1687 Visits: 1625
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 ;-)
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6506
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26050 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6506
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
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26050 Visits: 17528
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Adi Cohn
Adi Cohn
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3657 Visits: 6506
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search