Help with Query

Hi Everyone,

I need help writing the following query. I have a table named DeptHistory that stores an employee's department history:

DeptHistory [DeptID(pk),EmpID(fk),StartDate,EndDate]

(EndDate will be NULL for the current department)

I need to write a query that will return all the department that employee was in between Jan 2007 to Jun 2007

Example:

--

Employee Name: Sam Costa

Service Dept 5/1/2006 - 12/5/2006

Parts Dept 12/6/2006 - 3/1/2007

Dispatch Dept 3/2/2007 - NULL

--

So the query should return Parts Dept and Dispatch Dept

Thank You,

-Sam

Code Snippet

select DeptID

from DeptHistory dh

where isnull(year(EndDate), 2007) = 2007

and EmpID = 1

|||

Hi DaleJ,

Thank you for your response. The query you provided does not check which depts the employee was in for a specifed date range.

|||

Well, If the end date isn't in 2007 (or null) then they weren't in that dept between jan 2007 and jun 2007.

|||

try the following query..

Code Snippet

Create Table #data (

[Dept] Varchar(100) ,

[SDate] datetime ,

[EDate] datetime

);

Insert Into #data Values('Service Dept','5/1/2006','12/5/2006');

Insert Into #data Values('Parts Dept','12/6/2006','3/1/2007');

Insert Into #data Values('Dispatch Dept','3/2/2007',NULL);

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or EDate between '01/01/2007' and '6/30/2007'

|||

what if an employee was employeed at a dept for the entire duration?

Code Snippet

Select * From #Data

Where

SDate between '01/01/2007' and '6/30/2007'

or isnull(EDate, getdate()) between '01/01/2007' and '6/30/2007'

or (SDate < '01/01/2007' and isnull(EDate, getdate()) > '6/30/2007')

also... for each end date, you'll have to check for null.

|||

As the approach below demonstrates, it is only necessary to determine if someone's StartDate is before the end of the period, and that their EndDate was sometime after the beginning of the period. (The current date is substitued for the NULL values.)

Code Snippet

SET NOCOUNT ON

DECLARE @.DeptHistory table
( RowID int IDENTITY,
DeptID int,
EmpID int,
StartDate smalldatetime,
EndDate smalldatetime
)

DECLARE @.Employee table
( EmpID int IDENTITY,
EmpName varchar(20)
)

DECLARE @.Department table
( DeptID int IDENTITY,
DeptName varchar(20)
)

INSERT INTO @.Employee VALUES ( 'Sam Costa' )
INSERT INTO @.Employee VALUES ( 'Bilbo Baggins' )

INSERT INTO @.Department VALUES ( 'Service' )
INSERT INTO @.Department VALUES ( 'Parts' )
INSERT INTO @.Department VALUES ( 'Dispatch' )

INSERT INTO @.DeptHistory VALUES ( 1, 1, '5/1/2006', '12/5/2006' )
INSERT INTO @.DeptHistory VALUES ( 1, 2, '5/1/2006', NULL )
INSERT INTO @.DeptHistory VALUES ( 2, 1, '12/6/2006', '3/1/2007' )
INSERT INTO @.DeptHistory VALUES ( 3, 1, '3/2/2007', NULL )

SELECT
d.DeptName,
e.EmpName,
h.StartDate,
h.EndDate
FROM @.DeptHistory h
JOIN @.Employee e
ON h.EmpID = e.EmpID
JOIN @.Department d
ON h.DeptID = d.DeptID
WHERE ( h.StartDate < '2007/07/01'
AND isnull( h.EndDate, getdate() ) >= '2007/01/01'
)

DeptName EmpName StartDate EndDate
-- -- -- -
Service Bilbo Baggins 2006-05-01 NULL
Parts Sam Costa 2006-12-06 2007-03-01
Dispatch Sam Costa 2007-03-02 NULL

(Output edited for display.)