Need urjent output - 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 2012 Forums

Transact-SQL (2012)

Need urjent output

Author

Topic

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 04:56:04

There is 2 tables
1st table student_reg (col- id, stu_name) ,
2nd table student_details (col-date_of_birth and interview_date,stu_id)

student_reg data.

1 Anna
2 smita
3 smita
4 madhuri
5 madhuri
6 madhuri
7 devi

student_details data.
date of birth interview_date student_id

1-3-2012 1-3-2012 1

1-1-2012 21-3-2012 2

1-2-2012 21-3-2012 3

31-1-2011 1-6-2012 4

31-1-2011 11-7-2012 5

31-1-2011 21-3-2012 6

31-1-2015 1-3-2012 7

my question is student-id 4,5,7 is same name and same dob birth
so last_applied_date will be of previous id interview_date

need output is :

student_id student_name dob_of_birth last_applied_date

4 Madhuri 31-1-2011 Null
5 Madhuri 31-1-2011 1-6-2012
6 Madhuri 31-1-2011 11-7-2012

Means

N/A

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 07:56:55

can anyone tell the answer??plzzzzzzzzzzzz

N/A

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 07:56:56

can anyone tell the answer??plzzzzzzzzzzzz

N/A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 08:22:38

This is clearly homework. We're happy to help but you need to have a go at it yourself first. Please try to write a query to do what you need, post it here, and we'll give you hints on next steps.

The thing is, if we answer homework questions, students will learn less. We want you to succeed, not just in this course, but in your subsequent professional career.

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 11:56:17

select t.*,
CASE WHEN count(t.candidate_name)>1 and count(t.date_of_birth)>1 THEN min(c.interview_date)
end as last_appiled_date,

CASE WHEN cand_status= 0 THEN concat('Rejected in', " " , t.round)
WHEN cand_status= 1 THEN concat('Selected in', " " , t.round)
WHEN cand_status=2 THEN concat('On hold in', " " , t.round) end as Interview_status
from candidate_registration intr inner join candidate_personal_detail c on intr.id=c.candidate_id

left JOIN

(
select c.candidate_id,candidate_name,date_of_birth,c.interview_date,round,
(select can_status from candidate_status where id=max(b.id)) as cand_status
from candidate_status b right join candidate_registration a on a.id=b.candidate_id
inner join candidate_personal_detail c on a.id=c.candidate_id
left join interview_round i on i.id=b.round_id
group by a.id,candidate_name, date_of_birth

) t
on c.candidate_id = t.candidate_id
and c.interview_date = t.interview_date

group by intr.id,candidate_name, date_of_birth;

N/A

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 11:57:24

i tried my best, but not getting perfect answer.

Thanks in advance..

N/A

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-05-01 : 12:23:54

This query should do it:


SELECT id
	,stu_name
	,date_of_birth
	,lag(interview_date) OVER (
		PARTITION BY stu_name ORDER BY interview_date
		) AS last_applied_date
FROM student_reg r
INNER JOIN student_details d ON r.id = d.stu_id
ORDER BY stu_name
	,id
	,last_applied_date

Note that the results differ from what you expected. Analyzing that, you can see that for madhuri, Interview date of 1 June 2012, the previous date is 21 March 2012, not null. Same idea for the other rows.

anita.86
Starting Member

21 Posts

Posted - 2015-05-01 : 12:55:45

Showing error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( PARTITION BY candidate_name ORDER BY interview_date ) AS last_applied_date' at line 4 1.279 sec

N/A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-01 : 13:04:33

quote:
Originally posted by anita.86

Showing error:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( PARTITION BY candidate_name ORDER BY interview_date ) AS last_applied_date' at line 4 1.279 sec

You are using MySQL. SQLTeam.com is for Microsoft SQL Server. You'll want to post your question on a site that specializes in MySQL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources