Finding designation of an employee on a given date

Hi all,

I have two tables

CREATE TABLE [JEMP] (
[EMPID] [int] NOT NULL ,
[DESIGID] [int] NULL , -- CURRENT DESIGNATION OF EMPLOYEE
[DOB] [smalldatetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [JPRO] (
[PromoID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpID] [int] NOT NULL ,
[EffectiveDate] [smalldatetime] NOT NULL ,
[NewDesigID] [int] NOT NULL , -- PROMOTED TO DESIGNATION
[DesigID] [int] NULL -- PROMOTED FROM DESIGNATION
) ON [PRIMARY]
GO

INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(1,1,'1962-03-11 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(2,25,'1980-10-7 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(3,8,'1978-04-05 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(4,7,'1962-07-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(5,22,'1973-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(6,55,'1971-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(7,11,'1973-09-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(8,22,'1975-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(9,22,'1977-02-12 00:00:00')
INSERT INTO JEMP(EMPID,DESIGID,DOB) VALUES(10,23,'1984-07-11 00:00:00')

INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2002-15-11 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(3,'2003-03-01 00:00:00',8,7)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2002-01-04 00:00:00',20,22)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(4,'2005-05-01 00:00:00',7,20)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(5,'2001-10-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(6,'2001-08-01 00:00:00',55,NULL)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(7,'2003-10-01 00:00:00',11,8)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(8,'2001-09-01 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(9,'2002-01-05 00:00:00',22,23)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2002-11-01 00:00:00',24,25)
INSERT INTO JPRO(EmpID,EffectiveDate,NewDesigID,DesigID)
VALUES(10,'2003-11-15 00:00:00',23,24)

--
I wish to find the designation of employee on given date by using
promotion and master table . I am using the following query to get the
result

select isnull( ( select top 1 newdesigid from JPRO where
empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
JatinderHistory is usually modeled in durations with a (start_time, end_time)
pair; this lets you use a BETWEEN predicate for most of your queries.|||jsfromynr (jatinder.singh@.clovertechnologies.com) writes:
> I wish to find the designation of employee on given date by using
> promotion and master table . I am using the following query to get the
> result
> select isnull( ( select top 1 newdesigid from JPRO where
> empid=1 and effectivedate<'anygivendate' order by effectivedate desc )
> , (select desigid from empmast where empid=1) )
>
> It did give the result but looking for better method to solve this.

I don't see anything seriously wrong with that query. Here is an
alternate:

select Top 1 NewDesigID
from (select NewDesigID, EffectiveDate
from JPRO
where EmpID=7 and
EffectiveDate < '20050301'
union
select DESIGID, '19000101'
from JEMP
where EMPID=7) AS x
order by EffectiveDate desc

But I'm not sure in what way it would be "better".

If you are looking for a more ANSI way of doing it, you would have
to get the MAX(EffectiveDate) and then join back to the derived
table again. As you may guess, this is likely to be less effecient.
In SQL 2005, you could use a CTE (Common Table Expresssion) to avoid
repetition of the code for the derived table, but alas the query
plan is likely to be equally ineffecient.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

find all the employees under one manager (was "Need Help with Query")

I have an employee table with manager id and employee ids , i need to find all the employee ids for a manager id . Each employee can be a manager in turn . So I need to find all the employees under one manager and if any of the employee is in turn a manager , i need to find the employees under him as well .

The table structure is defined and i cannot edit it .

Please let me know if we could have a single query to do this .

Thank you
kishoreYou don't state what version of SQL Server you are using. New to SQL Server 2005 is Common Table Expressions (CTEs). An article on MSDN describes exactly what you are looking for: Recursive Queries Using Common Table Expressions (http://msdn2.microsoft.com/en-us/library/ms186243.aspx).

I'm afraid this is somewhat more tricky on SQL Server 2000.|||I need to support SQL server 2000 as well as 2005 ,also other Database like oracle , Db2 and sybase. Apart from Common Table Expression , is there any way i can do it .|||Well, the code won't be identical on the various platforms anyway. CTEs are supported on SQL Server 2005 and DB2, and perhaps the latest version of Sybase as well. It is a part of the SQL Standard since SQL:1999, so I would suggest using it where possible. For the rest you could create a temporary table, and use pretty much the same idea as in the CTE:

1. Insert all without a parent (top level)
2. Insert all whose having those in 1 as parent
3. Recursively insert all whose having their parent inserted, but are not inserted themselves yet.

I know, it's not very pretty code, but it works. Hope you got my idea :)|||SELECT e.known_as_and_surname
AS 'Employee'
,m.known_as_and_surname
AS 'Manager'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number

Any good to you?
Self-join genius courtesy of Rudy at www.r937.com :beer:|||Any good to you?
That would be nice for 2 levels (manager and employee).
Triumph wants a top person's subordinates from 1, 2, x levels down.

If there is a fixed number of levels you can union that number (minus one) of select statements. If not, the temporary table that roac describes would be useful.

<edit>
Congratulations!|||roac approach would work i guess , but i was looking for a solution where in a single query would do the trick , but i guess its not going to be the case . I needed this solution to improve performance .|||no single query is ever going to work the same in sql server 2000, sql server 2005, oracle, db2, and sybase

if you are writing an application that "abstracts" the database layer, you are never going to achieve your result by trying to abstract the sql

instead, you need to abstract the information request, and write specific sql modules for each database -- in this case, CONNECT BY for oracle, CTEs for those that support it (it's part of the sql standard), a recursive call for other databases, etc.|||Thanks to all for helping me. I think i will do the recurvise calls using Java .|||Well good luck!

I'd love to see a solution to this if/when you get one ;)|||Thanks to all for helping me. I think i will do the recurvise calls using Java .
Ugh.
You would get excellent performance using the algorithm suggested by ROAC. I'd bet it would beat any recursive algorithm hands down. Recursion requires a separate call for each item in the hierarchy, while ROAC's suggestion only requires one call for each level in the hierarchy.|||I noticed the thread changed title... This simple query solves it as the title asks ;)
To find all the employees under ONE manager:

SELECT e.known_as_and_surname AS 'Employee'
FROM employee AS e
LEFT OUTER JOIN employee AS m
ON m.employee_number = e.manager_number
WHERE m.known_as_and_surname = '<InsertNameOfManager>'|||Without editing the layout of the table you're going to have difficulty getting a work around. If you know the max number of levels then you could hack it with a single query (based on rudy's example) :

SELECT
e1.name AS 'Lvl1',
e2.name AS 'Lvl2',
e3.name AS 'Lvl3',
e4.name AS 'Lvl4',
e5.name AS 'Lvl5',
FROM employee AS e1
LEFT OUTER JOIN employee AS e2 ON e2.employee_number = e1.manager_number
LEFT OUTER JOIN employee AS e3 ON e3.employee_number = e2.manager_number
LEFT OUTER JOIN employee AS e4 ON e4.employee_number = e3.manager_number
LEFT OUTER JOIN employee AS e5 ON e5.employee_number = e4.manager_number|||Queston : does MSSQL (2000) support recursive procedures?|||even if there is no maximum number of levels, you could still use the 4-way self-join to show just 4 levels of subtree, with links at each bottom level node if it has any further levels below it

and of course clicking on one of the links returns up 4 lower levels below that node, so in effect it "recurses" down the tree but it is the user driving the process 4 levels at a time

usually, any tree that is large enough to have "unlimited" levels is at the same time also too large to allow the complete display of the entire tree, so recursion would be neither practical nor desirable anyway

the way i see it ;)|||Rudy you have a point, however if this is for report generation and there are a lot of employees/managers then full recursion is going to be needed. Or you're planning on making a nice big tree heirarchy using some fancy graphical functionality then i suspect returning the full set will still be beneficial.

Find a newly inserted record from table without using MAX or TOP

i have a table employee and i want to find the newly inserted record from employee table without using MAX And TOP ...it possible ? yes then How?

Quote:

Originally Posted by Ripendra007

i have a table employee and i want to find the newly inserted record from employee table without using MAX And TOP ...it possible ? yes then How?

i'm assuming you're using an identity column as the primary key on your table. if so you can say:

SELECT @.@.IDENTITY

your development environment might also provide this information in a more seamless way

Filtering the dataset

Hi All,
I have reports which return employee information. I want the report to have
an optional 3 parameters representing Entity, Business Unit and Cost Centre.
If any of these parameters(or a combination is used) I want to filter the
returned dataset to display only relevant rows.
Before anyone suggests using the parameters in the stored procedure, forget
it! The stored procedure is complex enough that it has to figure out the
users access level...so I'd rather filter after the fact.
I've hit a brick wall with this one, so if anyone has any fantastic ideas,
Id love to hear them.
Kind Regards,
Terry PinoFiltering the dataset can be done with the filter tab in the dataset.
U can try with the filters - Expression & Value. using custom expressions.
To help U out, I need more details abt the parameters and returned columns
with which u filter the dataset.
"Terry" wrote:
> Hi All,
> I have reports which return employee information. I want the report to have
> an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> If any of these parameters(or a combination is used) I want to filter the
> returned dataset to display only relevant rows.
> Before anyone suggests using the parameters in the stored procedure, forget
> it! The stored procedure is complex enough that it has to figure out the
> users access level...so I'd rather filter after the fact.
> I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> Id love to hear them.
> Kind Regards,
> Terry Pino|||Thanks Chans,
I worked it out after posting this article.Basically I ended up with 3
filter values each one something like this:
iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
null,"",Parameter.value1)
Getting around my problem of the filter parameter being null.
Always helps to talk about things;-)
Cheers,
Terry
"Chans" wrote:
> Filtering the dataset can be done with the filter tab in the dataset.
> U can try with the filters - Expression & Value. using custom expressions.
> To help U out, I need more details abt the parameters and returned columns
> with which u filter the dataset.
>
> "Terry" wrote:
> > Hi All,
> >
> > I have reports which return employee information. I want the report to have
> > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > If any of these parameters(or a combination is used) I want to filter the
> > returned dataset to display only relevant rows.
> > Before anyone suggests using the parameters in the stored procedure, forget
> > it! The stored procedure is complex enough that it has to figure out the
> > users access level...so I'd rather filter after the fact.
> > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > Id love to hear them.
> >
> > Kind Regards,
> > Terry Pino|||Did you filter the report or the dataset?
"Terry" wrote:
> Thanks Chans,
> I worked it out after posting this article.Basically I ended up with 3
> filter values each one something like this:
> iif(Parameter.value1 is null,"",Fields.value1) = iif(Parameter.value1 is
> null,"",Parameter.value1)
> Getting around my problem of the filter parameter being null.
> Always helps to talk about things;-)
> Cheers,
> Terry
> "Chans" wrote:
> > Filtering the dataset can be done with the filter tab in the dataset.
> > U can try with the filters - Expression & Value. using custom expressions.
> >
> > To help U out, I need more details abt the parameters and returned columns
> > with which u filter the dataset.
> >
> >
> > "Terry" wrote:
> >
> > > Hi All,
> > >
> > > I have reports which return employee information. I want the report to have
> > > an optional 3 parameters representing Entity, Business Unit and Cost Centre.
> > > If any of these parameters(or a combination is used) I want to filter the
> > > returned dataset to display only relevant rows.
> > > Before anyone suggests using the parameters in the stored procedure, forget
> > > it! The stored procedure is complex enough that it has to figure out the
> > > users access level...so I'd rather filter after the fact.
> > > I've hit a brick wall with this one, so if anyone has any fantastic ideas,
> > > Id love to hear them.
> > >
> > > Kind Regards,
> > > Terry Pino