what would be the query for this requirement

Question

0.00/5 (No votes)

See more:

i have a dropdown list for the a number of functions like wedding,birthday etc,
and three are two table Employee,AND EmployeeStatus Table

SQL

Employee Table
id     Emp_Id  Name 
1       23      ssss
2       24      dddd
3       34       ffff

and EmployeeStatus Table

SQL

id    functionType_Id    Emp_Id    Name    Date
1        1                 23        sss   10-05-2015
2        1                 24        ddd    10-05-2015

i want that when there is more than one function on the same day ,then employee that are already used up can never used up again,rather employee that are left behind are allocated for next function for the same day.

Posted 20-May-15 18:28pm

Sajid227

Add a Solution

Comments

VICK 21-May-15 1:18am

So EmployeeStatus Table is the one in which you are saving the Function-User relationship???

VICK 21-May-15 1:20am

Do try this.

select * from Employee
where Emp_Id not in
(
select distinct Emp_id from EmployeeStatus where Convert(date, Date) = Convert(date, 'YourDate')
)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Accepted Answer · 2015-05-20T19:47:00

First thing first,
1. There is no need for Name field in the employeestatus table as it rightfully belongs to employee table, this concerns http://www.studytonight.com/dbms/database-normalization.php[^]
2. Avoid using 'date' as field name as this is a reserved word in sql and it should be of the appropriate date type not string, https://msdn.microsoft.com/en-us/library/bb630352.aspx[^]
3. Use NOT EXISTS[^] to get the result, e.g.

select * from employee e where NOT exists
(
select * from employeestatus es where e.emp_id=es.emp_id AND
  function_date = '10-05-2015'
)