Click here to Skip to main content
14,970,669 members
Please Sign up or sign in to vote.
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)
for example
tbl_login :
userid name
001 abcd
002 defc
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

i dont know if its possible or not,if it is possible
please help me

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

This should help:
you can use if DateDiff(day, fromdate, sysdate) > 0 then return "absent" else return "present"
You should specify ur fromdate field as datetime type.. try it out..
kishore Rajendran 10-Mar-11 4:58am
fromdate is a datetime field
Would this work?
select tbl_login.userid,,
         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
join   tbl_leave on tbl_leave.userid=tbl_login.userid

- 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
kishore Rajendran 10-Mar-11 6:51am
see duration is like '2days','Half Day','Full Day' etc
so we cant cast it to int
this wont work

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900