How to get the reverse o/p for this query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How to get the reverse o/p for this query?

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-23 : 01:20:14

Hi,
I have a query to list the employees who are free b/n a given date range. Now i want the exactly opposite o/p.
The query is like this:

select e.emp_seq_no, e.emp_name,p.prj_project_name,rra.rra_start_date,rra.rra_end_date
from rpmg_resource_allocations rra
inner join employee e on e.emp_seq_no = rra.emp_seq_no
and e.emp_status_item_code = 1 and rra.rra_status = 1
inner join project p on p.prj_seq_no = rra.prj_seq_no
inner join sys_business_code_detail sbcd on sbcd.sbd_item_code = p.prj_status_item_code
and sbcd.sbm_type_code = 'SZ' and sbcd.sbd_item_desc not in ('Completed','Scrapped')
where rra.rra_end_date = (select max(rra1.rra_end_date) from rpmg_resource_allocations rra1
where rra1.emp_seq_no = e.emp_seq_no)
and not exists
(select 1 from rpmg_resource_allocations
where ((@from_date between rra.rra_start_date) and (rra.rra_end_date)) OR
(@to_date between (rra.rra_start_date) and (rra.rra_end_date))
or (@from_date < rra.rra_start_date and @to_date >rra.rra_end_date)
))

order by e.emp_name

this gives the employees who are free between start and end dates.
so the rra_start_date and rra_end_date of those employees will NOT be within the given range.

Now, i want those employees whose start and end dates are within that range. How to change this query to get that?

Thanks in advance....

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-07-23 : 01:54:50

It is a lot easier to help you if you give us the details of the table and the columns and tell us what you want to find.

mrm23
Posting Yak Master

198 Posts

Posted - 2008-07-23 : 02:52:38

Hi,
I have to get something like this:

EmployeeName | Project | Start_date | End_date
ABC | XYZ | 01-JAN-2001 | 31-DEC-2004
DEF | HJJ | 20-MAY-2007 | 15-AUG-2008

Now asuume this is my data. Now the user enters two dates : from_date and to_date
Those two date should fall between the start_date and end_date.
Now if i enter 01-JUL-2008 to 31-JUL-2008, i must get the second record in the above result set because tht employee is allocated to some project between those dates.
Now the query is showing those records which DO NOT fall under that date range,so i want to reverse the query to get the desired o/p.
Pls help me...

Thanks for your reply....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 04:14:47

select fields
from yourtable
where @from_date>=Start_date
and @to_date <=End_Date

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources