sql query needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql query needed

Author

Topic

harik
Starting Member

3 Posts

Posted - 2007-03-13 : 05:03:57

I have a table attendence which has got columns empno, date ,timein , timeout. If an employee is absent on a day there will be no row for it. How can i get the days where an employee is absent?

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-13 : 05:24:07

don't u keep absenty records of employee? it would be better the keep such records.

Mahesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 07:49:22

Do you have a calendar table ?
left join to to the attendence table

harik
Starting Member

3 Posts

Posted - 2007-03-13 : 10:03:52

No there is no calendar table. But all the days in a month will come in the attendance table date field since all the employees have different off days. I would like to know whether it is possible to get the offday without making another calendar table since all the dates are coming in this table if we are giving the distinct date command.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 10:14:59

then make use of F_TABLE_DATE http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&SearchTerms=F_TABLE_DATE

michaelxvo
Starting Member

47 Posts

Posted - 2007-03-14 : 13:30:08

I THINK YOU CAN USE A PROCEDURE LIKE THIS TO CREATE A MONTH. I TEST IT AND IT WORKS UP TO THE POINT " select * from @month"

IN YOUR QUERY, YOU WILL REPLACE EMPNO WITH YOUR CORRECT EMPNO

declare @day varchar(10),
@count tinyint,
@end tinyint

declare @month table(id tinyint identity,dayofmonth smalldatetime)

set @count = 1
set @end = 31

while @count <= @end
begin
set @day = '03/'+ rtrim(convert(varchar(2),@count)) + '/2007'
insert into @month values(convert(smalldatetime,@day))
set @count = @count + 1
end

--select * from @month

select dayofmonth,a,b,c,d
from @month left join (select empno a,date b,timein c, timeout d
from attendance where date between '2007-03-01 00:00:00' and '2007-03-31 23:59:59'
and empno = 11) x
on convert(char(10),dayofmonth,101) = convert(char(10),b,101)

harik
Starting Member

3 Posts

Posted - 2007-03-18 : 12:14:21

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources