[SOLVED] Union Select filtering

Union Select filtering

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.

Solution

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.