Search Employee by JoinYear and LeaveYear

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

, +

I am beginner in SQL.. unable to fetch Employee details where JoinYear<=@Year and DateofLeave=null or DateofLeave=@Year..

I searched this on google but i didn't get right solution..
plz help

Posted 15-Feb-15 21:10pm

itsathere

Updated 15-Feb-15 21:18pm

v2

Add a Solution

2 solutions

Solution 2

Not sure if I understood your requirement correctly, but if the data is in date format you need to extract the year portion. So perhaps somethig like

SQL

SELECT ...
FROM TableName
WHERE DATEPART(year, JoinDate) <= @year
AND   COALESCE(DATEPART(year, LeaveDate), @year) = @year

Posted 15-Feb-15 22:59pm

Wendelius

v3

Comments

itsathere 16-Feb-15 4:20am

u have great idea but LeaveDate will be null sometime if employee is still working..

Wendelius 16-Feb-15 4:44am

That is why I used the COALESCE. It returns the first non null value :)

itsathere 16-Feb-15 5:51am

thanks for your valuable comments

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)

OriginalGriff · Accepted Answer · 2015-02-15T21:26:00

Solution 1

First thing to look at is the actual values you are comparing: if they are dates (and it's normal to store Join dates and leave dates as DateTime rather than just a year value as almost nobody starts of finishes on Jan 1st) then equality tests don't often work as they are accurate to milliseonds!

The next thing is to look at your condition.

A AND B OR C

Is that

(A AND B) OR C

Or

A AND (B OR C)

There is an important difference in there! If you aren't exactly sure how SQL (or any other system) will treat a condition, then use the appropriate brackets to force exactly the intrepretation you need.
I'm guessing you want

A AND (B OR C)

but I could be wrong.

Finally, check the values you are passing to to check against, and the DB values you are referring to: look at what you think you should get, and then at exactly what you do get. If they don't match, there is likely to be a pattern in the delivered data which will point you in the right direction.

Sorry, but without the data you are using, we can't be specific on a solution.

Posted 15-Feb-15 21:26pm

OriginalGriff

Comments

itsathere 16-Feb-15 3:30am

all i need is A AND (B OR C)

OriginalGriff 16-Feb-15 3:47am

So add the brackets, and see what happens...

itsathere 16-Feb-15 3:51am

still not working..

itsathere 16-Feb-15 3:57am

sorry, it's working in sql but same condition not working in linq

OriginalGriff 16-Feb-15 4:06am

So show us the Linq?

Remember that we can't see your screen, access your HDD, or read your mind.
:laugh:

itsathere 16-Feb-15 4:08am

var emplouee = objempBL.GetAllEmployeeDetails().Where(a => (Convert.ToDateTime(a.DOJ).Year >= 2009 && Convert.ToDateTime(a.DOJ).Year <= Convert.ToInt32(ddlyear.SelectedValue) && (Convert.ToDateTime(a.DOL).Year == null || Convert.ToDateTime(a.DOL).Year <= Convert.ToInt32(ddlyear.SelectedValue)))).ToList();

itsathere 16-Feb-15 4:11am

on change in query

var emplouee = objempBL.GetAllEmployeeDetails().Where(a => (Convert.ToDateTime(a.DOJ).Year >= 2009 && Convert.ToDateTime(a.DOJ).Year <= Convert.ToInt32(ddlyear.SelectedValue) && (a.DOL == null || Convert.ToDateTime(a.DOL).Year <= Convert.ToInt32(ddlyear.SelectedValue)))).ToList();

OriginalGriff 16-Feb-15 4:37am

Firstly, why is your EmployeeDetails class not holding dates as DateTime objects? It would make all your other code much, much easier to read if it did...
As in:

Where(a => a.DOJ >= 2009 && a.DOJ.Year <= wantedYear && (a.DOL == null || a.DOL <= wantedYear)

So, that returns values where they joined 2009 or later, and before or in the year you are looking for, and haven't left, or left before the year you are looking for.

Now, I have no idea what value it is returning - so I'd start by looking at the parts.
Break it into two statements: The first creates a list of employees who fulfil the Joining criteria. The second uses that list to select those that match the leaving criteria.

Look at the second list first, and see if they match what you were getting - and not what you wanted. It should be the same as the list you are getting at the moment.
So then look at the first list and see what values it holds - that should tell you why the result is not what you want.
Sorry, but you need the data to make any sense of what is going on - and I don;t have any access to your data!

itsathere 16-Feb-15 5:51am

I have done it with stored procedure btw many many thanks.