query with loop - 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)

query with loop

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-02-23 : 04:21:05

how ia can write query for the following requirement

i need to assign manager for particular employee

manager designation should be greater than the employee designation

so iam checking the designations and picking the employees with that designation .out of few records iamassigning one employee as a manager to the employee

before picking employee as manager iam checking whether that designation is present in employee table if that designation is not present then pick next+1 designation employees

the loop should go on like this til;l the designation is present in employee table

for we need cursor to check????

is their any body to give solution for this
if soo i will be very thankfull

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 04:22:37

it will be easier if you can post the table structure with some sample data and the result that you want

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-02-23 : 04:24:09

quote:
Originally posted by khtan

it will be easier if you can post the table structure with some sample data and the result that you want

KH

declare @grade int
select @grade=Grade from l_Grade where DesignationId=@Id
print @grade

declare @dept int
select @dept=DepartmentId from l_DesignationMaster where DesignationId=@Id
print @dept

declare @max int
select @max=max(g.Grade) from l_Grade g
join l_DesignationMaster d on d.DesignationId=g.DesignationId
where d.DepartmentId=@dept and d.status=1
print @max

if(@grade=@max)
begin
print @dept

select 'SIL-'+ CONVERT(VARCHAR,c.Employee_Id)+' ' + E.Employee_First_Name as EMPLOYEE_CODE,c.Employee_Id as Manager_Id
FROM o_currentjob C
JOIN O_Employee E on E.Employee_Id = C.Employee_Id

where c.JobTitle_Id in (
select g.DesignationId from l_Grade g
join l_DesignationMaster d on d.DesignationId=g.DesignationId
join o_currentjob c on c.JobTitle_Id=g.DesignationId
where g.grade=(select max(Grade) from l_Grade) )group by C.Employee_Id ,E.Employee_First_Name

end

else
begin
----selecting the manager for particular designation depending on grade

IF EXISTS (select 'SIL-'+ CONVERT(VARCHAR,c.Employee_Id)+' ' + E.Employee_First_Name as EMPLOYEE_CODE,c.Employee_Id as Manager_Id
FROM o_currentjob C
JOIN O_Employee E on E.Employee_Id = C.Employee_Id

where c.JobTitle_Id in(select g.DesignationId
from l_DesignationMaster d
join l_Grade g
on d.DesignationId=g.DesignationId
join l_DepartmentMaster e
on d.DepartmentId=e.DepartmentId
where d.DepartmentId=@dept
and d.Status=1 and e.Status=1
and Grade = @grade+1
and g.DesignationId in (select Jobtitle_id from o_currentjob) group by g.DesignationId,d.DesignationCode)group by C.Employee_Id ,E.Employee_First_Name)
BEGIN
select 'SIL-'+ CONVERT(VARCHAR,c.Employee_Id)+' ' + E.Employee_First_Name as EMPLOYEE_CODE,c.Employee_Id as Manager_Id
FROM o_currentjob C
JOIN O_Employee E on E.Employee_Id = C.Employee_Id

where c.JobTitle_Id in(select g.DesignationId
from l_DesignationMaster d
join l_Grade g
on d.DesignationId=g.DesignationId
join l_DepartmentMaster e
on d.DepartmentId=e.DepartmentId
where d.DepartmentId=@dept
and d.Status=1 and e.Status=1
and Grade = @grade+1
and g.DesignationId in (select Jobtitle_id from o_currentjob) group by g.DesignationId,d.DesignationCode) group by C.Employee_Id ,E.Employee_First_Name
print 'NEXT'
end

ELSE
Begin
select 'SIL-'+ CONVERT(VARCHAR,c.Employee_Id)+' ' + E.Employee_First_Name as EMPLOYEE_CODE,c.Employee_Id as Manager_Id
FROM o_currentjob C
JOIN O_Employee E on E.Employee_Id = C.Employee_Id

where c.JobTitle_Id in(select g.DesignationId
from l_DesignationMaster d
join l_Grade g
on d.DesignationId=g.DesignationId
join l_DepartmentMaster e
on d.DepartmentId=e.DepartmentId
where d.DepartmentId=@dept
and d.Status=1 and e.Status=1
and Grade = @grade+2
and g.DesignationId in (select Jobtitle_id from o_currentjob) group by g.DesignationId,d.DesignationCode)group by C.Employee_Id ,E.Employee_First_Name
PRINT 'NEXT + 1'
END
end

Malathi Rao

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 04:31:04

quote:
Originally posted by khtan

it will be easier if you can post the table structure with some sample data and the result that you want

KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources