One recordset from two date ranges

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 2000 Forums

Transact-SQL (2000)

One recordset from two date ranges

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-01-07 : 09:00:26

Doug writes "Here is my SQL dilemma:

Say there is an employee who was a freelancer from 01/01/04 - 06/01/04, and then a fulltime employee from 06/02/04 - 12/31/04.

This employee's job was Designer from 01/01/01 - 03/01/04, and then Sr. Designer from 03/02/04 - 12/31/04.

-----------------------

Data for basic employee is stored in one table:
base.fname
base.lname
base.emp_id

Data for category (freelancer, fulltime, parttime, etc.) is stored in one table:
employ.emp_id
employ.category
employ.startdate
employ.enddate

Data for job (designer, sr. designer, etc.) is stored in yet another:
job.emp_id
job.jobname
job.startdate
job.enddate

I need to write a query that would correctly return this result set:


Startdate    Enddate    Category       Job
01/01/01     03/01/04   Freelancer     Producer
03/02/01     06/01/04   Freelancer     Sr. Producer
06/02/04     12/31/04   Fulltime       Sr. Producer

What might this query look like? A simple join wont work, because the data in job is simply duplicated for every occurrence of employ.

Thanks so much for any insight."

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-07 : 11:12:05

I think this will do it:

select case
when e.startdate < j.startdate then e.startdate
else j.startdate
end AS StartDate,
case
when e.enddate < j.startdate then e.enddate
else j.enddate
end AS EndDate,
e.category,
j.jobname
from employ e
join job j on e.emp_id = j.emp_id
and (e.startdate between j.startdate and j.enddate
or e.enddate between j.startdate and j.enddate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources