Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query Help Expand / Collapse
Author
Message
Posted Friday, December 21, 2012 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
Post #1399549
Posted Friday, December 21, 2012 12:31 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 13,008, Visits: 12,428
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 Moden's 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)
Post #1399552
Posted Friday, December 21, 2012 12:33 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 13,008, Visits: 12,428
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 Moden's 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)
Post #1399553
Posted Friday, December 21, 2012 12:35 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 1, 2014 12:13 PM
Points: 115, Visits: 342
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
Post #1399554
Posted Wednesday, December 26, 2012 4:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:13 AM
Points: 1,127, Visits: 1,594
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
Post #1400209
Posted Wednesday, December 26, 2012 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1400223
Posted Wednesday, December 26, 2012 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 13,008, Visits: 12,428
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 Moden's 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)
Post #1400236
Posted Wednesday, December 26, 2012 7:38 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1400246
Posted Wednesday, December 26, 2012 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:01 AM
Points: 13,008, Visits: 12,428
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 Moden's 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)
Post #1400258
Posted Wednesday, December 26, 2012 8:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 3:12 AM
Points: 2,112, Visits: 5,480
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/
Post #1400261
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse