February 13, 2007 at 10:36 am
I need to create a cross charge report.
Table - emp – empnum,empname, lent1,lent2 (lent2 column is the Home Department number)
Table - Total – empnum, lent1, lent2 total1, total2, total3 (Lent1 are the different department numbers from which they worked)
Table- Labor_Dept – Lentry (which is lent1 and lent2 in the same column) lref ( is the department name)
Each employee has one “Home” department which they belong too. They do charge their time to other departments. How can I construct a query that shows all other departments worked for except their home department?
CY
February 13, 2007 at 11:54 am
Can you give a sample of the data from each of the tables as well as a short narrative of what you want to see in output and what each piece of data means? I'm especially interested in how one determines what departments an employee has worked for and what the home department is.
Russel Loski, MCSE Business Intelligence, Data Platform
February 13, 2007 at 12:09 pm
Sure.
Empnum,empname Lent1(Dept) lent2(Home Dept)
Emp table - 0001003699Smith,Colleen M 53001011000000 001124
Total table 000100369953001011000000001040 6.5
000100369953001011000000001041 19.0
000100369953001011000000001124 17.25
Labor_Dept
53001011000000 - IT
1040- Actg/Finance
1041- HR
001124 -IT
Desired Output
Actg/FinanceSmith,Colleen M 6.5
HR Smith,Colleen M.19
Thanks,
Marty
February 13, 2007 at 12:59 pm
select lref . empname, total.total1
from emp inner join total
on emp.EmpNum = total.EmpNum
inner join Labor_Dept
on Labor_Dept.Lentry = total.lent1
where emp.lent2 <> total.lent1
Russel Loski, MCSE Business Intelligence, Data Platform
February 13, 2007 at 1:09 pm
It does give them all, however it still inclueds the home department as well.
I'm trying this:
SELECT
c.lref,
b.empname,
SUM(TOTAL_1/60 + TOTAL_2/60) as total
FROM [TOTAL] a
InnerJoin emp b on a.empnum=b.empnum
Inner Join labor_Dept c on a.lent2 = c.lentry
where a.lent1 c.lentry
Group By b.empname,c.lref
Order by b.empname ASC
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply