MINUS/INTERSECT Operator in SQL

MINUS/INTERSECT Operator in SQL

  

Dear friends,

i'm developing an Attendance Taking App, where users have to submit their daily attendance, PRESENT/ABSENT by clicking on a button.

In cases when users forget to enter their attendance, the admins should have a page where they can view who have not submitted their attendance.

What i'm trying to do is see who did not submit their attendance for that day by having the Total number of User in User Table Minus those who have submitted their attendance (refelected in ADetails table).

I have no idea how to do it.

I have tried MINUS/INTERSECT SQL but to no avail...am i supposed to use SQL for this?

Please give me some help i've been working for days :(


MINUS SQL

SELECT {User}.[Name]
FROM {User}
MINUS
SELECT {ADetails}.[Name]
FROM {ADetails}


INTERSECT SQL

SELECT {User}.[Name]
FROM {User}
INTERSECT
SELECT {ADetails}.[Name]
FROM {ADetails}

ADetails (Contains User's Attendance) Table, Filetered By StartDate = CurrDate()


User Table

No need for SQL, join the 2 tables by [Name] and include all records in {User}, set the condition to {ADetails}.[Name] = ""


Hi Wei


No need to use Minus/Intersect or any advanced SQL. You can join ADetails with User (Adetails.UserId = User.Id) and set a filter where ADetails.Status = NullIdenitifer() (or NullTextIdentifier() since i think your Attendance field is a text one).

This will get all users that have not set the attendance using only Aggregates, which is a best practice (unless doing mass updates or deletes).


Hope this helps!

  CLSJ

Hi Guys,


I have tried both of your solution, but it does not seem to get what i want. When i run both of your filters i get no records to show.

Maybe i did not make myself clear enough, 

So in this case, based on today's date, 26/7/2018

Wei Ren Nah has his attendance entered as in ADetails table, and test2 has entered his attendance with ABSENT previously with StartDate 07/20 - 07-27(end date) as today's date lies between 07/20-07/27 we will consider that he entered his attendance because today's date lies within the start and end date 

What i am trying to do is to have an admin page that display the names of those who have not entered their attendance for that day,

So the admin will have a "View Attendance Not Submitted For Today Page" and  should see test1 attendance as not submitted for today.


Hi Wei,

Join between User and ADetails should be With or Without and the clause of the join should be:

Adetails.UserId = User.Id

And set in the filter this:

ADetails.Id = NullIdentifier()

Cheers,

José

José Costa wrote:

Hi Wei,

Join between User and ADetails should be With or Without and the clause of the join should be:

Adetails.UserId = User.Id

And set in the filter this:

ADetails.Id = NullIdentifier()

Cheers,

José


Hi Jose,

I followed your steps, but i got "no records to show" shouldn't it be showing test1 as a record?

Hi Wei,

Switch the order of the join:

First User, then ADetails.

Cheers,

José

José Costa wrote:

Hi Wei,

Switch the order of the join:

First User, then ADetails.

Cheers,

José

Thank you José, this solves the first part of my problem. However, if you see my problem below,

I have created some extra data and added into ADetails table.

In this case, test1 has an absent record from 29/07-31/07.

But if we look at today's date, 26/07, test1 should still reflect in the ADetails table because he has no attendance for today, so this does not solve the main problem.

Please help....



Hi Wei,

Now I see what you want to do...

To do that you have to have a list of all days that you want to search. That can be on an entity and then you will have some aggregate to filter the data.

Or you have a start date and an end date and with logic go through the list of results from the aggregate and check which are the users that do not have anything in those dates.

The bottom line is:

- You have to have a list of dates to search for the non-submitting users. Otherwise, and let me take this to some absurd example, all the users have not submitted anything in all the days of last century...

Cheers,

José

José Costa wrote:

Hi Wei,

Now I see what you want to do...

To do that you have to have a list of all days that you want to search. That can be on an entity and then you will have some aggregate to filter the data.

Or you have a start date and an end date and with logic go through the list of results from the aggregate and check which are the users that do not have anything in those dates.

The bottom line is:

- You have to have a list of dates to search for the non-submitting users. Otherwise, and let me take this to some absurd example, all the users have not submitted anything in all the days of last century...

Cheers,

José

Hi Sir,

Care to furthur explain with some examples?, i'm have some trouble understanding... :(


Solution

Hi Wei Ren,

What José means, is that you want 26/7 (the date of today) to appear in your query results, but anything you want as record in the results in any query must come from the database. So if you want 26/7 to appear, 26/7 must be in the database somewhere! Basically what you want can't be done via a query, unless you have a table that holds all the dates you want to check against.

Solution

Hi Wei,

Here goes an example of having the Dates that you want in an Entity.

Check it out.

Cheers,

José

Hi Wei,

You are asking the eternal question to find something does not exists. If your back end is SQL Server, you can use CTE but I have not try it in Advanced SQL.

However the aggregate as you tried can query for a single date by adding a filter to {ADetails}.

An alternative is create a table containing the dates of interest, and add it to the aggregate (again using outer joins) 

Hope this helps.

Stanley