Subquery problem (what should I use!)

Question

Post reply

Subquery problem (what should I use!)

alovegrove

SSC Rookie

Points: 25
More actions
February 22, 2008 at 9:50 am

#184232

I have a problem with the following query and its sub-query. The query which will be used in a spreadsheet (the variables will be replaced by parameters) retrieves the data to be displayed through the main query but what it actually displays is using the sub-query as one of it's conditions. It should only display those employees if their employee code in the 'Employee_Code' field is within the results of the sub-query and also their value in the 'Absence_start_date' field is within the 2 date variables (@DATE1 and @DATE2). How do I make this work so it only shows those employees within the subquery? I've used the EXISTS command but this just shows all records in the main query if it returns any in the sub-query. Any help would be appreciated!
DECLARE @DATE1 DATETIME
SET @DATE1 = '2007-01-01'
DECLARE @DATE2 DATETIME
SET @DATE2 = '2007-01-31'
SELECT *
FROM Employee_Absence_Days
where Absence_start_date >= @DATE1 AND Absence_start_date <= @DATE2
and
-- what do I put here?
select Employee_Absence_Days.Employee_code,Employee_Absence_Days.tfirst,Employee_Absence_Days.tsurname, Absence_start_date, No_of_absence_days, Absence_end_date,
case
when datediff(year, Absence_start_date, @date1) = 1 AND (Absence_end_date >= @DATE1)
then No_of_absence_days - (datediff (day,@date1,Absence_end_date) + 1)
when datediff(year, Absence_start_date, @date1) > 1 AND datediff(year, Absence_end_date, @date1) = 1
then No_of_absence_days - (datediff (day,Absence_start_date,@date1 - 365))
else No_of_absence_days
end
as 'Calculated_absence_days'
from Employee_Absence_Days
where (Absence_start_date (@DATE1-366)
or Absence_end_date (@DATE1-366))
and
(case
when datediff(year, Absence_start_date, @date1) = 1 AND (Absence_end_date >= @DATE1)
then No_of_absence_days - (datediff (day,@date1,Absence_end_date) + 1)
when datediff(year, Absence_start_date, @date1) > 1 AND datediff(year, Absence_end_date, @date1) = 1
then No_of_absence_days - (datediff (day,Absence_start_date,@date1 - 365))
else No_of_absence_days
end
>= 20))

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Stanislas Biron SSC Veteran Points: 262 More actions · Answer 1

One option your have to accomplish this is to use the "IN" operator and return only the Employee_code in your subquery

Like :

SELECT *

FROM Employee_Absence_Days

where Absence_start_date >= @DATE1 AND Absence_start_date <= @DATE2

and

Employee_Code IN ( SELECT Employee_Code from ......[the rest of your subQuery] )

You still get all your fields from Employee_Absence_Days and the rows are filtered to show only the employees you want.

Stanislas Biron

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

If you can write an "IN" like that, then you can also write it as a JOIN to a derived table. A derived table is a query in parenthesis that appears in the FROM clause and is given an alias just like a table. They are usually very effective and can also be thought of as an "inline view".

Lookup "Derived Tables, Using the FROM Clause" in Books Online for more info.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

Or, as this looks like the database is SQL Server 2005, I'd start with the subquery only. When it returns the appropriate data, turn it into a CTE and use the CTE in an inner join with the main query. It's along the same lines as Jeff mentioned using derived tables, but I think it looks a little cleaner and easier to maintain.

😎