How to select values

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have student table with these values and columns

St_Name St_Code St_Date

Arvind 35 2/3/2012

Arvind 78 2/24/2012

Arvind 78 2/10/2012

Ram 35 2/20/2012

Krishna 35 4/1/2012

Ravi 35 2/1/2012

Ravi 78 2/20/2012

Ravi 78 2/14/2012

First I need to check whether the Student Has the st_code=78, if it is not there then need to get the top most student code that contains st_code=35.

Then getting the top most st_code=35 record, so we need to add 14 days to that st_date. Adding after 14 days we need to display and that must be less than the getdate().

Next->

If the St_code contains both 78 and 35 , then i need to get the top most student code that contains st_code=78.

Then getting the top most st_code=78 record, so we need to add 14 days to that st_date. Adding after 14 days we need to display and that must be less than the getdate().

So, Here how can i check both two conditions to write the query.

Help me Please..

Thanks,

Posted 8-Mar-12 14:28pm

Member 4391175

Add a Solution

Comments

walterhevedeich 8-Mar-12 20:33pm

What have you tried?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

foxyland · Answer 1 · 2012-03-08T16:05:00

@walterhevedeich:

SQL

--- Create sample data:
Create table #temp1 (St_Name Char(20), St_Code Char(2), St_Date Datetime)
Insert into #temp1 Values ('Arvind','35','2/3/2012')
Insert into #temp1 Values ('Arvind','78','2/24/2012')
Insert into #temp1 Values ('Arvind','78','2/10/2012')
Insert into #temp1 Values ('Ram','35','2/20/2012')
Insert into #temp1 Values ('Krishna','35','4/1/2012')
Insert into #temp1 Values ('Ravi','35','2/1/2012')
Insert into #temp1 Values ('Ravi','78','2/20/2012')
Insert into #temp1 Values ('Ravi','78','2/14/2012')

--- And here is the query:
if exists (select * from #temp1 where St_Code = '78')
	select Top 1 * from #temp1
	 where St_Code = '78'
	 order by St_Name
Else
	select Top 1 * from #temp1
	 where St_Code = '35' and St_Date+14 < GETDATE()
	 order by St_Name
 
Drop table #temp1
go

But, you said:
First I need to check whether the Student Has the st_code=78, if it is not there then need to get the top most student code that contains st_code=35.

What criteria is 'the top most'? Is it based on St_Name? Or is it based on St_Date? Anyway, if the query I gave you fulfill your needs, you could simply change the order by clause.

hth,
foxyland