problem with select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem with select

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-16 : 03:35:14

Hi

I have the following stored procedure that returns some data such as interviewer name, amount candidate has to pay etc.



SELECT     TOP (1) dbo.Users_sys.Given_Names + ' ' + dbo.Users_sys.Surname AS Interviewer_Name, dbo.Users_sys.Email AS Interviewer_Email,Candidate_121.Interviewer_id AS Interviewer_id
 ,dbo.Candidate.ERM AS ERM, ermMail.Email AS ERM_Email
FROM         dbo.Users_sys INNER JOIN
                      dbo.Candidate_121 ON dbo.Users_sys.User_id = dbo.Candidate_121.Interviewer_id RIGHT OUTER JOIN
                      dbo.Candidate INNER JOIN
                      dbo.Settings ON dbo.Candidate.Country_id = dbo.Settings.Country_id ON dbo.Candidate_121.Candidate_id = dbo.Candidate.Candidate_id
                      RIGHT OUTER JOIN dbo.Users_sys ermMail on dbo.Candidate.erm = ermMail.user_id
WHERE     (dbo.Candidate.Candidate_id = @Candidate_id)
ORDER BY dbo.Candidate_121.Interview_date DESC

In the table Candidate_121 i have the following fields:
[Cand_121_ID] [int] IDENTITY
[Candidate_id] [int]
[Interviewer_id] [int]
[Attend] [bit]
[Date_Entered] [datetime]

I have to modify the statement above as follows:
I have to search for an entry for a candidate where attended=1. If there is more than one attended=1 i need to return the one with the latest date_entered.
if there is no entry with attend=1 then i need to return the entry with the latest date_entered.
Not sure how to modify the sp to reflect the changes.

Appreciate any help.

Thanks :-)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 03:58:14

SELECT Candidate_id, Interviewer_id, Attend, Date_Entered, [datetime] FROM (
SELECT Candidate_id, Interviewer_id, Attend, Date_Entered, [datetime], ROW_NUMBER() OVER (PARTITION BY Candidate_id ORDER BY Attended DESC, [datetime] DESC) AS recID
FROM Candidate_121) AS d
WHERE RecID = 1

Peter Larsson
Helsingborg, Sweden

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-16 : 04:38:44

Hi,

Thanks for the reply.
If i don't have and entry for attend=1 then I want it to return the row where entry=0 and date_entered is the last. However, it doesn't do that.
Also, if i have more than one attend=1 for that candidate i want it to return the entry with the latest date_entered.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 05:17:27

Where is "Entry" column?

The query will work as wished for.
Have you actually tried it yet?
If not, please do. If yes, post some sample data and I will give my suggestion a go on your provided sample data.
And for accuracy, please post expected output for comparison.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources