Sum the counts from two different tables

Elizabeth Rani

Hi All, Iam new to database. Here iam using sql server 2000. i want to sum the counts from two different tables. I need this to calculate the employees who have taken continuous leaves in a week/month. Example: I have three table such as Table1:tbl_attandence(it contains the employee id and the intimings and outtime) Fields:empcode,intime,outime Eg: EMP001,11/24/2011 09:30:45 AM,11/24/2011 04:30:15 PM Table2:tbl_leaveentry(it contains the leave dates taken by an employee) Fields:empcode,entrydate Eg: EMP001,11/25/2011 Table3:tbl_holidays(it contains the list of holidays such as sunday,saturday,chrismas,newyear....) Fields:holidays,days Eg: 11/25/2011,sunday I am attaching my code such that it return the sum but iam unable to return the employee name and id because it is showing the result as null as the holidays does not match please help me in completion of process select sum(counts) from( select a.empcode as empcode,b.name,count(*) as counts from tbl_leaveentry a,tbl_emp b where a.empcode=b.empcode and 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 having sum(counts)>=3 the above code returns correct sum but if i add name before the sum and execute the result is not been summed because holidays does not match with the leaves Please suggest that how can i do this task any help will be appreciated

Corporal Agarn

Try something like:

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

Note the GROUP BY and I have changed to an INNER JOIN. Hope this helps. Sorry, I do not have time to dive deeper into this. [edit]to spell note correctly[/edit]

SilimSayo

Try this.

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

Elizabeth Rani

Hi SilimSayo, Thank you so much for the query it matches my result perfectly.

SilimSayo

No problem.