Attendance query Required?

Question

employee(empid empname) attandance(id empiddateofprent daytimein timout) show empname Absentdate that are absent in april2012 Haider Ali Software Engineer

Kunal Vaishya · Answer

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(MM1@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] 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.EmpId0) = 0