Logic error..Monthly report query with joins doesnt work
-
Hi,
I have this monthly report query which I wrote which gets Company info from COMPANY TABLE, employees in system from Employee table, and I need to use third table USERLOG because I have to exclude rows from employee table where employee_no is present in USERLOG table and userlog.uname is not like 'masterclient%'(these uname are our admin unames and client emps) and userlog.upass != 'welcome' (this is also our user and we want to exclude from report).
Here is the query
SELECT Company.Company_Name 'CompName', Company.Company_ID as Company_ID,
Company.Reference_Id 'RefID',
Company.Employee_Count 'EmpsSetup',
(Select Count(Leave.Leave_No) from Leave where Leave.Company_ID=Company.Company_ID
and Request_Date > (dateadd(mm,-1,getdate()) ) and Request_Date < getdate()) 'CountLeaves', Count(Employee.Employee_No) as 'EmpsSystem' FROM (Employee Full outer JOIN Company ON Employee.Company_ID = Company.Company_ID) left outer join userlog on
((userlog.employee_no = employee.employee_no and userlog.uname not like 'masterclient68') and (upass != 'welcome') )
WHERE Company.Company_ID > 2 and Company.Company_ID != 59 -- dont send template compsGROUP BY Company.Company_Name, Company.Reference_Id, Company.Employee_Count, Company.Company_ID
Order By 1
Output :
For ABC, I get EmpsSystem(employee count) as 2 where these 2 users are actually our company users like with userlog.uname as masterclient68 and upass = welcome. I want to exclude these 2 from the total count of emps returned by query. So ABC should say 0 emps in system.
What is wrong with my query?
Any suggestions are welcome.
-
Not sure about others, but I could use the DDL for your tables and some sample data and expected results so that i could do some testing.
-
create table employee(employee_no int identity,company_id int,empl_first varchar(50), empl_last varchar(50))
create table company(company_id int identity,company_name varchar(50), Reference_id varchar(50),Employee_count int)
create table Leave (leave_no int identity, Requestdate datetime, company_id int)
create table userlog(employee_no int, uname varchar(50), upass varchar(50))
insert into employee(company_id, empl_first,empl_last)
values (3,'Master','MyUser')
insert into employee(company_id, empl_first,empl_last)
values (3,'Training','MyUser')
insert into employee(company_id, empl_first,empl_last)
values (3,'Client','User')
insert into company (company_name,reference_id,employee_count)
values ('comp1','refcomp1',500)
insert into company (company_name,reference_id,employee_count)
values ('comp2','refcomp2',500)
insert into company (company_name,reference_id,employee_count)
values ('comp3','refcomp3',500)
finally for userlog table you want to insert for company 3 only since my condition says company_id > 2
insert into userlog(employee_no, uname, upass)
values (1,'masterclient3','password')
insert into userlog(employee_no, uname, upass)
values (2,'trainer','welcome')
insert into userlog(employee_no, uname, upass)
values (3,'regularuser','anything')
OUTPUT
we should get count(employee.employee_no) for company_id 3 as 1 i.e. excluding masterclient3 user and trainer user. Using my query, the result will be 3. Please edit the condition in my query where it says userlog.uname not like 'masterclient68' to 'masterclient%'.
I wrote this queries over here and have not tested yet. Plz correct small errors if there are any.
Thanks..
-
With the small amount of data that you provided, this:
SELECT Company.company_name AS compName,
Company.Company_ID AS Company_ID,
Company.Reference_Id AS RefID,
Company.Employee_Count AS EmpsSetup,
COUNT(Leave.Leave_No) as CountLeaves,
COUNT(Employee.Employee_No) AS EmpsSystem
FROM Company
LEFT JOIN Employee
ON Employee.Company_ID = Company.Company_ID
INNER JOIN userLog
ON userlog.employee_no = employee.employee_no
AND userlog.uname NOT LIKE 'masterclient%'
AND upass != 'welcome'
LEFT JOIN leave
ON leave.company_ID = company.company_ID
AND Request_Date > (dateadd(mm,-1,getdate()) )
AND Request_Date < getdate()
WHERE company.company_id > 2
AND Company.Company_ID != 59 -- dont send template comps
GROUP BY Company.Company_Name, Company.Reference_Id, Company.Employee_Count, Company.Company_ID
Order By 1
Gives:
compName Company_ID RefID EmpsSetup CountLeaves EmpsSystem
comp3 3 refcomp3 500 0 1
-
the only problem with your query is that there are lot more emps in employee table than in userlog table. So if we do inner join on userlog we are missing employees from employee table who might not be present in userlog table. so outer join on userlog table is a must...
makes sense?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply