Attendance query Required?

Question

employee(empid ,empname)

attandance(id ,empid,dateofprent day,timein timout)

show empname ,Absentdate that are absent in april2012

Haider Ali
Software Engineer

Answer 1

Dear Haider ali


    We make Query for you try this



-------------------------------------------------------------------------------
first create a function for date range in Sql Query
-------------------------------------------------------------------------------
create Function GetDateRange (@month int,@Year int)
RETURNS @OutTable Table   
( 
    dt datetime 
) 
As
Begin
    declare @FRDate datetime
    declare @ToDate datetime

    SET @FRDate = convert(varchar(10), convert(varchar(4), @Year) + '/' + convert(varchar(2),@month) + '/01' ,112)
    set @ToDate= dateadd(MM,1,@FRDate)
    set @ToDate= dateadd(DD,-Day(@FRDate),@ToDate)

    Begin


    with im as
    (select @FRDate as [nom]
        union all
     select [nom] + 1 from Im where [nom] < @ToDate)

    insert into @OutTable
    select *  from im

    ENd
return
End
-----------------------------------------------------------------------------------------------------------------------
After Create Query Like This
-----------------------------------------------------------------------------------------------------------------------
declare @month int
set @month=4
Declare @Year int
set @Year = 2012

select e.EmpName, Qry.AbsentDate from
(select e.empid ,DTRAnge.dt as AbsentDate
from employee e
cross apply (select * from dbo.GetDateRange(@month,@Year)) as DTRange ) as Qry
inner join employee e on e.empid=Qry.EmpId
left outer join   attandance A on A.empid = Qry.EMpID And  A.dateofprentday = Qry.AbsentDate
where  isnull(A.EmpId,0) = 0