select * from table
where
cast(cast(StaffAtten.MonthId  as varchar)+'/01/'+cast(StaffAtten.YearId as varchar) as datetime) between 
cast(cast(@frommonthid as varchar)+'/01/'+cast(@fromyearid as varchar) as datetime) and 
dateadd(month,1,cast(cast(@tomonthid as varchar)+'/01/'+cast(@toyearid as varchar) as datetime))

CREATE PROCEDURE proc_Staff_BindAttendenceGroup 
( 
@FromMonthId int, 
@FromYearId int, 
@ToMonthId int, 
@ToYearId int, 
@CompanyId int, 
@DeptId int 

) 
AS 
BEGIN 
declare @FromD datetime
declare @ToD datetime

set @FromD=convert(datetime,convert(varchar,@FromYearId)+convert(varchar,@FromMonthId)+'01')
set @ToD=convert(datetime,convert(varchar,@ToYearId)+convert(varchar,@ToMonthId)+'01')

Select 
StaffAtten.UserId, 
Sum(StaffAtten.DaysInmonth) as TotalDays, 
sum(StaffAtten.Present) as Present, 
sum(StaffAtten.Absent) as Absent, 
ULogin.UserName 
from 
Staff_MonthAttendence as StaffAtten 
Left join User_Login as ULogin on StaffAtten.UserId=ULogin.UserId 
Left Join User_colink as ULink on ULogin.UserId=ULink.userId 
LEFT JOIN DEPT AS D ON D.DEPTID=ULink.DEPTID 
WHERE 
convert(Datetime,convert(varchar,StaffAtten.YearId)+convert(varchar,StaffAtten.MonthId+'01')) between @FromD and @ToD 
and ULink.CompanyId= @CompanyId 
and ULogin.UserLevel <>1 
and ULogin.UserLevel <>2 
and (@DeptId is null or ULink.DeptId=@DeptId) 
and ULogin.isActivated!=0 
Group By 
StaffAtten.UserId, 
ULogin.UserName 
--Tour 
ORDER BY 
ULogin.UserName 
END 


--exec proc_Staff_BindAttendenceGroup 11, 2008,11,2008,2,5

WHERE
    StaffAtten.YearId BETWEEN @FromYearId AND @ToYearId 
    AND StaffAtten.MonthId
        BETWEEN
            CASE
                WHEN StaffAtten.YearId = @FromYearId
                THEN @FromMonthId
                ELSE 1
            END    
        AND
            CASE
                WHEN StaffAtten.YearId = @ToYearId 
                THEN @ToMonthId
                ELSE 12
            END    
    AND ULink.CompanyId = @CompanyId 
    AND ULogin.UserLevel <> 1 
    AND ULogin.UserLevel <> 2 
    AND (@DeptId IS NULL OR ULink.DeptId = @DeptId) 
    AND ULogin.isActivated <> 0 

quote:

---

Originally posted by abcd

Sir these are the results after i brought stffatten.monthid and staffatten.yearid...


UserId TotalDays Present Absent UserName monthid yearid
----------- ----------- ---------------------- ---------------------- -------------------------------------------------- ----------- -----------
28 433535 426542.5 6992.5 Akash 1 2009
28 419550 398572.5 20977.5 Akash 11 2008
28 433535 426542.5 6992.5 Akash 12 2008
26 433535 433535 0 Gagan 1 2009
26 419550 377595 41955 Gagan 11 2008
26 433535 384587.5 48947.5 Gagan 12 2008
79 433535 377595 55940 RupaliS 1 2009
79 419550 293685 125865 RupaliS 11 2008
79 433535 223760 209775 RupaliS 12 2008
27 433535 377595 55940 Saurabh 1 2009
27 419550 405565 13985 Saurabh 11 2008
27 433535 426542.5 6992.5 Saurabh 12 2008
25 433535 433535 0 Tahir 1 2009
25 419550 419550 0 Tahir 11 2008
25 433535 349625 83910 Tahir 12 2008

(15 row(s) affected)

---