Union Select filtering

Question

This is my first question in the site and I apologize for the vague or misleading title of the question but I could not come up with something better at the time of writing this question.

I need to change a report in Report Builder 2.0 ,using query as text and not a Stored Procedure, for users that have taken a sick leave in a given period. But the "catch" is that I have to include users that have zero instances of sick leave. The current query in pseudo code is like this:

SELECT 
    UserName
    FROM Leave
WHERE location = 'NY'
AND leave_type = 'Sick'

There is a label that counts distinct the result set and presents them as number of occurrences of sickness. I was advised to use a Union, but the union will return entries that are not sickness leave too. How will I do the filtering to distinguish between sick leave and other leave?

Example:

 SELECT 
    UserName
    FROM Leave
    WHERE location = 'NY'
    AND leave_type = 'Sick'
UNION
SELECT 
    UserName
    FROM Leave
    WHERE location = 'NY'  

I could not also find out how to run the query for the users without any sick leave and return it as a separate field for the report. I can only get the results of the first Select as fields. Meaning I could not have two Select in the query and have the fields of the report populated by both of them. Only the first Select's results are recognized as fields in the report builder.

Example:

  SELECT 
        UserNameWithSickLeave
        FROM Leave
        WHERE location = 'NY'
        AND leave_type = 'Sick'

SELECT UserNameWithoutSickLEave
FROM users
where username NOT IN(
 SELECT 
        UserNameWithSickLeave
        FROM Leave
        WHERE location = 'NY'
        AND leave_type = 'Sick'
)

Thank you.

Answer 1

If you are using a union to combine the sick and none sick people into one big list but want to distinct between the two groups you can just add a flag.

SELECT 
UserName
1 AS [Sick] -- edit here
FROM Leave
WHERE location = 'NY'
AND leave_type = 'Sick'

UNION

SELECT 
UserName
0 AS [Sick] -- edit here
FROM Leave
WHERE location = 'NY' 

It should then give you the full list but now an extra column saying 1 = sick people and 0 = none sick people. Then you can filter based on that to get your distinct groups.

Answer 2

You can use distinct to get the usernames and a case expression with a correlated subquery and exists to check if the user has ever taken leave for sickness.

-- Sample data.
declare @Leave as Table ( UserName VarChar(10), Location VarChar(2), Leave_Type VarChar(10) );
insert into @Leave ( UserName, Location, Leave_Type ) values
  ( 'Amy', 'NY', 'happy' ),
  ( 'Bob', 'NY', 'sick' ),
  ( 'Bob', 'NY', 'vacation' ),
  ( 'Cat', 'NY', 'pet' );
select * from @Leave;

-- The query.
select distinct UserName,
  case when exists ( select 42 from @Leave as IL where IL.UserName = L.UserName and Leave_Type = 'sick' )
    then 1
    else 0 end as Sick
  from @Leave as L;

The "given period" requirement can be handled by adding suitable data to the Leave table and checking for any overlap between the "given period" and the leave period.

Tip: The general check for overlapping ranges is Start1 <= End2 and Start2 <= End1. Refer to the overlap tag wiki for an explanation.