Database

SELECT @SDate = DATEADD (day, - @holidayCount, @endDate)

The truth is it's not good to access a web service trought a trigger, due to performance concerns.

select name, sum(counts)
from(
    select a.empcode as empcode,b.name,count(*) as counts 
    from tbl_leaveentry a
    INNER JOIN tbl_emp b
        ON a.empcode=b.empcode
    where  a.empcode='EMP001'
        and a.entrydate between '11/24/2011'; and '11/27/2011'
    group by a.empcode, b.name

    union
    
    select null, null, count(*) as counts 
    from tbl_holidays
    where holidays between '11/24/2011' and '11/27/2011'

) as c 
GROUP BY name
having sum(counts)>=3 

DECLARE
@holidayCount AS int,
@startDate AS datetime,
@endData AS datetime,
@empCode AS VARCHAR(20)

SET @startDate='11/24/2011 00:00:00.000'
SET @endDate='11/27/2011 23:59:59.997' 
SET @empCode='EMP001'

SET @holidayCount=(SELECT COUNT(*) FROM tbl_holidays WHERE
                   holidays BETWEEN @startDate AND @endDate)

--I am assuming that 'holidays' is a datetime field/column

SELECT empCode, name, (Counts + @holidayCount) As holidayCounts 
FROM
(
   SELECT a.empCode, b.name, (Count(*) as Counts 
   FROM tbl_leaveentry a,tbl_emp b
   WHERE   a.empCode=b.empCode AND
           a.empCode=@empCode AND
           a.entryDate @startDate and @endDate
GROUP BY by a.empCode,b.name
) AS temp
WHERE  Counts + @holidayCount>3

Mycroft Holmes wrote:

so we build ELT processes.