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)
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

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
   
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..
   
Comments
kishore Rajendran 10-Mar-11 4:58am
   
fromdate is a datetime field
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
   
Comments
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