SQL QUERY FOR DISPLAYING DATA FROM TABLE

Question

4.00/5 (3 votes)

See more:

I have two tables,
first one is "tbl_login" which contains two columns "userid' and "name" (both are varchar fields)
second one is "tbl_leave" which conatins three columns "userid","fromdate","duration" (all are varchar fields)
tbl_login.userid=tbl_leave.userid
for example
tbl_login :
userid name
001 abcd
002 defc
tbl_leave

userid 		fromdate 		duration
-----------------------------------------------------------------------------------
001 		2011/03/08 	2 days
002		2011/03/28 	5 days

i want a query to display like this

here name of user with id 001 have on leave from 08/03/2011 for 2days
so when i login on 09/03/2011,it shos like this following

userid		name           status
------------------------------------------------------------------------------------- 	
001		 abcd 	  absent
002 		 defc 	  present

because "abcd" is on leave and defc is on not leave
and when today=29/03/2011
its will show like

userid		name           status
-------------------------------------------------------------------------------------	
001		 abcd 	  present
002 		 defc 	 absent

because defc is on leave from 28/03/2011 for 5 days

ALL THE FIELDS ARE VARCHAR FIELDS
i dont know if its possible or not,if it is possible
please help me

Posted 9-Mar-11 22:22pm

kishore Rajendran

Add a Solution

4 solutions

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)

Ryan Zahra · Answer 1 · 2011-03-09T22:54:00

Solution 1

This could be done using a stored procedure and using an if statement.

This should help: http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause

Posted 9-Mar-11 22:54pm

Ryan Zahra

rajeshmulchandani · Answer 2 · 2011-03-09T23:06:00

Solution 3

you can use if DateDiff(day, fromdate, sysdate) > 0 then return "absent" else return "present"

Posted 9-Mar-11 23:06pm

rajeshmulchandani

Raj.rcr · Answer 3 · 2011-03-09T22:55:00

Solution 2

You should specify ur fromdate field as datetime type.. try it out..

Posted 9-Mar-11 22:55pm

Raj.rcr

Comments

kishore Rajendran 10-Mar-11 4:58am

fromdate is a datetime field

Costica U · Answer 4 · 2011-03-10T00:19:00

Would this work?

SQL

select tbl_login.userid,
       tbl_login.name,
       case
         when getdate() between tbl_leave.fromdate and dateadd(day,tbl_leave.duration-1,tbl_leave.fromdate) then 'absent'
         else 'present'
       end as ststus
from   tbl_login
left
join   tbl_leave on tbl_leave.userid=tbl_login.userid

NOTE:
- use left join ( tbl_leave may not have data for all users)
- tbl_leave.duration is (must be) a int, if not you must convert it to an int